March 23, 2012 at 10:17 am
Hi guys, LONG time lurker. This is my first post as I could not find any help with this.
I am trying to create a SQL Job that will grab the latest full backup from a server and restore it using Litespeed.
I have everything down except that backup file name changes with the date (ex. FullBackup_03232012.bak).
I know there is a way I can write my script to grab the latest backup of a particular database. Anybody got a clue? Sorry If there is already a similar thread on this, I could not find it.
Thanks!! 🙂
March 23, 2012 at 12:00 pm
Is xp_CmdShell enabled on your instance? If so, then you can issue a DOS DIR command on your backup directory and capture the results to a table, and then pick the latest from the table to build your restore command.
I prefer to use PowerShell for these types of (file system) tasks, and further, prefer to have xp_CmdShell disabled on my instance, but understand why many SQL professionals use it. You can get the file name and do the restore through PowerShell using SMO or with CmdLet Invoke-SqlCmd.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 23, 2012 at 12:53 pm
You can get this information from the backupset and backupmediafamily tables in msdb. You will also need to rename the files using WITH MOVE if you are restoring to the same instance.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 23, 2012 at 1:12 pm
Jeffrey Williams 3188 (3/23/2012)
You can get this information from the backupset and backupmediafamily tables in msdb. You will also need to rename the files using WITH MOVE if you are restoring to the same instance.
The backupset and backupmediafamily tables will store the paths relative to the server that initiated the backup. If you're restoring backups taken from one server onto another, know that you need to account for possible differences in the pathing.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 26, 2012 at 6:46 am
In addition to Jeffrey's comments you may also query the table msdb.dbo.backupfile, this will detail the logical and physical filenames for the databases that were part of a backup operation
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 27, 2012 at 4:51 pm
Just curious. You are doing backup(s) to a ceratin location(s)?
Can't you just get the latest backup from this location(s) and restore it?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply