What are the challenges you face when working across database platforms? Take the survey
Options

restore script needed

freddy12345freddy12345 Posts: 19 Bronze 2
edited June 15, 2007 4:26AM in SQL Backup Previous Versions
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

Comments

  • Options
    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
    Daniel Handley
    Red Gate Software Ltd
  • Options
    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
  • Options
    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
    Red Gate Software Ltd
  • Options
    Ok. Let me just change some database names and I'll send it in.
    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
  • Options
    Thanks
    I will post the location to this thread once i have recieved it.

    Dan
    Daniel Handley
    Red Gate Software Ltd
  • Options
    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
    Daniel Handley
    Red Gate Software Ltd
Sign In or Register to comment.