March 30, 2010 at 8:38 am
Okay, I've been running some tests on test machines, and I wanted to see how much of a pain it would be to restore to a computer with a different drive layout.
As expected, it seems to be a pain.
I put up the new server, installed SQL, shut it down, brought it back up with /m /c /T3608, restored master, brought it back up with /f /T3608. Master restore worked, and as expected, because I restored master from a server with a different drive layout, it can't find the other system DBs. Cool.
But now when I try to alter their locations, SQL crashes.
Here's some excerpts from the log:
startup:
2010-03-30 10:08:15.18 Server Registry startup parameters:
-d T:\Default\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf
-e T:\Default\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l T:\Default\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2010-03-30 10:08:15.18 Server Command Line Startup Parameters:
/f
/t 3608
Crash:
2010-03-30 10:08:16.60 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2010-03-30 10:18:06.41 spid51 Clearing tempdb database.
2010-03-30 10:18:06.41 spid51 Starting up database 'model'.
2010-03-30 10:18:06.41 spid51 Error: 17204, Severity: 16, State: 1.
2010-03-30 10:18:06.41 spid51 FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\model.mdf for file number 1. OS error: 2(The system cannot find the file specified.).
2010-03-30 10:18:06.41 spid51 Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
I'm confused by a couple of things here.
a) Why is it trying to open model? It does that whether I try to alter the location of model or msdb or tempdb first. I know the system can't find the file, that's why I'm trying to alter its location!
b) Why is it complaining about tempdb? I checked the location in the source master db that I restored from another system, and it's pointed to a location that does exist, that the sql user has rights to on the file system, and there's plenty of space.
Any ideas?
April 1, 2010 at 6:08 am
Hi,
It tries to open model to re create tempdb, and then as it can't create tempdb, it stops. If you want to specify a new location for model, you may need to start with -m -T3608, to freeze the restart process just after master has been recovered, and then connect to do the alter database model modify file...
HTH
David B.
David B.
April 1, 2010 at 6:40 am
If you are thinking of using this as a method to move to another computer, give up now. You are just at the start of the problems you are going to face. 🙂
---------------------------------------------------------------------
April 2, 2010 at 1:14 pm
Thanks David, that did the trick. I don't know why my original method didn't work, I was under the assumption /m /c /T3608 should prevent non-master DBs from starting up too. Maybe I just did something different this last time around, ugh.
Either way, its working now. Which makes George's warning all the more scary! What else is bound to go wrong? It was a simplistic test, but it did seem to work.
What's a better way to move to a different machine?
April 2, 2010 at 5:09 pm
well you already had one problem and had to post for a solution 🙂
where is your resource database? If its not in the same location as master your next attempt to upgrade will fail with an esoteric error message. There's a big gotcha waiting to happen.
are you going to move the error and agent logs as well or leave them where they are? The process and the install is starting to look messy.
The local accounts created by SQL on install are from a different machine so will now be orphaned. these are used by SQL to control file permissions so access problems are possible. A quick get around is to give the service account local admin, but thats not best practice. If there any other local accounts added as logins they will also fail.
you will have to run sp_addserver\sp_dropserver.
sys.master_files possibly points to a bunch of file locations that don't exist so user databases will have to be dropped and reattached.
Is this enough reasons as to why this is not the best way to restore a SQL instance on another computer? As the old joke about asking for directions starts: 'well you don;t want to be starting from here'
You ask for a better way? Move to a computer with the same drive layout if you are trying to duplicate the machine or do DR, and don't restore a master database from a different server, script out the objects from it and run the results into the new server. In SQL2005 and above same goes for msdb.
If I had to move an instance this would be the last way I would want to do it. Just my view on the matter.
---------------------------------------------------------------------
April 5, 2010 at 9:13 am
Haha, okay, noted 🙂 Thanks for the advice!
April 5, 2010 at 2:02 pm
george sibbald (4/2/2010)
sys.master_files possibly points to a bunch of file locations that don't exist so user databases will have to be dropped and reattached.
you can also perform a restore with replace moving the files to new locations on each database and that will work rather than dropping and attaching 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 5, 2010 at 2:57 pm
Perry Whittle (4/5/2010)
george sibbald (4/2/2010)
sys.master_files possibly points to a bunch of file locations that don't exist so user databases will have to be dropped and reattached.you can also perform a restore with replace moving the files to new locations on each database and that will work rather than dropping and attaching 😉
In this case drop\attach should be faster but yes that is an alternative but still an extra step caused by the different drive layout. If you get the drive layout the same user databases would magically reappear following a restore of master.
---------------------------------------------------------------------
April 6, 2010 at 1:10 am
Hi,
Just a quick word,
In SQL 2008 resource is not requested to be under the same location as master anymore. The resource files are stored in the binaries directory, you don't need to bother about moving resource.
David B.
David B.
April 6, 2010 at 7:02 am
yes its only user dbs and model, msdb to worry about
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 7, 2010 at 3:10 am
I agree with George that trying to move SQL to another server with a different drive layout is a risky thing to do. If you want to do this as a training exercise then go ahead, but if you are planning to use this approach for a DR then you have the wrong plan.
In a true, unplanned, DR you must consider there will be missing staff as well as missing servers. You need your DR plan to be as simple as possible or it is likely to fail. If you plan to restore to a server with different drive layout then much of this work cannot be scripted, which means you need someone with the right skills and the right amount of time available to do the DR.
If you are in a small installation with not many staff, then there simply may be no-one with the right skills available to do the DR. Hiring someone to do this work in a DR situation is asking for more mistakes to be made.
If you are in a large installation where you can expect enough skilled staff to survive the disaster, then you still have the wrong DR plan. Any audit of your plan will put a big bad mark against any part that needs ad-hoc manual work and require that the work is scripted.
The ideal in any DR plan is that any service can be made to work by anybody in the infrastructure team, regardless of if the service is in their specialist area. You need to make a plan that tries to achieve this.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 7, 2010 at 4:31 am
David BAFFALEUF (4/6/2010)
Hi,Just a quick word,
In SQL 2008 resource is not requested to be under the same location as master anymore. The resource files are stored in the binaries directory, you don't need to bother about moving resource.
David B.
Question I don't know the answer to............In SQL2008 if you restore a master database from a different server and the server the backup originated from had a different drive layout so binaries installed in a different location which in turn means the resource database was in a different location, would SQL be able to find the resource database after the master restore?
---------------------------------------------------------------------
April 7, 2010 at 4:37 am
I guess my concerns with this approach are as much about restoring master across servers per se as the different drive layout.
another issue you could hit would be around encryption unless both servers ran under the same service account. You would then have to restore the service master key from the originating server, so make sure you have a backup available!
---------------------------------------------------------------------
April 7, 2010 at 5:36 am
george sibbald (4/7/2010)
Question I don't know the answer to............In SQL2008 if you restore a master database from a different server and the server the backup originated from had a different drive layout so binaries installed in a different location which in turn means the resource database was in a different location, would SQL be able to find the resource database after the master restore?
the resource database by default is in the Binn folder. You can move it but MS say that any future service packs,etc will put it back to the default location. This is detailed in BOL.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 7, 2010 at 5:53 am
Perry Whittle (4/7/2010)
george sibbald (4/7/2010)
Question I don't know the answer to............In SQL2008 if you restore a master database from a different server and the server the backup originated from had a different drive layout so binaries installed in a different location which in turn means the resource database was in a different location, would SQL be able to find the resource database after the master restore?
the resource database by default is in the Binn folder. You can move it but MS say that any future service packs,etc will put it back to the default location. This is detailed in BOL.
Perry, that I am aware of, but will SQL still be aware of the correct resource db location if you restore a master database from a different server where the binn location was different?
i.e. does SQL rely on info in the master database or the registry? I expect and hope the registry but have never actually tried out this scenario.
---------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply