SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


system databases moving


system databases moving

Author
Message
Ted Zatopek
Ted Zatopek
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 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
Old Hand
Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)

Group: General Forum Members
Points: 307 Visits: 705
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
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2089 Visits: 2083
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
Old Hand
Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)

Group: General Forum Members
Points: 307 Visits: 705
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 Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86697 Visits: 45254
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
Old Hand
Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)Old Hand (307 reputation)

Group: General Forum Members
Points: 307 Visits: 705
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
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1870 Visits: 2726
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