May 15, 2012 at 9:27 am
My organization has roughly 30 SQL Servers, and I am trying to come up with a policy to test the backups our enterprise backup is taking. My initial thoughts on what we will need are as follows:
1) We'll need a test server
- I'm guessing this will need to be roughly a 500 GB box with 4-8 processors (that is comparable to our most intense prod boxes)
2) We'll need a SQL Server License
- Express will not work as some of our databases go well over 100 GB in size
- This cost is my greatest concern. Is there a cheap solution here since I will be the only one using this box to restore databases? (I will delete them when I'm done)
All I want to do is restore the databases to this server to verify that our backups are good. Does anyone have any comments on what the best process is to accomplish this task? Any suggestions on my ideas/questions above would be greatly appreciated.
Thanks,
Michael
May 15, 2012 at 9:52 am
for testing backup validity, i would think the Developer version (~$50 dollars on Amazon) is ideal; it has all the abilities of the Enterprise version, but is for testing and development only; no production deployment.
an additional option for saving space might be something like Red Gates SQL Virtual Restore, which allows you to open a backup as if it were an attached database.
It's really an amazing product and can do things like let you open a backup without restoring it to it's fully defined size;
you know, where the reserved space is 3x or 4xtimes the actual space used to account for future expansion, even when the data is a smaller slice of that space is actually used.
Lowell
May 15, 2012 at 9:55 am
Thanks Lowell! Greatly appreciated.
May 15, 2012 at 1:09 pm
mreynold (5/15/2012)
1) We'll need a test server- I'm guessing this will need to be roughly a 500 GB box with 4-8 processors (that is comparable to our most intense prod boxes)
My question is why does it need to be similar specification to a production box to test a backup file will restore? To save on cost, a lower spec machine with lots of space is more useful for this task than one that performs with less space.
Lowell (5/15/2012)
an additional option for saving space might be something like Red Gates SQL Virtual Restore, which allows you to open a backup as if it were an attached database.It's really an amazing product and can do things like let you open a backup without restoring it to it's fully defined size;
IMHO if its not restored on SQL Server, it hasnt been tested. If a restore fails, no manager in any company will accept the excuse "well it worked in Red Gate". You'd be shown the door.
If your company can't or wont pay for a fully specced server (or provide an alternative) they need to understand the implications
May 15, 2012 at 1:21 pm
MysteryJimbo (5/15/2012)
My question is why does it need to be similar specification to a production box to test a backup file will restore? To save on cost, a lower spec machine with lots of space is more useful for this task than one that performs with less space.
It doesn't. I've been testing other stuff recently that needs a comparable box, so that blead into my original post. I've talked to our network peopls since the original post, and I actually think a decent box is about to become free for this, so problem solved.
MysteryJimbo (5/15/2012)
IMHO if its not restored on SQL Server, it hasnt been tested. If a restore fails, no manager in any company will accept the excuse "well it worked in Red Gate". You'd be shown the door.If your company can't or wont pay for a fully specced server (or provide an alternative) they need to understand the implications
I agree. I am going with the SQL Server Developer suggestion. I want to test fully restoring the database in a SQL Server environment.
Thanks MysteryJimbo.
May 15, 2012 at 1:34 pm
MysteryJimbo (5/15/2012)
IMHO if its not restored on SQL Server, it hasnt been tested. If a restore fails, no manager in any company will accept the excuse "well it worked in Red Gate". You'd be shown the door.If your company can't or wont pay for a fully specced server (or provide an alternative) they need to understand the implications
I mostly agree; you have to be sure backups work. We are on the same page on that, I think, although i didn't emphasis it as well as you did.
With that said, even though i'm not a spokesman for redgate or anything along those lines, I have a lot of confidence in that product. I've never seen or heard of a situation where a backup that was open-able via Virtual Restore was not also restoreable via a standard restore. a combination of actual restores to spot check the virtuals would probably be best.
In my experience, if the backup is a valid backup, SQl Virtual restore can open it, and it can also be restored the native SQL way.
Now, I thought we are also talking about testing all the backups from 30 OTHER SQL servers in a central testing server; Id really expect disk space to be a serious issue in that situation, which was the main reason I mentioned it.
Lowell
May 16, 2012 at 5:30 pm
There are several good reasons to have a nonProduction SQL server on which you restore backups from Production.
1) If you do this on a regular basis, you'll be well-practiced in the restoring process should you suddenly need to restore a backup in production.
2) You can test reindex scripts and other maintanance routines, performance tuning, and other processes on nonProduction copies before running them in production.
3) You can use your test server for production should a production server have a major hardware failure.
One more note: I HOPE you are regularly copying SQL backup files off of the server. I once had a client who had scheduled nightly backup jobs, but the backup files were stored on the same RAID-5 as the active files. When the array completely failed, they had no current backups and had use a backup more than a month old.
May 17, 2012 at 7:03 am
dan-572483 (5/16/2012)
1) If you do this on a regular basis, you'll be well-practiced in the restoring process should you suddenly need to restore a backup in production.
2) You can test reindex scripts and other maintanance routines, performance tuning, and other processes on nonProduction copies before running them in production.
3) You can use your test server for production should a production server have a major hardware failure.
This process has actually taken legs since I posted this, and I think my director is trying to budget for a enterprise grade box with SQL Server Standard for me to have as a test box. The box will serve some other purposes, but for the most part it will be my playground do do all the things I need to do (test backups, evaluate maintenance plans on different servers, etc.)
dan-572483 (5/16/2012)
One more note: I HOPE you are regularly copying SQL backup files off of the server. I once had a client who had scheduled nightly backup jobs, but the backup files were stored on the same RAID-5 as the active files. When the array completely failed, they had no current backups and had use a backup more than a month old.
That's affirmative. We have 2 sets of backups: 1) the local backups, and 2) the enterprise backups that are copied to tape. My goal is to test both backups, but mainly the enterprise backups to make sure they are good in the event that 1 of the servers gets hosed.
Thanks Dan.
May 17, 2012 at 9:21 am
Lowell (5/15/2012)
MysteryJimbo (5/15/2012)
IMHO if its not restored on SQL Server, it hasnt been tested. If a restore fails, no manager in any company will accept the excuse "well it worked in Red Gate". You'd be shown the door.If your company can't or wont pay for a fully specced server (or provide an alternative) they need to understand the implications
I mostly agree; you have to be sure backups work. We are on the same page on that, I think, although i didn't emphasis it as well as you did.
With that said, even though i'm not a spokesman for redgate or anything along those lines, I have a lot of confidence in that product. I've never seen or heard of a situation where a backup that was open-able via Virtual Restore was not also restoreable via a standard restore. a combination of actual restores to spot check the virtuals would probably be best.
Being a spokesman for Red Gate ;), Virtual Restore does a full restore. It tricks the server into thinking it's reading from an MDF with a filter driver, but it does have to read the entire backup and do a restore, just like a normal restore. The only difference is the bits aren't copied.
If it works in Virtual Restore, it works in the normal restore process.
A fully spec'd server isn't needed. You just need a box that restores things and has disk space. While you might need a test box, and can do this, an old workstation with drives works.
With 30 servers, if you can restore all your dbs every day, good for you. If you can't, put them on a schedule. Try to restore 10 every third day (so 30 in 3 days). It's not perfect, but it's better than nothing. The copy time, restore time, etc. can be problematic.
Note that you want this scripted and automated. You should have it running and have another process that checks the restores and pings you if anything fails. Nothing worse than getting an email every day on status of positive restores that you start to ignore.
May 17, 2012 at 12:06 pm
One thing to note is any difference in the drives and directories that the restored live data files go to. I have several SQL Servers myself, so any time I restore to a test box, I have to do a RESTORE WITH MOVE to move the data files to another drive and directory structure. With multiple databases on up to 30 boxes, that could be interesting, and varied. Might want to build a database with this information and let the script read that info for its restore, if there is a way to look up the data for each database from each individual server.
Especially if this restore process is going to be automated.
Just a suggestion.
May 17, 2012 at 12:14 pm
BTW - when I do scripts, I use the REPLACE function with the old drive:\directory structure\ and the new drive:\directory structure\ and just replace them for each file. These could be two of the columns in the moveto database to map old and new drive/directory values for each of the files associated with each database.
Again, just a suggestion.
May 17, 2012 at 1:50 pm
Thanks vikingDBA.
May 17, 2012 at 1:59 pm
vikingDBA (5/17/2012)
One thing to note is any difference in the drives and directories that the restored live data files go to. I have several SQL Servers myself, so any time I restore to a test box, I have to do a RESTORE WITH MOVE to move the data files to another drive and directory structure. With multiple databases on up to 30 boxes, that could be interesting, and varied. Might want to build a database with this information and let the script read that info for its restore, if there is a way to look up the data for each database from each individual server.Especially if this restore process is going to be automated.
Just a suggestion.
I haven't tried it (yet, still working on convincing my boss that something like this is worth the investment) but you may be able to do a restore filelistonly and dump that into a table variable then build the restore statement from that. That should require less maintenance than having a permanent table with that data.
May 17, 2012 at 2:16 pm
Yes, that is what I do, but for 30 servers, many databases per server, how are you going to build the move statements without knowing where to put the new ones? The only way to do that by hardcoding is if you use the same one for all of them, the new drive and structure couldn't change. That would be the easiest, so you could just use one directory to hold them.
FYI, here is a script that I use to get the restore statements for the most recent backup of full and tran logs. Could be a starting point, maybe. Parts were originally done by Josep on this website (url given for his original file), and I modified it a good bit. Just set the database name and the length of time to look back for the latest backup (won't look past that, which of course, you could change).
/*Creates script to restore a database with all tran log files for most recent backups
Restore database with all necessary files
By Josep NOTE: Modified by VikingDBA to add the MOVE and STOPAT statements
This script creates the script to restore your database with the information existing in [msdb] database.
It helps you by finding the last FULL backup, the last DIFFERENTIAL backup and all the TRANSACTION LOG backups needed.
It's quite comfortable when you are doing so many differential or log backups.
I hope you enjoy it!!!
Script done by Josep Martínez based on the script done by [jtshyman] named "List SQL backups"
Of course, there's no warranty, etc ...
The variable @DBName should be set to the name of the database you want to query on.
It is not case sensitive unless your collation is.
The variable @Days should be set to how many days back in the records you want to list backups for. By default set to 14 (old enought I think)
http://www.sqlservercentral.com/scripts/Restore/61810/
*/
/* ================================================================================================================== */
/* Create script to restore database from the latest backups (full and tran log) */
/* IMPORTANT: Do not forget to do a trailing log backup first!!!!!!!!! */
/* ================================================================================================================== */
DECLARE @DBName sysname
DECLARE @Days int
DECLARE @currdbname varchar(50)
DECLARE @logname varchar(50)
DECLARE @physname varchar(200)
SET @DBName='MyDatabase'-- change this value - database involved
SET @Days=60-- change this value (if needed) - Number of days back to check, but will pull the latest one
-- Important because we're going to 'print' the sql code for the restore
SET NOCOUNT ON
CREATE TABLE #BackupsHistory
(
id INT IDENTITY(1,1),
backup_start_date DATETIME,
backup_type CHAR(1),
physical_device_name VARCHAR(2000)
)
INSERT INTO #BackupsHistory (backup_start_date,backup_type, physical_device_name)
SELECT S.backup_start_date,
S.type,
M.physical_device_name
FROM msdb..backupset S
JOIN msdb..backupmediafamily M ON M.media_set_id=S.media_set_id
WHERE S.database_name = @DBName
AND DATEDIFF(DAY,S.backup_start_date,GETDATE()) < @Days
ORDER by backup_start_date
DECLARE @lastFullBackup INT, @lastFullBackupPath VARCHAR(2000), @lastDifferentialBackup INT, @lastDifferentialBackupPath VARCHAR(2000)
-- We get the last Full backup done. That is where we are going to start the restore process
SET @lastFullBackup = (SELECT TOP 1 id FROM #BackupsHistory WHERE backup_type='D' ORDER BY backup_start_date DESC)
SET @lastFullBackupPath = (SELECT physical_device_name FROM #BackupsHistory WHERE id=@lastFullBackup)
if @lastFullBackupPath IS NULL
SET @lastFullBackupPath = ''
PRINT '/* ============================================================================================== */'
PRINT '/* Script to restore database ' + @DBName + ' and all tran log files */'
PRINT ''
PRINT '/* File To Restore: ' + @lastFullBackupPath + ' */'
PRINT '/* ============================================================================================== */'
PRINT ''
PRINT ''
PRINT '--RESTORE FILELISTONLY FROM DISK=''' + @lastFullBackupPath + ''''
PRINT ''
PRINT '-- HEADERONLY gives version of SQL needed for restore of master db'
PRINT '--RESTORE HEADERONLY FROM DISK=''' + @lastFullBackupPath + ''''
PRINT ''
PRINT '--RESTORE VERIFYONLY FROM DISK=''' + @lastFullBackupPath + ''''
PRINT '--WITH LOADHISTORY'
PRINT ''
PRINT ''
-- Restoring the Full backup
PRINT 'RESTORE DATABASE ' + @DBName
PRINT 'FROM DISK=''' + @lastFullBackupPath + ''''
PRINT 'WITH'
DECLARE backupFiles CURSOR FOR
SELECT CONVERT(varchar(50),f.name) AS LogicalName, CONVERT(varchar(200),f.physical_name) AS PhysicalName
FROM sys.master_files f
JOIN sys.databases d ON f.database_id=d.database_id
WHERE d.database_id = db_id(@DBName)--for specific database
ORDER BY f.name
OPEN backupFiles
-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @logname, @physname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'MOVE N''' + @logname + ''' TO N''' + @physname + ''','
FETCH NEXT FROM backupFiles INTO @logname, @physname
END
CLOSE backupFiles
DEALLOCATE backupFiles
-- IF it's there's no backup (differential or log) after it, we set to recovery
IF (@lastFullBackup = (SELECT MAX(id) FROM #BackupsHistory))
PRINT 'RECOVERY, REPLACE'
ELSE PRINT 'NORECOVERY, REPLACE'
PRINT 'GO'
PRINT ''
-- We get the last Differential backup (it must be done after the last Full backup)
SET @lastDifferentialBackup = (SELECT TOP 1 id FROM #BackupsHistory WHERE backup_type='I' AND id>@lastFullBackup ORDER BY backup_start_date DESC)
SET @lastDifferentialBackupPath = (SELECT physical_device_name FROM #BackupsHistory WHERE id=@lastDifferentialBackup)
-- IF there's a differential backup done after the full backup we script it
IF (@lastDifferentialBackup IS NOT NULL)
BEGIN
-- Restoring the Full backup
PRINT 'RESTORE DATABASE ' + @DBName
PRINT 'FROM DISK=''' + @lastDifferentialBackupPath + ''''
-- IF it's there's no backup (differential or log) after it, we set to recovery
IF (@lastDifferentialBackup = (SELECT MAX(id) FROM #BackupsHistory))
PRINT 'WITH RECOVERY'
ELSE PRINT 'WITH NORECOVERY'
PRINT 'GO'
PRINT ''
END
-- For TRANSACTION LOGs
DECLARE @i INT, @logBackupPath VARCHAR(2000)
IF (@lastDifferentialBackup IS NULL)
SET @i = @lastFullBackup + 1
ELSE SET @i = @lastDifferentialBackup + 1
-- Here whe are scripting the restores for the necessary logs
WHILE (@i <= (SELECT MAX(id) FROM #BackupsHistory))
BEGIN
SET @logBackupPath = (SELECT physical_device_name FROM #BackupsHistory WHERE id=@i)
PRINT 'RESTORE LOG ' + @DBName
PRINT 'FROM DISK=''' + @logBackupPath + ''''
-- IF it's the last transaction log, we'll say it to recover
IF (@i = (SELECT MAX(id) FROM #BackupsHistory))
PRINT 'WITH RECOVERY'
ELSE PRINT 'WITH NORECOVERY'
PRINT 'GO'
PRINT ''
SET @i = @i + 1
END
PRINT '--WITH RECOVERY, STOPAT = ''Apr 20, 2008 12:00 AM'''
DROP TABLE #BackupsHistory
/* ======================================================================================================= */
DECLARE @loginname varchar(100)
DECLARE @dbnametouse sysname
PRINT ' '
PRINT ' '
PRINT '/* ======================================================================================================= */'
PRINT '-- Here are the logins and their default database settings'
PRINT ' '
DECLARE myCursorVariable CURSOR FOR
SELECT name, default_database_name as DefaultDB
FROM master.sys.server_Principals
WHERE type in ('G','S','U') AND default_database_name = @DBName
ORDER BY default_database_name
OPEN myCursorVariable
-- Loop through all the files for the database
FETCH NEXT FROM myCursorVariable INTO @loginname, @dbnametouse
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'ALTER LOGIN [' + @loginname + '] WITH DEFAULT_DATABASE = ' + @dbnametouse
FETCH NEXT FROM myCursorVariable INTO @loginname, @dbnametouse
END
CLOSE myCursorVariable
DEALLOCATE myCursorVariable
May 18, 2012 at 7:00 am
The method I would use to do this would be rather different just because of how we work. I would drive creating the restore statement off the backup. That way you can run it from whatever machine you're doing the restore on. Do something to get the backup onto the restore machine in a known directory (for us it would be a pull from TSM to stay entirely off prod). Then either have powershell call a proc with the filename as a parameter or use xp_cmdshell to get the filename and run it through something like what I have below.
Regardless of the method to get there, the UPDATE below will work for switching to the new restore path given the original file path. I'm hardcoding a lot that would need to be done dynamically but that will be highly dependent on the whole process.
--Create a table to hold the output from the "restore filelistonly" command
DECLARE @filelist TABLE (
logicalname VARCHAR(MAX),
physicalname VARCHAR(MAX),
TYPE VARCHAR(MAX),
filegroupname VARCHAR(MAX),
SIZE VARCHAR(MAX),
maxsize VARCHAR(MAX),
fileid VARCHAR(MAX),
createlsn VARCHAR(MAX),
droplsn VARCHAR(MAX),
uniqueid VARCHAR(MAX),
readonlylsn VARCHAR(MAX),
readwritelsn VARCHAR(MAX),
backupsizeinbytes VARCHAR(MAX),
sourceblocksize VARCHAR(MAX),
filegroupid VARCHAR(MAX),
loggroupguid VARCHAR(MAX),
differentialbaselsn VARCHAR(MAX),
differentialbaseguid VARCHAR(MAX),
isreadonly VARCHAR(MAX),
ispresent VARCHAR(MAX),
tdethumbprint VARCHAR(MAX)
);
--Set the command to get the list of files, would normally be dynamically created
DECLARE @sql VARCHAR(MAX) = 'restore filelistonly
from disk = ''D:\Backup\RestoreExample_FULL_20120517_200020.bak'''
--Fill the table, needs to be done with the exec
INSERT INTO @filelist
EXEC (@sql)
--Reset @sql to the actual restore command
SET @sql = 'restore database RestoreExample
from disk = ''D:\Backup\RestoreExample_FULL_20120517_200020.bak''
with
'
--Update the filename to the restore location and combine it with the base
UPDATE @filelist SET physicalname = 'F:\RestoreLocation' + RIGHT(physicalname,CHARINDEX('\',REVERSE(physicalname)))
(SELECT ', move ''' + logicalname + ''' to ''' + physicalname + '''' FROM @filelist FOR XML PATH(''))
, 1, 1, '')
select @sql
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply