Verify your SQL Server database backups
-
This was removed by the editor as SPAM
-
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
-
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
-
I had a teacher that spoke: the backup is not the problem. The problem is the restore. Always test your backup
-
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.
-
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.
-
does the sql service account (or sql agent if you are doing it via a job) have permissions to that folder?
MVDBA
-
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
-
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
-
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.
-
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.
-
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.
-
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 curs1this 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
-
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.
-
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
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply