September 4, 2014 at 8:43 am
I have been tasked with developing IT continuity docs for our department and have been working with our DBA to create documentation for a scheduled job that runs every night. Part of the process is to make a backup of the production database and then, according to my notes, restore to another database on the same server to verify the backup before commencing the re-indexing job. When I got the docs, there was nothing in them about doing the restore. The answer I got from him was that SQL Server 2012 handled the validation of the backup and it was no longer necessary to do that step (we migrated recently from SQL Server 2008). This just doesn't sound right to me, but since I work mainly with SSRS I don't really know and am dependent upon him for those answers. I would appreciate it if someone could help set me straight. Thank you for your time.
September 4, 2014 at 10:51 am
I dont believe SQL server automatically checks the validity of backups. There would have to be additional steps performed.
You can partially validate the backup without restoring by using restore verifyonly(http://msdn.microsoft.com/en-ca/library/ms188902.aspx). This will not verify data structure. To have restore verifyonly do a more extensive check, you need to backup the database with the CHECKSUM switch(myth 24p http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2430-twenty-six-restore-myths/).
September 4, 2014 at 11:43 am
Thank you, Bob. I appreciate you taking the time to answer.
September 4, 2014 at 12:36 pm
I wrote a short article on backup verification[/url], and, you're right, the only way to be sure your backup is good is to restore it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 5, 2014 at 12:01 am
You may use this code for verify the SQL BAK files
SELECT
DISTINCT
a.Name AS DatabaseName ,
CONVERT(SYSNAME, DATABASEPROPERTYEX(a.name, 'Recovery')) RecoveryModel ,
COALESCE(( SELECT CONVERT(VARCHAR(12), MAX(backup_finish_date), 101)
FROM msdb.dbo.backupset
WHERE database_name = a.name
AND type = 'd'
AND is_copy_only = '0'
), 'No Full') AS 'Full' ,
COALESCE(( SELECT CONVERT(VARCHAR(12), MAX(backup_finish_date), 101)
FROM msdb.dbo.backupset
WHERE database_name = a.name
AND type = 'i'
AND is_copy_only = '0'
), 'No Diff') AS 'Diff' ,
COALESCE(( SELECT CONVERT(VARCHAR(20), MAX(backup_finish_date), 120)
FROM msdb.dbo.backupset
WHERE database_name = a.name
AND type = 'l'
), 'No Log') AS 'LastLog' ,
COALESCE(( SELECT CONVERT(VARCHAR(20), backup_finish_date, 120)
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY backup_finish_date DESC ) AS 'rownum' ,
backup_finish_date
FROM msdb.dbo.backupset
WHERE database_name = a.name
AND type = 'l'
) withrownum
WHERE rownum = 2
), 'No Log') AS 'LastLog2'
FROM sys.databases a
LEFT OUTER JOIN msdb.dbo.backupset b ON b.database_name = a.name
WHERE a.name <> 'tempdb'
AND a.state_desc = 'online'
GROUP BY a.Name ,
a.compatibility_level
ORDER BY a.name
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply