Can you automate a restore process?

[pronet74]

I have a backup server in which our sql backup automatically gets copied to every night. When I run a restore on the backup server in enterprise manager it takes about 45 minutes for this to complete. The database is about 14GB in size. Since there is a way to automate the task of backing up a server, is there a way to automate the restore so that after the file gets copied over I can then have it automatically restore as well?

 

[ptheriault]

Yes, you can automote the whole thing.
Backup the database on Server A Then on Server B you would create a job that copies the file from the server A. The SQL Server agent account will need permissions to share where the .BAK file on server A exists.

use the windows copy or xcopy command to move the file.

Then step 2 of your job is the restore.
Here is an example of your restrore statement.

RESTORE [COLOR=blue]DATABASE[/color] AdventureWorks [COLOR=blue]FROM[/color] AdventureWorksBackups [COLOR=blue]WITH[/color] RECOVERY, MOVE [COLOR=red]'AdventureWorks_Data'[/color] [COLOR=blue]TO[/color] [COLOR=red]'C:FilesSQL Server.1.mdf'[/color], MOVE [COLOR=red]'AdventureWorks_Log'[/color] [COLOR=blue]TO[/color] [COLOR=red]'C:FilesSQL Server.1.ldf'[/color]

After the restore is done you should run
sp_change_users_login 'REPORT'
to make sure you don't have any out of sync user SIDS. If a user SID is out of sync then that user won't be able to connect to the database. You can correct those SIDS with ...

sp_change_users_login 'update_one', <'user_name'>,<'user_name'>

- Paul

- If at first you don't succeed, find out if the loser gets anything.