restore script needed

freddy12345

Hi,

i have a folder full of hourly backup files for our production database. the file names contain the date and time. i need a script that will restore the most recent backup in the given folder, onto a database of a different name (but on the same server).

i could write this using shell commands in TSQL, but thought i'd ask if you have something already written.

thanks!

Fred

Daniel Handley

Sorry we currently haven't written any scripts to do this, or recieved any that we can forward on.

However there are two ways to do this.
The first is to use the msdb databse in SQL Server which conatins the backup history of the server.
The second is to use the additioanl databse that SQB uses to store information.

To use the msdb you will have to use the following queries but add in your own filtering and join the tables appropriatly.
SELECT * FROM msdb..backupset ORDER BY backup_start_date DESC
SELECT * FROM msdb..backupmediaset
SELECT * FROM msdb..backupfiles


If you wish to use the SQB data store, which contains more information, then this can accesed via the sqbdata extended store procedure.
Again you will have to filter the information and then create a join.
master..sqbdata "SELECT * FROM backupfiles"
master..sqbdata "SELECT * FROM backuphistory"

Regards
Dan

aarons44

Fred,

email me and I can send you a script that might be similar to the one you are looking for. I have a script that queries a folder to get a list of database backups, stores the list in a recordset, and loops through the recordset, using SQLBackupC to restore the databases. It also runs scripts to change some tables because the databases are being restored on a different server, and uses the "With Move" option for that same reason. I'd post it here, but it is long.

Aaron

Daniel Handley

If you send the script in to support(at)red-gate.com i can always post it on our ftp site for retrieval.

It would also be useful for us to have to give out to other cutomers.

Thanks
Dan

aarons44

Ok. Let me just change some database names and I'll send it in.

Daniel Handley wrote:

If you send the script in to support(at)red-gate.com i can always post it on our ftp site for retrieval.

It would also be useful for us to have to give out to other cutomers.

Thanks
Dan

Daniel Handley

Thanks
I will post the location to this thread once i have recieved it.

Dan

Daniel Handley

This script is now avalable on the ftp site.
Here is a direct link to it.
ftp://support.red-gate.com/SQB_Scripts/ ... st5020.zip

If anyone make any improvemnts please let us know and we can update the script or have a new one.

Regadrs
Dan