How to do this in sql

[menark]

I have the need to back up a database everynight (I can schedule this) but I also need to restore that database over our test database everynight??? Is there anyway to automate this??? (the restore part)??

 

[DrSql]

Here is an eample
[tt]
--Prod Script to Schdule
Backup database NORTHWIND
to DISK= 'F:/MSSQL/Backup/NORTHWIND.bak'
with init, stats = 10, name = 'NORTHWIND Full Backup'


--DEV Script to schedule
RESTORE DATABASE NORTHWIND
FROM Disk = 'F:\Mssql\Backup\NORTHWIND.bak'
WITH STANDBY = 'F:\MSSQL\Backup\undoNORTHWIND.ldf'
, MOVE 'NORTHWIND_Data' TO 'E:\MSSQL\Data\NORTHWIND_data.mdf',
MOVE 'NORTHWIND_log' TO 'F:\MSSQL\Log\NORTHWIND_log.ldf'
[/tt]

TO DISK in PROD and FROM DISK in DEV can be a mapped drive.
'\\ServerName\Backup$\NORTHWIND.BAK'

Also you can use XP_CMDSHELL to copy the backup from prod to dev server. Depending the sixe of the db.
Samll db network restore is fine, bigger db, I would rather copy to loacally and restore.

Dr.Sql
Good Luck.