master restore

[beechill4]

Hi,

i am moving all databases from one server to another.

I have just installed sql server 2000 on the new server and copied across backups of all databases (user and system) to this new server.

i want to restore the master database first (i take it this is best practice?). therefore, i am trying to put sql server in single user mode using

sqlservr.exe -m.

can someone tell me what the expected output to screen should be for that command?

this is what i get but it looks like it has not completed?

C:\Program Files\Microsoft SQL Server\MSSQL\Binn>sqlservr.exe -m
2007-02-05 14:40:59.09 server Microsoft SQL Server 2000 - 8.00.194 (Intel X8
6)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

2007-02-05 14:40:59.09 server Copyright (C) 1988-2000 Microsoft Corporation.

2007-02-05 14:40:59.11 server All rights reserved.
2007-02-05 14:40:59.11 server Server Process ID is 1444.
2007-02-05 14:40:59.11 server Logging SQL Server messages in file 'd:\mssql\d
ata\MSSQL\log\ERRORLOG'.
2007-02-05 14:40:59.11 server SQL Server is starting at priority class 'norma
l'(2 CPUs detected).
2007-02-05 14:40:59.17 server SQL Server configured for thread mode processin
g.
2007-02-05 14:40:59.19 server Using dynamic lock allocation. [2500] Lock Bloc
ks, [5000] Lock Owner Blocks.
2007-02-05 14:40:59.22 server Attempting to initialize Distributed Transactio
n Coordinator.
2007-02-05 14:41:00.25 spid4 Warning ******************
2007-02-05 14:41:00.25 spid4 SQL Server started in single user mode. Updates
allowed to system catalogs.
2007-02-05 14:41:00.25 spid4 Starting up database 'master'.
2007-02-05 14:41:00.33 server Using 'SSNETLIB.DLL' version '8.0.311'.
2007-02-05 14:41:00.33 spid5 Starting up database 'model'.
2007-02-05 14:41:00.33 spid4 Server name is 'TEST'.
2007-02-05 14:41:00.34 spid4 Starting up database 'msdb'.
2007-02-05 14:41:00.34 server SQL server listening on 10.99.0.120: 1433.
2007-02-05 14:41:00.34 server SQL server listening on 127.10.9.1: 1233.
2007-02-05 14:41:00.36 server SQL server listening on TCP, Shared Memory, Nam
ed Pipes.
2007-02-05 14:41:00.36 server SQL Server is ready for client connections
2007-02-05 14:41:00.38 spid5 Clearing tempdb database.
2007-02-05 14:41:00.89 spid5 Starting up database 'tempdb'.
2007-02-05 14:41:00.95 spid4 Recovery complete.

am i now in single user mode?

 

[ptheriault]

Yes you are in single user mode.... But you don't need to and shouldn't move the master database. The only thing that is in the master database that you need is your login information. Use sp_help_revlogin to move your logins.
Do you need your other databases on-line while they are being moved. If the answer is no the detach them in bulk, copy them to the new server an re-attach them.


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[beechill4]

Paul,

if i get you right you are saying:

leave master alone
script logins and re run them.
attach all the user databases.

what about model and msdb?

Thanks

btw, master restored successfully for me

 

[ptheriault]

correct, Leave master alone. script your logins and attach all user databases. I would also just move the msdb. That is the database that has all you job information.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[beechill4]

paul,

get you.

so attach msdb, and do nothing with model?

 

[ptheriault]

Correct,
Although, restore the msdb database from a backup or if you shut down the other server just copy the mdf and ldf files. You can't detach msdb.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

Leave the model database alone. This is simply the template the SQL Server uses to create new databases.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLWilts]

Paul,
You can detach the MSDB database, but only using the -T3608 switch. Stopping the service and moving the MDF and LDF files is prob the best way to go if you can get away with doing that though.
I do have a script that will script out all of the logins, and their passwords that can then be run on another server if that would be useful to you...I plagurized it from somewhere on the web and adapted it to my own needs.

 

[SQLBill]

The only thing to be concerned about with the model database is if you made changes to it. As MrDenny says, its sole purpose is to be the template for new databases. Its settings are used as the default if you don't specify them when creating a database. Most people never touch the Model database after it's been installed. Where I work, we immediately change the recovery mode from full to simple, but that's no reason to move it to a new location.

-SQLBill

Posting advice: FAQ481-4875