restore all databases, including system dbs, on one server to another

Question

restore all databases, including system dbs, on one server to another

KATHLEEN Y ZHANG

Hall of Fame

Points: 3534
More actions
August 31, 2006 at 8:27 am

#116493

Hello!
I was trying to take a snapshot of a db server and restore the whole thing on another server. I made sure that the data files on the new server are on the same location as on the old server. Basically, I stopped sql server service on both servers, copied all data files from the first server ( including system db files) over to the second server following the same file paths. The second server had nothing but the system dbs before I copied the files. Then I tried to restart sql server service on the second one, it was not able to restart. Is there anything I am missing? Though the first instance is called Hamster and the second instance is called Packmule\Two. Does it matter?
Thanks much for any help!
Kathleen
September 4, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Kathleen

Not that I've ever done this before, but I think you need to use the sp_addserver stored procedure to change the name of your server. See if you can start your server in single user mode to run it. If not, you may have to rebuild the master database first.

John

Deadwood Mr or Mrs. 500 Points: 522 More actions · Answer 2

Can you post what the application event log says in regards to this.

Thanks

KATHLEEN Y ZHANG Hall of Fame Points: 3534 More actions · Answer 3

Thanks all for the reply.

I actually was not able to bring sql server service online. So I could not do/view anything. John, I think you are right, if I could change the instance name, all the user databases should come up as they are on the original server.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Kathleen

Are you not able to start SQL Server from a command prompt, either? Look up the sqlservr syntax and try using the -c, -m or -f parameters. Using trace flag 3608 (-T3608) is also worth trying.

John

KATHLEEN Y ZHANG Hall of Fame Points: 3534 More actions · Answer 5

Hi, John, I ran the command

sqlservr.exe -f -m -s two

to try to start the instance and got the following error in sql server log:

2006-09-07 15:45:12.44 server Server Process ID is 808.

2006-09-07 15:45:12.44 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL$TWO\log\ERRORLOG'.

2006-09-07 15:45:12.47 server Warning: -f command line flag used...minimal server configured.

2006-09-07 15:45:12.47 server SQL Server is starting at priority class 'normal'(4 CPUs detected).

2006-09-07 15:45:12.64 server SQL Server configured for thread mode processing.

2006-09-07 15:45:12.66 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.

2006-09-07 15:45:12.78 server MS DTC initialization skipped because of start up option.

2006-09-07 15:45:12.80 spid3 Warning ******************

2006-09-07 15:45:12.80 spid3 SQL Server started in single user mode. Updates allowed to system catalogs.

2006-09-07 15:45:12.84 spid3 Starting up database 'master'.

2006-09-07 15:45:13.09 spid3 Server started with '-f'. Auditing will not be started.

2006-09-07 15:45:13.11 server Using 'SSNETLIB.DLL' version '8.0.766'.

2006-09-07 15:45:13.11 spid5 Starting up database 'model'.

2006-09-07 15:45:13.11 spid5 udopen: Operating system error 2(The system cannot find the file specified.) during the creation/opening of physical device C:\Program Files\Microsoft SQL Server\MSSQL\data\model.mdf.

2006-09-07 15:45:13.13 spid3 Server name is 'PACKMULE\TWO'.

2006-09-07 15:45:13.13 spid5 FCB:pen failed: Could not open device C:\Program Files\Microsoft SQL Server\MSSQL\data\model.mdf for virtual device number (VDN) 1.

2006-09-07 15:45:13.16 spid5 Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL\data\model.mdf' may be incorrect.

2006-09-07 15:45:13.19 spid5 Device activation error. The physical file name 'C:\Program Files\Microsoft SQL Server\MSSQL\data\modellog.ldf' may be incorrect.

2006-09-07 15:45:13.20 spid5 Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

I guess it is looking for all the system database files in the default folder, because they were in with the default instance originally. I will try to restore them from another machine as the default instance and see if it works. Not sure if the server name matters though.

Thanks much!

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Kathleen

OK, so you can either copy the model database files to the default location before you start SQL Server, or you can start SQL Server with the -T3608 flag and attach the model database with the files where they are.

John