SQL Server 2000 automated restores 'From Device'

[stevenharrisesquire]

Hi there,

I want to automate the process of restoring a database from a backup file (i.e. .bak) which I can do manually by choosing 'From Device' during the 'Restore' wizard.

Does anyone know of any way to automate this process?

Thanks and regards,

Steve Harris

 

[SQLBill]

Did you use a wizard to create the .bak file or did you run the sp_addumpdevice command to create a backup device?

This is the way I would do it to automate it as a job...

create a backup device:
use master
go
exec sp_addumpdevice 'disk', 'mydbbackup', 'C:\backup\mydbbackup.bak'
go

Then create a job for backups:

use mydatabase
go
backup database mydatabase to mydbbackup with init
go

Then for restores, I would run:

use mydatabase
go
restore database mydatabase
from mydbbackup
go

You could automate this as a JOB, or create a procedure and run the procedure whenever you need to do a restore.

-SQLBill