This came to light when I was working with a company who were struggling with their MSDB database. They had a requirement to maintain huge volumes of historical job data and backup information and didn't want to archive this to another database. Therefore this meant a large MSDB which had filled their drive and they needed to relocate it.
To move the Model and MSDB databases in SQL Server 2008 R2 follows the same pattern as with tempDB. However, the reason I have put this in a seperate article is that I will also cover the steps involved in moving these databases under SQL Server 2005 (though not in great detail).
Firstly we find the current location of the Model and MSDB files:
In SQL Server 2008 R2 all you need to do is run the following:
Then you need to stop the SQL Server:
Move the physical files on your operating system, and then start you SQL Server once more:
This is FAR simpler than the method for SQL Server 2005. For this method we need a completely different approach.
As above we check for the current location of the files. Once found we then have to open SQL Server Configuration Manager and click on SQL Server Services on the left hand side:
In here, locate the main SQL Server Service, right click, and select Properties:
Click on Advanced:
And you should be able to see “Startup Parameters”:
Clicking the drop down arrow will let you see a nice text box which will tell you exactly what parameters are being used for startup:
You need to add ;-c-m-T3608
to the end of this:
Then start the service and SQL Server will start in single user mode.
Connect to SQL Server using Management Studio...
If you see the following error...
It is because you already have a connection in Object Explorer. Therefore Disconnect this...
You will now be able to open a solitary Query window...
Once you have done this you will be able to detach the msdb and model databases...
Now move your files in the file system to the location you want them and then re-attach the databases:
'D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\model.mdf',
'D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modellog.ldf',
'D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBData.mdf',
'D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDBLog.ldf',
NOTE: If you move both model and msdb together then you HAVE to complete this in the correct order otherwise you will get errors. You must detach MSDB first, then model, and then you must attach model before MSDB.
Remove the parameters from the startup line and start SQL Server. The databases are now being used from their new locations.