Verifying Data integrity - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Verifying Data integrity

Author

Topic

jmanini
Starting Member

1 Post

Posted - 2009-05-08 : 13:10:00

I'm kind of new to SQL and I set up Maintenance jobs for all of my instances. For one Instance i need a way to verify the backups at a different time rather then during the Back Up Database maintenance plan. I can't seem to find a T-SQL script that allows me to do this. Yes i understand that I can do a RESTORE VERIFYONLY manually poiting to a backup but my backup files get appended with the date automatically which is good but trying to point a script at files that change everday is what I need. I need a script that I can point to a particular folder and that would verify all backup files that are in it. Thank You

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-05-08 : 14:28:37

The only way to truly verify a backup is to perform a backup on another server. When RESTORE VERIFYONLY reports it's okay, it does not mean that the file is restoreable.

Here's some code that I use to grab the newest full backup from a specified directory, you'd just need to modify it to your requirements:


DECLARE 
	@remotePath varchar(100), @localPath varchar(100), 
	@whichFile varchar(1000), @cmd nvarchar(4000), @filename sysname

SELECT @remotePath = '\\Server1\G$\Backup\dbName\', @localPath = 'F:\Backup\dbName\'

CREATE TABLE #DeleteOldFiles(DirInfo VARCHAR(7000))

SELECT @cmd = 'dir "' + @remotePath + '*.BAK" /OD'

INSERT INTO #DeleteOldFiles
EXEC master..xp_cmdshell @cmd

SELECT @whichFile = SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo)) 
FROM #DeleteOldFiles
WHERE SUBSTRING(DirInfo, 1, 10) = (SELECT MAX(SUBSTRING(DirInfo, 1, 10)) FROM #DeleteOldFiles)

SET @cmd = 'del ' + @localPath + '*.* /Q /F'
--PRINT @cmd
EXEC master..xp_cmdshell @cmd, NO_OUTPUT

SET @cmd = 'xcopy ' + @remotePath + @whichFile + ' ' + @localPath + ' /Y'
--PRINT @cmd
EXEC master..xp_cmdshell @cmd, NO_OUTPUT

DROP TABLE #DeleteOldFiles

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources