How to move datafiles and logfiles of system databases.

Question

Post reply

How to move datafiles and logfiles of system databases.

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
February 27, 2010 at 12:10 pm

#138459

Hello There,
I have a requirement where I've to move the system database files of temdb from drive G to drive H.
If I stop the SQL Server services and after that move the files and start the Services, will that not work?
How will that work if I do the same for other system database files, E.g, master, model and msdb?
Please suggest your ideas.
Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Here's an article on how to move system databases.

http://msdn.microsoft.com/en-us/library/ms345408.aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

Thank you so much Jason. This is the perfect document for which I was looking around.

Thanks.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

You're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

EdVassie SSC Guru Points: 60448 More actions · Answer 4

The article tells you how to move system databases, but does not tell you if it is a good idea to do so.

Many people need to put tempdb on to specific drives in order to optimise performance, and it is considered best practice to do this. As long as you can spell the syntax correctly there are no risks in doing this work.

However, moving master, resource and model is a different matter. There are no integrity or performance advantages to moving these databases, and moving them has proved to be risky. Microsoft have a history of issuing SPs and CUs that assume master and resource are in their default locations, and applying the SP or CU fails (sometimes leaving SQL unusable), if they have been moved. This problem has not happened for a while, but it is a brave person who assumes it will not recur. Best practice is to leave these databases where they are put by the SQL install.

Many people feel that msdb should be treated the same as any user database regarding its location. A lot of sites rely heavily on msdb for SQL Agent jobs, SIS package store, etc, resulting in a msdb of a few hundred MB in size. Other people say even at this size there is no need to move it. However, there are no particular risks in moving msdb, so if you want to do this then go ahead.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

free_mascot One Orange Chip Points: 27169 More actions · Answer 5

Hello EdVassie,

Thanks for sharing this in detail 🙂

I was not sure about the master and done the location change for all systemdatabase. I was fortunate that nothing happen and everything is running smoothly.

But taking your consideration I will restrict in future.

Cheers!!

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."