May 19, 2016 at 6:00 am
I'm writing up some T-SQL to create backup jobs and while I've done that before, what I haven't done before is check to see if a database has ever had a FULL backup before I start. As I started digging into the available backup tables in MSDB, I discovered BackupSet. But when I looked at the data, I found something extremely weird.
The server I'm working on is a Dev server that we're restoring production 2008 backups to in order to test our upgrades. I would expect not to see anything related to the 2008 servers in this table. But that's exactly what I'm seeing. A sample of the data is below (not all columns).
SELECT database_name, name, server_name, backup_finish_date, [type], database_version, machine_name, flags, backup_size, compressed_backup_size, [compatibility_level],
* FROM msdb..backupset bs
ORDER BY bs.database_name, bs.backup_finish_date;
database_namenameserver_namebackup_finish_datetype
MyDBNULLSQL2k8ProdDB2015-06-01 19:04:44.000D
MyDBMyDB-Full Database BackupSQL2k8QADB2015-06-02 08:47:35.000D
MyDBNULLSQL2k8ProdDB2016-01-11 20:50:41.000D
MyDBNULLSQL2k8ProdDB2016-01-17 19:21:24.000I
MyDBNULLSQL2k8ProdDB2016-03-28 19:12:50.000D
MyDBNULLSQL2k8ProdDB2016-03-30 19:20:25.000I
MyDBMyDB_FULL_20160509_0816SQL2012DevDB2016-05-09 07:29:51.000D
MyDBMyDB_20160510_0657.BAKSQL2012DevDB2016-05-10 06:57:26.000I
Why do the 2008 servers show up here? It's even showing a 2008 QA server name in my results.
May 19, 2016 at 10:56 am
Backupset table in Dev server will also contain information about backups that were taken on other servers and restored on Dev server.
May 19, 2016 at 11:31 am
shahm10 (5/19/2016)
Backupset table in Dev server will also contain information about backups that were taken on other servers and restored on Dev server.
Ah. That makes sense.
It also makes my job a little more difficult, but at least I know what to look for and what to ignore.
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply