Restore System DBs to another server

Last Post 07 Jul 2009 02:58 AM by yupsay. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
dbbyleo
New Member
New Member

--
24 Mar 2003 07:47 AM
Hi Guys...I'm new to SQL and have been mostly an Oracle DBA. I'm starting to support SQL at work and what I am basically trying to do is to restore my SQL Server 2000 to another server. I am assuming this is a practical thing to do.

Scenario: serverA is our production SQL server. I take full backups of the master, msdb, model, and all the user databases to tape. serverA burns down. We setup serverB with the same configuration with the exception of the file structure not being the same...otherwise - memory, CPU, etc are all the same. We installed a fresh copy of SQL Server 2000 on serverB and patched it to the same level as before (SP3).

Based on a doc from MS support, this is what I've done so far:

1.) Shutdown the newly installed SQL server (on serverB).
2.) Start SQL server in single-user mode (with sqlservr -c -m ).
3.) Connect with Query Analyzer and issue the following command:

restore database master from disk = 'C:\MSSQL\backups\New Folder\masterBackup.bak'
with
move 'master' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\master.mdf' ,
move 'mastlog' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\mastlog.ldf' ,
replace

(This execute successfully and SQL server shutsdown automatically as expected).

4.) Start SQL server (with sqlservr -c -f -T3608)
5.) Connect with Query Analyzer and issue the following command(s):

update sysdatabases set filename='C:\Program Files\Microsoft SQL Server\MSSQL\Data\tempdb.mdf' where name='tempdb'

(This execute successfully)

alter database tempdb modify file (name = tempdev, filename = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\tempdb.mdf')
alter database tempdb modify file (name = templog, filename = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\templog.ldf')

(This is were I get the error

[Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.

Connection Broken

When I looked in the ERRORLOG file for SQL server, I find that it tries to start the model database when I issue the last command (alter database...). And of course, the master database now thinks the model database datafiles are located in a physical location that does not exist in this server...and thus, errors out.

I've been trying to get support in this issue and not even Premier Support from MS can help. It's been 1 week and 4 different technicians from MS.

I'm starting to realize that something as practical as this: to be able to restore the entire database server and databases to another machine - is not as practical/easy in SQL server as one would think. However, I still refuse to believe that this is not something that can be easily on SQL databases....it just seems so basic and

Can you confirm this impression I am starting to have. And if it is something done often in practice by most SQL DBAs, then could someone shed some light on this for me.
dbbyleo
New Member
New Member

--
24 Mar 2003 02:03 PM
RM...Thanks for the reply. This doc (224071) seemed to be lacking some conherency. It doesn't really go thru the actually steps of restoring the entire system databases as a whole...meanining, it doesn't explain that there's a specific order to follow to restore system databases (master, msdb, and model). Doc 304692 (the doc I previous followed) seemed to lead the right direction, but I only ended up with errors during the steps instructed.

I actually spoke with another Premier MS support tech in the meantime. In this session he was also not successful in helping me. His suggestions included creating and using virtual drives to fool SQL that such a file location exists. At that point, I was still in awe at that fact that I would have to go down to the OS level to facilitate this. But at the same time, he also explained to me the startup sequence of SQL (how it handles and the order it needs that system databases)...info I hadn't know before.

Having said that....

After understanding a bit more of the startup process of SQL, and after seeing your suggestion to read doc# Q224071, I decided to have another glance at this doc.

And let me say this: Only til now...after 1 week of technical support, 4 different MS technicians, 1 forum thread, and 1 Senior forum member....did "I" finally find my solution!

So...here it is for anyone else you would come to trying to figure the path to this process:

1.) Restore the Master db on the new box from the backup taken from the old box:
a.) Start SQL (on new box) in single user mode. From Start>Run type: sqlservr -c -m
b.) Connect to SQL using Query Analyzer.
c.) Issue the RESTORE DATABASE for master using the MOVE and REPLACE option.

Example:

restore database master from disk = 'C:\MSSQL\backups\New Folder\masterBackup.bak'
with
move 'master' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\master.mdf' ,
move 'mastlog' to 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\mastlog.ldf' ,
replace

SQL shuts down upon completion of this command. On the next step (when you startup SQL with sqlservr -c -f -T3608), SQL starts up only the master database. Since the master database has been restored from the previous box, it also has the information of the other databases from the previous box, including where their datafiles should be located....a location that doesn't exist on this new box. Therefore we have to change those information in the master database before we can start SQL in normal operation mode. The first thing we need to do is to have an available model databases for SQL to use. However, because we can not modify the model database information in master without having an available tempdb....and we can't modify the tempdb database information in the master without having an available model database, we will have to detach the previous model database and attach the model database that is on this new box.

2.) Detach/Attach the model database using the following instruction:
a.) Start SQL using the following options (sqlservr -c -f -T3608)
b.) Connect to SQL via Query Analyzer.
c.) Detach the model database (of the old box): sp_detach_db 'model'
d.) Attach the model database (of the new box).

Example:

sp_attach_db 'model','C:\Program Files\Microsoft SQL Server\MSSQL\Data\model.mdf','C:\Program Files\Microsoft SQL Server\MSSQL\Data\modellog.ldf'

3.) Update the system databases of the tempdb datafile location on this new box:
a.) Using Query Analyzer to update the system of the tempdb's new datafile location using the UPDATE SYSDATABASES and ALTER DATABASE command.

Example:

use master
go
update sysdatabases set filename='C:\Program Files\Microsoft SQL Server\MSSQL\Data\tempdb.mdf' where name='tempdb'
go
alter database tempdb modify file (name = tempdev, filename = 'C:\Program Files\Mic
winmonid
New Member
New Member

--
20 May 2008 06:23 PM
Hi,

I am also facing this same problem. The network error. Can you please, tell me, how did do the restoration.

- Monica
yupsay
New Member
New Member

--
07 Jul 2009 02:58 AM
Well, It seems I've found a workaround for restiring the original backups from the old system. And this is when the temp db is not available. If any one is still seeing this thread let me know.
You are not authorized to post a reply.

Acceptable Use Policy