How to move the system database files in SQL Server
Вставка
- Опубліковано 6 вер 2024
- Sometimes installation defaults are missed, or storage is added, and you need to move the system database files to a new location. In this video, watch Microsoft Certified IT Professional Jon Seigel show you how to perform this procedure, including how to set the security permissions on the target folder.
Blog post for this video:
voluntarydba.co...
Visit my channel for more database administration videos:
/ voluntarydba
Subscribe to get notified about my latest videos:
www.youtube.co...
Read additional content on my blog:
voluntarydba.com
Follow on Twitter:
/ voluntarydba
Like on Facebook:
/ voluntarydba
This is a very useful information. I've been struggling to find how to do this online, but there are not too many information out there about this. You have saved my day! thanks mate
I just had to perform this operation at work. Thank you so Much! Please keep this going!
This is exactly what I will be doing for a client in a couple weeks! Thanks a bunch!
You're welcome!
nice thanks for share knowledges ! Brazil..
Very good presentation.
Thanks for your sharing.
Mel
You're welcome!
Really good video.very very helpful. thank you so much for sharing this. please share all topics videos if you have then those will be helpful for every dba learners... :)
Anji Pavuluri That's the plan. :) Thanks for watching!
Hi! I will do this procedure tomorrow on a SQL cluster. Would you say that its basically the same procedure or does it differ a lot?
The only difference i can see is that you have to shut down all SQL server resources in failover cluster(all except cluster IP).
I'll be honest, I've never actually done this on a cluster. From what I do know, I agree, definitely make sure the service is stopped and won't start on any node while you do this. If you're moving the master database, you'll have to change the service startup parameters on every node, and testing failover to every node would be a good idea. Make sure every node can access all new location(s)/drive(s) correctly before touching anything in SQL Server. The rest of the procedure should be the same because it's all shared information that exists in only one place at a time. I can't think of any other differences. Let me know how it goes!
Voluntary DBA
Thanks man! Great guide otherwise.
I followed this and it worked fine. I did however change the dump directory of the SQL server agent as well and that is not included in this tutorial.
Thanks for the answer. I booked in another service window were we can fail over and surveill the whole procedure to see if the other node is configured correctly as well.
I though however that when i fail over the SQL service to the second node, that it will take the configuration of node1 with it. So i think that the startup parameters should be transferred over, but i'm not sure about this.
I'm glad to hear it went well! Yes, if you changed the service startup parameters in any way, those changes have to be applied to all of the other nodes. Most of the configuration will automatically carry over from node-to-node because it's stored within one of the system databases, which are shared between the nodes (though only 1 node can have control of them at a time). But the services (within Windows) on each machine are separate, so each one has to be told the location of the master database, which contains the locations of the rest of the databases in the instance.
Thank you very much for sharing this video , it is indeed helpful, Great Work, Keep it up.
Is there any other way to moving or copying file rather than using code, though code is very much easier but, I saw an option as " Copy Database" I want to learn that as well. If you have any related links then please share.
You're welcome.At this point, there's no other way (and I doubt there ever will be). I encourage the use of code anyway because it's a good idea to have a script written that can do this dynamically. Tempdb in particular may have many data files and doing copy/paste manually opens things up to errors. I did it manually in the video to show the nuts & bolts of how it works.
You're right that the Copy Database wizard would be the UI version, but it can't be used on the system databases as stated here: msdn.microsoft.com/en-us/library/ms188664.aspx The procedure to move a user database is much simpler than for the system databases (master is another exception even to that), and of course doesn't require taking the instance down just to copy the files.
Very helpful! Thank you very much!
You're welcome!
Thanks a lot!
Thanks, that really helped!
You're welcome!
Gracias por esta buena informacion
tnq
THINK