Script to restore databse at 12.00

Berg

Hi!

I have 2 databases.

1 Sharp database
1 Test database

That i hope to do is:
Restore sharp database at 12.00 PM to test database to have newest data and all that every day.

Sharp database is backup at 1.00 AM every day.

Not ask why for its not my idea

Please help me

Brian Donahue

Hi Berg,

No problem. ;-)

The basic idea would be to create a job in the SQL Server Agent. In this job, you can specify a script like the following, and add a daily schedule to expidite the script daily at 1200.

exec sqlbackup '-sql "RESTORE DATABASE [test] FROM disk=''\\sharpserver\backupshare\sharpfull.sqb'' WITH PASSWORD=''xxx'', RECOVERY, REPLACE"'

There could be problems, potentially. If users are connected to the database, I think you need some way of ensuring their connections are closed, otherwise, you might get a 'database in use' error. If the SQL Server where SHARP normally resides is a different SQL Server, then you'll need to create a share on that server and get SQL Backup on test to restore from that share. In order to make this work, the security identity for the SQL Backup Agent Service needs to be a Windows domain user who has access to the share.

If TEST and SHARP are on the same server, then the disk location should be a local drive.

If you are restoring a full backup, then the file name of the backup must be known, unlike log restores, which can be restored with a filespec of wildcard (*). So make sure that in your full backup job for SHARP, you are using the same file name. If you want to overwrite an existing file with the same name, then don't forget to do the backup with the INIT keyword or you will get a 'can't overwrite: file already exists' error.

I hope I've covered all of the bases!