Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Script to restore red gate backups automatically from a folder. Expand / Collapse
Author
Message
Posted Sunday, October 11, 2009 6:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 14, 2014 10:55 AM
Points: 116, Visits: 369
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
Post #801294
Posted Sunday, October 11, 2009 7:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, October 3, 2010 3:50 AM
Points: 146, Visits: 178
maybe you should ask folks from redgate ?
Post #801299
Posted Monday, October 12, 2009 7:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 14, 2014 10:55 AM
Points: 116, Visits: 369
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.
Post #801551
Posted Monday, October 12, 2009 7:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 6:43 PM
Points: 2,838, Visits: 8,570
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.


Post #801570
Posted Monday, October 12, 2009 8:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 14, 2014 10:55 AM
Points: 116, Visits: 369
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.
Post #801587
Posted Monday, October 12, 2009 3:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 17, 2010 3:04 PM
Points: 157, 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.
Post #801865
Posted Saturday, October 17, 2009 11:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 6:43 PM
Points: 2,838, Visits: 8,570
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




Post #804704
Posted Wednesday, February 24, 2010 7:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 12:33 PM
Points: 21, 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

Post #872423
Posted Friday, February 26, 2010 3:47 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, November 2, 2014 1:00 PM
Points: 368, Visits: 544
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
SQLServerGeeks.com/blogs/sarab
www.Sarabpreet.com
Twitter: @Sarab_SQLGeek
Post #873304
Posted Friday, February 26, 2010 1:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 12:33 PM
Points: 21, 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.
Post #873689
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse