Restoring master via a .cmd file on the SQL server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Restoring master via a .cmd file on the SQL server

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-02-24 : 08:23:53

Geoffrey writes "I would like to restore the master database onto our DSR server every night followed by the our company database with no recovery.

The databases are restored with out any problem using isql.

I know that master requires the "sqlservr -c -m" to be run, this I have done but it will not continue with the next command which is the restore. I have restarted the server to no avail.

The code I use is as follows:

Rem Stop SQL Server

net stop sqlserveragent /Y
net stop mssqlserver /Y

Rem ***SQL Server in single user mode

c:

cd \program files\microsoft sql server\mssql\binn

sqlservr -c -m

net start mssqlserver /Y
net start sqlserveragent /Y

isql -S STR_CLS1TEST -U sa -P STR_CLS1TEST -d master -Q "Restore database Master from Masterfull with recovery"

net stop sqlserveragent /Y
net stop mssqlserver /Y

net start mssqlserver /Y
net start sqlserveragent /Y

isql -S STR_CLS1TEST -U sa -P STR_CLS1TEST -d master -Q "restore database DBName from DBName Full with recovery"

Any pointers gratefully received.

Thank you for your consideration in this matter.

Regards

Geoffrey"

MuadDBA

628 Posts

Posted - 2004-02-24 : 10:34:33

Well, there are a couple things wrong there. First, by running SQLSRVR -M, you are effectively starting the SQL server service. You don't need to do a net start for that.

Second, if it's in single-user mode, if you start the agent, that will use up the one connection you need to do your other queries...so don't do that, either.

Finally, the process that starts the SQLSRVR.EXE will be dedicated to running that until you end that process or stop the SQL Server service. You have to do any other queries via a different script.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-24 : 12:36:46

And why would you want to restore master on the disaster recovery server? You should only restore the user databases. To get everything else across like logins, you should have scripts to do this.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources