Log Shipping (sort of) with Standard Edition

I have about 30 Databases on a Production SQL Server STANDARD Edition 2000k Instance.

All Backups are performed using SQL Server Maintenance plans (Full Nightly backups and periodic Xaction log backups during business hours)

I would like the ability to copy the FULL Backups to another Server - perhaps on a nightly basis - and then restore them to this other Server as a means of having some sort of Disaster Recovery.

I know with Enterprise Edition, you can set up linked servers and do log shipping or replication, but I don't have that luxury here. I also don't have access to the MS Backoffice Resource Kit - which I understand has some canned scripts to do what I want to do here.

My problem really is that because I don't have control over the backup filenames, how to I automate the restore process on the second Server?

any help would be much appreciated.You could try using the Scripting.FileSystemObject to perform a directory listing of the files in a particular directory. Then use a DO...LOOP to scroll through the list, copying the file and then restoring it to a remote server.

Alternatively, SQL keeps track of the file names used for backups in the msdb database. Look at the following tables:

backupfile
backupmediafamily
backupmediaset
backupset

Finally, you might just consider having the backups run out over the network to a remote server. In the event the primary node fails, you could restore the backups to an alternate server pretty quickly. Just remember that you also need to script off logins, jobs and save DTS packages (as binary files). You then also need to "exercise" your disaster recovery plan on a periodic basis to validate/verify your processes.

Regards,

hmscott|||Thank you for your reply!

I am actually already doing backups locally as well as to another Server on the Network - My problem was how to 'programatically' restore these backups on the remote server.

I found this script - posted by someone else on another forum...

It is SWEET!!!! As long as my backups are in a certain format and all in the same directory, this store procedure will check the folder for the newest .bak file for a specific database and restore it!!!!

So now what I have done is set up a maintenance plan on my prod server to do a full set of backups to this 'network' server - then I just schedule this stored procedure to run on the 'network' server and my databases are automatically refreshed..

Thanks again for your help|||Hi,
Can you let me know where you got that script...
Looking to do something similar.

Thanks,
-jmr|||I would also like to see that script.