Script to restore red gate backups automatically from a folder.

  • We are trying to keep our test environment in sync with our production. In order to achieve that, we need our red gate backup files to be restored periodically on our test environment.

    I currently have the red gate backup files stored in separate folders for each database. I need a script to search these folders and then automatically restore these files for the specific database in our test environment.

    The reg gate backup files for a database like test are stored in a folder called d:\backups\test.

    I am looking to automate this process of restoring the backups from this folder. The script needs to find the latest files and restore them to the database.

    Any help in this regard would be appreciated.

    Thanks

    Codezilla

  • maybe you should ask folks from redgate ?

  • Thank you so much for replying.

    But I am looking for a script to restore databases regardless of whether they are red gate backups or regular backups. Something which is done automatically as a job.

  • I've done it by putting the regular restore script into a job, with additional steps to fix the logins. In my case, the backup file had the same name each night so I didn't have to figure out which one to use. I'm not sure if the system backup history tables have the path & file name that got created. I don't see that information, but maybe I missed it.

  • Yeah that sounds about right if the backup file name does not change. In my case the file name changes with a data parameter in it. So any ideas on how do search a directory for files before I do the restore.

  • I'd suggest to create a linked server to your production server and then query the system tables on your production server to see what the filename(s) of the last full backup(s) is/are. You can even go so far to also query for any Incremental backup file names after that full to stay completely up2date.

  • Maybe a script like this would help you find the most recent backup and it's path:

    select top 1 bset.database_name, bset.Type, media.physical_device_name, backup_start_date

    from msdb.dbo.backupset bset

    join msdb.dbo.backupmediafamily as media on bset.media_set_id = media.media_set_id

    where database_name = 'Database_Name'

    and type = 'D' -- Full

    order by backup_start_date desc

  • Codezilla, have you find a solution to your restore script yet ? I'm also looking for a script to restore 10 databases that I can run whenever needed. The backup files name changes everyday (it has the date attach to the database name) but the location of the backup files and the databases name are not. What I am doing for now is:

    restore database dbname

    from disk = 'path:\dbname_yyyymmddhh.BAK'

    with dbo_only, -- this ensure that only user that have access to this db can access the db

    replace, --to replace the pre-existing db with the same name

    move 'logicaldbname' to 'path:\physical_dbname.mdf',

    move 'logicaldb_logfilename' to 'path:\physical_dbname_log.ldf'

    go

    I do this for all of my 10 databases that I need to restore. Yes, painful, but you got to do what you got to do.

    However, what I would like to do or find a way to do :

    1. Get all the backup files (*.BAK) from the path:\ put them into a temp table

    2. loop through the temp table created above and set @dbvar for the databasename and set @bkfile for that database backup files

    3. then restore the dbs by putting in the @dbvar and @bkfile variables.

    4. drop the temp table from step 1.

    This pose a few problems. How can I find out what the logical and the physical name for the datafile and the logfile.

    I'll be so happy if some one can help with this. If I find the solution for this, I'll post it here.

    TIA

  • you need to create a SSIS package to search & select a backup file

    you can use the below mentioned query of REDGATE which can be used to restore the DB.

    EXECUTE master..sqlbackup N'-SQL "RESTORE DATABASE [TESTDB]

    FROM DISK = ''K:\FULL_(local)_TESTDB_20091122_003838.sqb''

    WITH STANDBY = ''G:\BACKUP\Undo_TESTDB.dat'',

    MOVE ''testdb_ext4Data'' TO ''I:\Database\TESTDB\TESTDB_1.MDF'',

    MOVE ''testdbData'' TO ''J:\Database\TESTDB\TESTDBDATA.NDF'',

    MOVE ''testdb_ext2Data'' TO ''J:\Database\TESTDB\TESTDB_EXT2DATA.NDF''"'

    HINT:

    in SSIS package pick backup file names

    takeout substring of the name which has date_time value and on the basis of the same you can decide which one to resotore.

    Happy Coding 😉

    Regards,
    Sarabpreet Singh 😎
    Sarabpreet.com
    SQLChamp.com
    Twitter: @Sarab_SQLGeek

  • Thank you Sarabpreet for your response. However, I'm on SQL2000.

    Also, I found a way to get the logical and physical name for the database file and the logfile.

    use dbname

    sp_helpfile

    I'm getting close.

  • Thank you Sarabpreet for your response. However, I'm on SQL2000.

    Also, I found a way to get the logical and physical name for the database file and the logfile.

    use dbname

    sp_helpfile

    I'm getting close.

  • Thank you Sarabpreet for your response. However, I'm on SQL2000.

    Also, I found a way to get the logical and physical name for the database file and the logfile.

    use dbname

    sp_helpfile

    I'm getting close.

  • How to include the "stats and Replace" options?

    EXECUTE master..sqlbackup '-SQL

    "RESTORE DATABASE [ABCD] FROM DISK = ''D:\backup\file.sqb''

    WITH RECOVERY

    , MOVE ''abc_1'' TO ''F:\MSSQL\Data\as.mdf''

    , MOVE ''abc_1_log'' TO ''G:\MSSQL\Logs\2_log.ldf''"'

    --, Stats = 10

    --, Replace'

    Thanks.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply