SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Script to restore red gate backups automatically from a folder.


Script to restore red gate backups automatically from a folder.

Author
Message
Codezilla-292868
Codezilla-292868
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 400
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
Marcin Gol
Marcin Gol
SSC-Addicted
SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)

Group: General Forum Members
Points: 420 Visits: 178
maybe you should ask folks from redgate ?
Codezilla-292868
Codezilla-292868
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 400
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.
homebrew01
homebrew01
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6413 Visits: 9177
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.



Codezilla-292868
Codezilla-292868
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 400
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.
fhooglander
fhooglander
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 82
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.
homebrew01
homebrew01
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6413 Visits: 9177
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





newdb
newdb
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 321
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
Sarab_SQLGeek
Sarab_SQLGeek
Say Hey Kid
Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)Say Hey Kid (690 reputation)

Group: General Forum Members
Points: 690 Visits: 558
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 Cool
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
newdb
newdb
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 321
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search