September 5, 2012 at 8:19 am
With the help from this forum, I've re-engineered out SQL backup strategy, and now it does a full backup a day, with differentials throughout the rest of the day (it makes the most sense for our environment).
Now I'd like to change it a bit. After the full backup is complete I'd like to copy the backup file off to a network share, where our secondary SQL Server server can pick it up and we can load that into that secondary server. I'm pretty sure that there's an agent, or task, within SQL Jobs that I could use for performing the copy on the production server, and then copy it down from the test server. But how do I determine the newly created backup file's name? It's kind of a long thing, so I don't know how to go about it.
Kindest Regards, Rod Connect with me on LinkedIn.
September 5, 2012 at 8:27 am
Probably the easiest way is to look at the physical_device_name column of the msdb.dbo.backupmediafamily system table. This table contains an entry for each backup, and with some joins to other tables in that database you can get a wealth of information about backups you've performed.
_______________________________________________________________________________________________
Bob Pusateri
Microsoft Certified Master: SQL Server 2008
Blog: The Outer Join[/url]
Twitter: @SQLBob
September 5, 2012 at 12:38 pm
Dang, that is sweet. I guess I'll have to develop some logic to filter on the largest media_set_id for the backups I'm interested in. Before I go too far down this path I'd like to know if there's a system SP that should be favored, instead of directly accessing the system table backupmediafamily? I know that sometimes Microsoft recommends using such-and-such a system SP, or favors of command over something else, just in case they change the system tables' names.
Kindest Regards, Rod Connect with me on LinkedIn.
September 5, 2012 at 12:51 pm
I'm not aware of any views or SPs that expose this information - I've only ever seen examples using the tables.
I of course can't guarantee anything, but given that these tables are well documented in BOL, I'd be inclined to believe that MS won't make breaking changes to them without giving plenty of advance warning first...
http://msdn.microsoft.com/en-us/library/ms188062.aspx
_______________________________________________________________________________________________
Bob Pusateri
Microsoft Certified Master: SQL Server 2008
Blog: The Outer Join[/url]
Twitter: @SQLBob
September 5, 2012 at 1:35 pm
SQL_Bob (9/5/2012)
I'm not aware of any views or SPs that expose this information - I've only ever seen examples using the tables.I of course can't guarantee anything, but given that these tables are well documented in BOL, I'd be inclined to believe that MS won't make breaking changes to them without giving plenty of advance warning first...
Making it easier for those that may follow:
September 5, 2012 at 8:08 pm
Rod at work (9/5/2012)
. But how do I determine the newly created backup file's name? It's kind of a long thing, so I don't know how to go about it.
This might be of some help
SELECT name AS 'Backup Name',database_name,recovery_model,backup_start_date FROM msdb..backupset
Sample return from above:
Backup Name database_namerecovery_modelbackup_start_date
Scores-08-16-2012.bakScores SIMPLE 2012-08-16 11:41:10.000
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply