Verify your SQL Server database backups

  • This was removed by the editor as SPAM

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21277

    quite interesting - I've been using a low power server to do my DR testing - my databases get restored regularly but I've avoided checkdb purely on the amount of time it takes to restore a few hundred databases - I use Redgate sql backup and it let it do the checks for me

    checksum (tests existing pages checksums and generates a backup checksum)

    Verify (checks the file is readable)

    I then copy the most recent backup of each database using a script to my little server and restore them each week

    am I missing a trick?

    MVDBA

  • Steve Rezhener

    SSC Veteran

    Points: 229

    The first line of defense is SQL Server’s CHECKSUM verification, but to be certain that a database is corruption-free, you need to run regular DBCC

    https://www.red-gate.com/hub/product-learning/sql-clone/sql-clone-quick-tip-offloading-dbcc-checks

  • Daniel Olivieri

    Newbie

    Points: 1

    I had a teacher that spoke: the backup is not the problem. The problem is the restore. Always test your backup

  • uk00121

    Mr or Mrs. 500

    Points: 573

    We've taken similar approach but one step further.  Convinced the enterprise to give us a dedicated server then via powershell reach out to each of our SQL instances, get list of databases, restore latest backup, and check each one, then move on to the next, on a 24/7 loop.  Not only do we know the databases can be restored, but we move the resources of the CHECKDB to a non-production server.

    I love your approach, and may adjust my scripts accordingly.

  • jimmy.white 20227

    SSC Journeyman

    Points: 84

    Tried this on both 2012 and 2019 get the same error.

    -- 1. GET LATEST BACKUP FILE FOR A DATABASE--

    {3E74E0E1-1273-426E-B8E4-20E14E1C6992}2

    -- 2. CREATE DATABASE NAME TO RESTORE --

    Accounting_November212019

    -- 3. CHECK FREE DISKSPACE TO RESTORE THE DATABASE --

    0

    108

    -- 4. RESTORE DB--

    RESTORE DATABASE [Accounting_November212019] FROM DISK = N'{3E74E0E1-1273-426E-B8E4-20E14E1C6992}2' WITH FILE = 1, MOVE N'Accounting' TO N'D:\Program Files\Microsoft SQL Server\MSSQL11.DEV12\MSSQL\DATA\Accounting_November212019_data.mdf', MOVE N'Accounting_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL11.DEV12\MSSQL\DATA\Accounting_November212019_log.ldf', NOUNLOAD, REPLACE, STATS = 10

    Msg 3201, Level 16, State 2, Line 1

    Cannot open backup device 'D:\Program Files\Microsoft SQL Server\MSSQL11.DEV12\MSSQL\Backup\{3E74E0E1-1273-426E-B8E4-20E14E1C6992}2'. Operating system error 2(The system cannot find the file specified.).

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21277

    does the sql service account (or sql agent if you are doing it via a job) have permissions to that folder?

    MVDBA

  • jimmy.white 20227

    SSC Journeyman

    Points: 84

    Absolutely, I'm running it as myself, I'm the DBA and basically have sa permissions.

    Did you see the backup file name that it is coming up with?

    {3E74E0E1-1273-426E-B8E4-20E14E1C6992}2

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21277

    I did think that was unusual, and it occurred to me that it doesn't fit well with enumerating files from NTFS - perhaps just save your backup as accounting.bak 🙂

    MVDBA

  • jimmy.white 20227

    SSC Journeyman

    Points: 84

    Running SQL Server 2012 and 2019 on Windows Server 2016.

    Backup file name is ACCOUNTING_backup_2019_11_20_180003_1654318.bak

    Not sure how to pull that name from sys tables.

  • Steve Rezhener

    SSC Veteran

    Points: 229

    That script should not be tied to a specific SQL Server version. That being said, I tested only against a SQL Server 2016 instance and if that fails for you, I have a problem. Stay tuned for an update.

  • Steve Rezhener

    SSC Veteran

    Points: 229

    OK. What you need is a database name, not a database backup file name. Your database name is probably ACCOUNTING. I'm updating the code to check for a database backup before trying to restore it.

  • MVDBA (Mike Vessey)

    SSC-Insane

    Points: 21277

    I have a script

    ALTER PROC [dbo].[p_copylatestbackup] @user VARCHAR(100)=NULL, @expireson DATE=NULL, @database VARCHAR(100)=NULL
    AS
    SET NOCOUNT ON

    DECLARE @db VARCHAR(100)
    DECLARE @filename VARCHAR(400)
    DECLARE @str VARCHAR(1000)
    DECLARE curs1 CURSOR FOR

    SELECT x.database_name,x.physical_device_name FROM (
    SELECT ROW_NUMBER() OVER (PARTITION by database_name ORDER BY msdb.dbo.backupset.backup_finish_date desc) AS rnk,
    CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
    msdb.dbo.backupset.database_name,
    msdb.dbo.backupset.backup_finish_date AS last_db_backup_date , physical_device_name
    FROM msdb.dbo.backupmediafamily
    INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
    WHERE msdb..backupset.type = 'D'
    ) AS x
    WHERE rnk=1 AND x.database_name NOT IN ('master','model','msdb') AND (@database IS NULL OR x.database_name=@database)
    ORDER BY
    x.database_name

    OPEN curs1
    FETCH NEXT FROM curs1 INTO @db, @filename
    WHILE @@FETCH_STATUS=0
    BEGIN
    SET @str='copy /Y /B "'+@filename+'" \\CZC915CYSD\RestoreTest\'+@db+ISNULL(@user,'')+CASE WHEN @expireson IS NULL THEN '' ELSE CONVERT (VARCHAR(12),GETDATE(),105) end+'.sqb'
    PRINT @str
    EXEC xp_cmdshell @str
    FETCH NEXT FROM curs1 INTO @db, @filename
    END
    CLOSE curs1
    DEALLOCATE curs1

    this takes every database from my server and copies the latest backup to my DR server - i'm sure you can tear apart my script to get what you need

    MVDBA

  • Steve Rezhener

    SSC Veteran

    Points: 229

    This might work as well. That being said, I updated my initial script and now it takes a server as a parameter, so backup and restore servers could be 2 different servers.

  • eliassal

    Ten Centuries

    Points: 1295

    Steve quite interesting and more than sure will make use of it. However, after copying and pasting the T-sql in ssms, I ran ApexSQL Refactor, Format SQL, to make it more readable, it refuses and indicates

    Line: 56, Column:214, Position: 0, Incorrect syntax near 'as'

    Any idea? Thanks

    Line55

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply