Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


system databases moving


system databases moving

Author
Message
Ted Zatopek
Ted Zatopek
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 200
Just recently, I've had to move the system databases to a drive separate from the user databases due to a requirement from a 3rd party tool. We are evaluating using the backup process that our SAN vendor provides. That tool requires that the system databases be on their own SAN drive in order for the tool to work.

It was interesting running through the process. I learned a lot that I wasn't necessarily planning on learning, specially in the test environment. :-) For example how to recover SQL when I didn't do things in the right order, or got too click happy, or fat fingered something.



cphite
cphite
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 650
GilaMonster (1/14/2014)
Moving TempDB is easy. The other three, not so much.


True enough. I needed to move tempdb on our production server a few months back and even that was making me nervous ;-) Not so much the moving part (which is easy) but the "how do I get things working again if something goes wrong?" aspect of it.

The others I have moved on development boxes, where it really didn't matter. Good to know how to do them, though.
ramana3327
ramana3327
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1252 Visits: 2048
oh. I am thinking that for temp db we don't need to move physically. Once you restart the server it will take the new path
cphite
cphite
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 650
ramana3327 (2/8/2014)
oh. I am thinking that for temp db we don't need to move physically. Once you restart the server it will take the new path


Correct - when you restart SQL Server it will create tempdb at the new location. The only catch is that if for whatever reason the new location isn't accessible, SQL won't start and you'd need to restore master.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47091 Visits: 44342
cphite (2/9/2014)
The only catch is that if for whatever reason the new location isn't accessible, SQL won't start and you'd need to restore master.


SQL won't start in that case, but you don't need to do anything as extreme as restoring master.
http://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


cphite
cphite
SSC-Enthusiastic
SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)SSC-Enthusiastic (138 reputation)

Group: General Forum Members
Points: 138 Visits: 650
GilaMonster (2/9/2014)
cphite (2/9/2014)
The only catch is that if for whatever reason the new location isn't accessible, SQL won't start and you'd need to restore master.


SQL won't start in that case, but you don't need to do anything as extreme as restoring master.
http://www.simple-talk.com/sql/backup-and-recovery/the-sql-server-instance-that-will-not-start/


Ah, okay - cool... I did not know that would work Smile
Nadrek
Nadrek
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1023 Visits: 2673
GilaMonster (1/14/2014)
Moving TempDB is easy. The other three, not so much.


MSDB and Model are fairly easy, too.

Master's where you have to pull out the fun startup parameters.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search