October 11, 2009 at 6:49 am
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
October 11, 2009 at 7:27 am
maybe you should ask folks from redgate ?
October 12, 2009 at 7:18 am
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.
October 12, 2009 at 7:43 am
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.
October 12, 2009 at 8:05 am
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.
October 12, 2009 at 3:53 pm
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.
October 17, 2009 at 11:39 am
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
February 24, 2010 at 7:37 pm
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
February 26, 2010 at 3:47 am
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
February 26, 2010 at 1:42 pm
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.
February 26, 2010 at 1:43 pm
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.
February 26, 2010 at 1:46 pm
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.
September 20, 2016 at 8:31 am
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