question

Chitra avatar image
Chitra asked

Move system and user databases

Should I detach system databases before detaching all of the user databases? Which database should I move first? I tried a trial run on the test instance and I moved the temp, msdb, user and then the master and I see some of the user databases are detached although if I run the sql select physical_name from sys.master_files, I can see the location of the datafile and log file and they are not located in the same drive where I have moved. Please advice. Thanks in advance for all the advices.
sql-server-2005
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you for all the comments and I my hair is on fire and I am working by following your suggestion. But tell me one thing in production I have only 496 MB left and it is growing. Can the cause be the the temp file. Do I move that database or by simply stopping and starting the service will clean up the temp database. Please advice me. Also what are the distmdl.mdf and distmdl.ldf? Do I need to move them?
0 Likes 0 ·
Restarting the SQL Server Service will recreate the TEMPDB. You want TEMPDB on your fastest drive. To move tempdb is the simpliest database to move. Simply alter with move to the new location and restart services.
0 Likes 0 ·
Thanks for the comment, can I just move the tempdb to a different drive and leave rest of the databases like the system and the user databases in a different drive.
0 Likes 0 ·
Yes, if you are not going to move to a different server. Remember to restart the SQL instance when you have moved the tempdb files. Also remember to place the tempdb on a fast disc system.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
I would reverse the orders. Remove the user databases then the system databases, then on the other machine attach the system databases then the user databases.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Grant, I haven't tried to detach any system database, what will happen when you detach the tempdb and master database. Doesn't you server stop responding? (on the other hand, you never need to move the tempdb) I would use backup restore to move any system databases.
1 Like 1 ·
Yeah, you're right, of course, I wasn't clear. No, I wouldn't bother with tempdb. You can detach & attach the system databases. It's not usually a best case, but it's possible to do.
0 Likes 0 ·
Blackhawk-17 avatar image
Blackhawk-17 answered
To move temp dB start with this sort of T-SQL: ALTER DATABASE tempdb MODIFY FILE BOL can help with the rest.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.