June 6, 2013 at 6:28 am
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?
June 6, 2013 at 7:10 am
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 😎
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 😉
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply