November 3, 2009 at 2:26 pm
I am not a DBA but am acting as one for now so I don't know 100% how to write certain things. The situation we have is follows:
We have a stored procedure that runs every night that does a full backup of all the databases on a particular server to a NAS location lets say, "\\server1\backups" This location only holds the most current backup and 1 day old backup. These backups are production backups. These backups are typical names (database_20091102120019.bak)
I would like to create a stored procedure that will restore TO THE DEVELOPMENT server, only a selection of databases from the "\\server1\backups" folder. The restore will not be taking the most recent backup but the one that is a day old. Since the backups are timestamped, I'm not 100% sure how to do this.
The restore does not have to have recovery and is something that I want to schedule to happen once a week. Anyone have any sql T-scripts that can help me? Thanks.
November 3, 2009 at 3:02 pm
If you select from the backup tables in msdb, you should be able to get the file name of the most recent full backup that's more than 1 day old for any given database. The backup date is in dbo.backupset, so is the database name.
From that, it should be relatively easy to have dynamic SQL build a restore command.
I don't have a script for you. Let me know if the above isn't enough to get your started.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 3, 2009 at 7:34 pm
Here's a rough draft starting point for you:
select bset.database_name, bset.Type, convert(varchar(25),backup_start_date,101),convert(varchar(25),getdate()-1,101) , media.physical_device_name
,bset.*
from msdb.dbo.backupset bset
join msdb.dbo.backupmediafamily as media on bset.media_set_id = media.media_set_id
where database_name = 'MyDatabase' and
type = 'D' and
convert(varchar(25),backup_start_date,101) = convert(varchar(25),getdate()-1,101)
order by backup_start_date desc
You can use the GUI to setup the restore, then use "Script" to generate a script you can use as a base for your stored procedure along with the path & file name you get from the above query.
November 4, 2009 at 7:19 am
Thank you I think this may be what I need.
homebrew01 (11/3/2009)
Here's a rough draft starting point for you:
select bset.database_name, bset.Type, convert(varchar(25),backup_start_date,101),convert(varchar(25),getdate()-1,101) , media.physical_device_name,bset.*
from msdb.dbo.backupset bset
join msdb.dbo.backupmediafamily as media on bset.media_set_id = media.media_set_id
where database_name = 'MyDatabase' and
type = 'D' and
convert(varchar(25),backup_start_date,101) = convert(varchar(25),getdate()-1,101)
order by backup_start_date desc
You can use the GUI to setup the restore, then use "Script" to generate a script you can use as a base for your stored procedure along with the path & file name you get from the above query.
November 5, 2009 at 10:48 am
USe PowerShell to read from the directory..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply