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


moving tempdb - recovery plan


moving tempdb - recovery plan

Author
Message
cphite
cphite
Old Hand
Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)Old Hand (315 reputation)

Group: General Forum Members
Points: 315 Visits: 706
So this is one of those areas where I feel least comfortable... messing around with system files.

Our new production SQL server was set up with all of the system databases on the same VMDK - including tempdb - and it turns out that is bad for replication to our DR environment. So I have been tasked with moving tempdb to it's own separate drive. Now, moving tempdb itself isn't the issue - I've done it before, it's a relatively simple process - ALTER DATABASE with the new location for each file, restart SQL, and you're done.

The problem is, I need to develop a plan for if things don't go right. That is, we get to the "restart SQL" phase and it won't start. So I was hoping to put my plan here and see what people thought.

1. Back up master - we already have backups of master nightly but back it up anyway.
2. Stop SQL
3. Make copies of master.mdf and master.ldf to another folder
4. Start SQL
5. Run the script to move tempdb
6. Restart SQL

7. If SQL won't start, move the copies I made of master.mdf and master.ldf back to the original folder; start SQL.
8. If SQL won't start (any reason it wouldn't?) then start in single-user mode, restore master

The reason I am looking at steps 2, 3, and 7 is because, in my view, it's a lot simpler. With step 8, I need to make sure that no services try to connect to SQL when I'm starting it in single-user mode; which means either mucking with the firewall to block them, or turning off all of the services I can think of on other servers. I figure that if I just put the original master files back, SQL should start okay (assuming no corruption during the copy, but that's unlikely).

Does this make sense? Anything I'm missing?
Divine Flame
Divine Flame
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2297 Visits: 2816
cphite (6/6/2013)
So this is one of those areas where I feel least comfortable... messing around with system files.

Our new production SQL server was set up with all of the system databases on the same VMDK - including tempdb - and it turns out that is bad for replication to our DR environment. So I have been tasked with moving tempdb to it's own separate drive. Now, moving tempdb itself isn't the issue - I've done it before, it's a relatively simple process - ALTER DATABASE with the new location for each file, restart SQL, and you're done.

The problem is, I need to develop a plan for if things don't go right. That is, we get to the "restart SQL" phase and it won't start. So I was hoping to put my plan here and see what people thought.

1. Back up master - we already have backups of master nightly but back it up anyway.
2. Stop SQL
3. Make copies of master.mdf and master.ldf to another folder
4. Start SQL
5. Run the script to move tempdb
6. Restart SQL

7. If SQL won't start, move the copies I made of master.mdf and master.ldf back to the original folder; start SQL.
8. If SQL won't start (any reason it wouldn't?) then start in single-user mode, restore master

The reason I am looking at steps 2, 3, and 7 is because, in my view, it's a lot simpler. With step 8, I need to make sure that no services try to connect to SQL when I'm starting it in single-user mode; which means either mucking with the firewall to block them, or turning off all of the services I can think of on other servers. I figure that if I just put the original master files back, SQL should start okay (assuming no corruption during the copy, but that's unlikely).

Does this make sense? Anything I'm missing?


The only reason that SQL server won't restart may be if you put a wrong directory path (like a path which doesn't exist) when executing ALTER DATABASE MODIFY FILE command. SQL Server recreates tempdb when it restarts & since you have provided a directory path which doesn't exist SQL Server will not be able to create tempdb & hence won't start at all.

In that case nothing will work i.e. starting the SQL Server in single user mode or restoring the master database, because when SQL Server is not starting it will not start in single user mode either Cool

So, what you can do in that case is start SQL Server with minimal configuration. This is done using the -f startup parameter. It will allow SQL Server to create the tempdb files at the default database file location. Once SQL Server is started you can again run ALTER DATABASE command for tempdb to change the path to correct one.

P.S. When you are taking backup of master database, it is always better to copy the mdf & ldf files for master, model & msdb databases.

It is always easy to replace database files for these databases rather than restoring them ;-)


Sujeet Singh
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