Restore MSSQL server

[bertieuk]

I have both SQL 7 and 2000.

I use maintenance plans for both servers. All databases fine.

I have tested disaster recovery and have restored fine using the microsoft info.....This takes each database backup file and RESTORES. I have currently stored the commands in a text file and paste them into Query Analyser.
An example of this file is below:

RESTORE DATABASE master FROM disk='d:\backup\master\master_db_200406242300.bak'
WITH MOVE 'master' to 'd:\mssql7\data\master.mdf',
MOVE 'mastlog' to 'd:\mssql7\data\mastlog.ldf',
REPLACE
go

-------------------------------------------

USE master
go
UPDATE sysdatabases SET filename='d:\mssql7\data\tempdb.mdf' WHERE name='tempdb'
go
ALTER DATABASE tempdb MODIFY FILE (name = tempdev, filename = 'd:\mssql7\data\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE (name = templog, filename = 'd:\mssql7\data\templog.ldf')
go

------------------------------------------------

exec sp_detach_db N'model'
go


The procedure also requires stopping and starting the server in single user mode.

I would like to automate the whole process including the restore of the system databases and the stopping/starting the server in different modes. Ideally I would like to have all backup .bak databases in a directory and a script to cycle through all and restore. Where do I start? I am experienced with vb if it helps with the scripting.

Thanks

Si

 

[nigelrivett]

You could probably do it from a batch file.
Net start / net stop for the start/stops and osql for the sql commands.



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[mutley1]

Hi Si,

As Nigel said, the best way would be to do a batch file. If you are using full recovery model (i.e. backing up transaction logs in between full DB backup), then you may be able to use the script at the following URL to compile what needs to be restored on the user DB side. Basically, I would use Nigel's suggestion to reinstate the master AND the msdb, then run this script. It reads the backup information in msdb and compiles a script from the info as to what needs to be restored. e.g. if you backed up last night and had 4 tran log backups since, it will compile the script for the relevant items. Have a read and a play with it, but it is very handy.

http://www.databasejournal.com/features/mssql/article.php/2174411

Matt.