Can anyone tell me what the restore of procedure should be.
I can restore the user database, but I am having problems with the master, I
can't seem to start it in single user mode. Recovery Mode is as follows
master Recovery=FULL
model Recovery=FULL
msdb Recovery=SIMPLE
rsms Recovery=FULL
tempdb Recovery=SIMPLE
Backup is as follows
BACKUP DATABASE [master] TO DISK = N'D:\SQL Data
Files\RSMS\Backups\Master.bak' WITH INIT , NOUNLOAD , NOSKIP , STATS =
10, NOFORMAT

BACKUP DATABASE [msdb] TO DISK = N'D:\SQL Data Files\RSMS\Backups\msdb.bak'
WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

BACKUP DATABASE [model] TO DISK = N'D:\SQL Data
Files\RSMS\Backups\model.bak' WITH INIT , NOUNLOAD , NOSKIP , STATS =
10, NOFORMAT

BACKUP DATABASE [rsms] TO DISK = N'D:\SQL Data Files\RSMS\Backups\RSMS.bak'
WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT

BACKUP LOG [RSMS] TO DISK = N'D:\SQL Data Files\RSMS\Backups\RSMS.TRN'
WITH INIT

What I've done is setup a test (development) server and want to restore the
db here...
RESTORE DATABASE [rsms] FROM DISK = 'C:\Temp\RS_Server Backups\rsms.bak'
WITH FILE = 1, NOUNLOAD , STATS = 10, NORECOVERY , REPLACE , MOVE
'master' TO 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\master.mdf',
MOVE 'RSC_LOG' TO 'D:\Program Files\Microsoft SQL
Server\MSSQL\Data\Gallatin_log.ldf'

now I just need to do the same with the other tables but can't seem to do
this,
i've done a NET STOP MSSQLServer and then a sqlservr –m to start the db in
single user mode, but this seems to be hanging up...
Am I doning something wrong?
Brian

I understand that you want to restore databases from Some Server to test
(development) server. For restoring the Master DB, you have tried to start
the SQL Server in Singfle User Mode by executing following command from
Command Prompt:
sqlservr –m

Now, at the Command Prompt, you must be seeing an open window with some
messages.

Now connect in the Query Analyser, and run the script to restore the Master
DB.

If this doesn't work, then post any error message which you are getting, the
messages which you are getting after executing 'sqlservr –m', and the script
which you have used to restore the Master DB.

Best Wishes,
Absar
"Brian Shafer" wrote:

Hi

Check out
http://support.microsoft.com/kb/224071/EN-US/
and
http://support.microsoft.com/default...;en-us;Q314546

John

"Brian Shafer" wrote:

When I do that with the following prodcedure :
RESTORE DATABASE [master] FROM DISK = 'C:\Temp\RS_Server Backups\master.bak'
WITH FILE = 1, NOUNLOAD , STATS = 10, NORECOVERY , REPLACE ,
MOVE 'master' TO 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\master.mdf'

I get
Server: Msg 3108, Level 16, State 1, Line 1
RESTORE DATABASE must be used in single user mode when trying to restore the
master database.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.


The command window is still open and looking at the log file this is what is
processed:
2005-12-06 11:51:45.86 server Microsoft SQL Server 2000 - 8.00.760
(Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

2005-12-06 11:51:45.86 server Copyright (C) 1988-2002 Microsoft
Corporation.
2005-12-06 11:51:45.86 server All rights reserved.
2005-12-06 11:51:45.86 server Server Process ID is 4152.
2005-12-06 11:51:45.86 server Logging SQL Server messages in file
'D:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2005-12-06 11:51:45.88 server SQL Server is starting at priority class
'normal'(2 CPUs detected).
2005-12-06 11:51:45.93 server SQL Server configured for thread mode
processing.
2005-12-06 11:51:45.93 server Using dynamic lock allocation. [2500] Lock
Blocks, [5000] Lock Owner Blocks.
2005-12-06 11:51:45.97 server Attempting to initialize Distributed
Transaction Coordinator.
2005-12-06 11:51:47.00 spid2 Starting up database 'master'.
2005-12-06 11:51:47.11 server Using 'SSNETLIB.DLL' version '8.0.766'.
2005-12-06 11:51:47.11 spid5 Starting up database 'model'.
2005-12-06 11:51:47.13 spid2 Server name is 'WINTEST'.
2005-12-06 11:51:47.13 spid8 Starting up database 'msdb'.
2005-12-06 11:51:47.13 spid9 Starting up database 'pubs'.
2005-12-06 11:51:47.13 spid11 Starting up database 'RSMS'.
2005-12-06 11:51:47.13 spid10 Starting up database 'Northwind'.
2005-12-06 11:51:47.15 server SQL server listening on 172.16.30.28: 1433.
2005-12-06 11:51:47.15 server SQL server listening on 127.0.0.1: 1433.
2005-12-06 11:51:47.29 spid5 Clearing tempdb database.
2005-12-06 11:51:47.32 server SQL server listening on TCP, Named Pipes.
2005-12-06 11:51:47.33 server SQL Server is ready for client connections
2005-12-06 11:51:47.79 spid5 Starting up database 'tempdb'.
2005-12-06 11:51:47.88 spid2 Recovery complete.
2005-12-06 11:51:47.88 spid2 SQL global counter collection task is
created.

Any Help
Brian

"Absar Ahmad" wrote:

Before executing the command for Master DB Restoration, please verify that
you have followed the instruction for starting SQL Server given at the
following location:
http://msdn.microsoft.com/library/de...tartp_4okl.asp

How to start the default instance of SQL Server in single-user mode (Command
Prompt)
To start the default instance of SQL Server in single-user mode from a
command prompt

From a command prompt, enter:
sqlservr.exe -c -m

Note You must switch to the appropriate directory (for the instance of
Microsoft® SQL Server™ you want to start) in the command window before
starting sqlservr.exe.

"Brian Shafer" wrote:

John,
That was useful info. However, I didn't see anything that talked about
restoring the master db from a backup to a different location from the
orginal? And as you can see from what I've posted, are there any "gottha's"
in my scripts? I want to be able to rebuild my database if something were to
happen, and I am having a hard time proving I can.

Thanks,
Brian

"John Bell" wrote:

Hi

As Absar pointed out you should start the server with the -c and -m flags. I
am not sure why you backup the log for RSMS unless there is activity in
between the full backup and the log backup.

John

"Brian Shafer" wrote:

It is not likely right now, as the total process time of all backups is under
2 seconds, that the log file will be written too. But by doing a backup of
the log, don't I ensure that the log fill will not get full and cause
database problems?
Brian

"John Bell" wrote:

Correct. Or you can set the database to simple recovery. But if you do have modifications between
the db backup, do take log backups. sooner or later, you want to do point in time restore, or more
frequent log backups to minimize potential data loss.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/


"Brian Shafer" <BrianShafer (AT) discussions (DOT) microsoft.com> wrote

Hi Brian

I would assume that you have another job that appends log backups to
RSMS.TRN a given intervals before this job is called again?

John

"Brian Shafer" wrote: