August 20, 2018 at 10:16 am
Awesome stuff everyone. I appreciate everyone's thoughts and help on this. I think I will be able to make a very nice proposal for our backup plan and ensure we don't lose anything. I've learned a lot through this thread.
My boss seems more interested in doing a daily full back up with hourly differentials during the business hours. Says he's OK not being able to restore to any point in time that would be possible with transaction log. I'll have to put on my campaign hat and see if I can alter his view. He seems to be a little concerned that a restore with logs would be a little more challenging or time consuming. For example - say 2:30 pm is when we need to do a restore
With differential - full backup from 12:00 am and then the differential at 2 pm. We would lose anything from 2 to 2:30 pm
With transaction Log - full back from 12:00 am then have to restore each hourly log from 7 am to 2 pm. Would we not still lose data from 2 to 2:30 seeing as the next transaction log wasn't backing up until 3? In this situation we have the power to restore to any minute between 7 am and 2 pm (which is more powerful than only being able to choose on the hour marks with the differentials). But the concern is that this might take longer to restore and require more work or scripting that would be more difficult for us to figure out. Not having done any type of restore, I'm reading up on how easily SSMS allows you to do a restore to a point in time with transaction logs. Long story short, I don't have an answer to his concern yet, but I hear all of you loud and clear that the transaction log is the better way to go given the situation I've described.
One step at a time!
August 20, 2018 at 12:26 pm
Red14 - Monday, August 20, 2018 10:16 AMAwesome stuff everyone. I appreciate everyone's thoughts and help on this. I think I will be able to make a very nice proposal for our backup plan and ensure we don't lose anything. I've learned a lot through this thread.My boss seems more interested in doing a daily full back up with hourly differentials during the business hours. Says he's OK not being able to restore to any point in time that would be possible with transaction log. I'll have to put on my campaign hat and see if I can alter his view. He seems to be a little concerned that a restore with logs would be a little more challenging or time consuming. For example - say 2:30 pm is when we need to do a restore
With differential - full backup from 12:00 am and then the differential at 2 pm. We would lose anything from 2 to 2:30 pm
With transaction Log - full back from 12:00 am then have to restore each hourly log from 7 am to 2 pm. Would we not still lose data from 2 to 2:30 seeing as the next transaction log wasn't backing up until 3? In this situation we have the power to restore to any minute between 7 am and 2 pm (which is more powerful than only being able to choose on the hour marks with the differentials). But the concern is that this might take longer to restore and require more work or scripting that would be more difficult for us to figure out. Not having done any type of restore, I'm reading up on how easily SSMS allows you to do a restore to a point in time with transaction logs. Long story short, I don't have an answer to his concern yet, but I hear all of you loud and clear that the transaction log is the better way to go given the situation I've described.
One step at a time!
You're close on what's involved with transaction log backups, but not quite all the way there. If you did do a differential at sometime after 12am, say noon, you could restore the 12am full, the noon differential, then the logs from 1pm to 2pm. Which brings us to the last point. Depending on how the database failed, you might still be able to take a "tail-log" backup, thus being able to restore to 2:30pm (or darn close to it,) OR if it was someone doing something boneheaded (delete without a where,) take a log backup manually and add it to the restore process.
Also to keep in mind, log backups tend to be quite fast, so you could increase your schedule for log backups from say every hour to every 10 minutes, or every 5 minutes, or (potentially) even less. Yes, this means a LOT more backup files, and your restore will likely take longer, but it also reduces your potential data loss.
But regardless of all of that, the paranoiac in me would also recommend getting whatever backup schedule your boss calls for *IN WRITING.*
August 20, 2018 at 1:02 pm
Also, if all you are doing is full and differential backups, you should not be using bulk logged or full recover model for the databases.
August 20, 2018 at 11:12 pm
Actually, if you're not doing log backups, then you MUST not be using Full or Bull-logged.
SQL Server will let you do it, but in this setup, your transaction logs will simply grow and grow and never reuse any file space. They cannot overwrite anything that hasn't been backed up, so they'll permanently hold on to every piece of logged info about everything that ever happened, waiting for a log backup that never comes around to collect it. And if your server and databases are left like that, then it's basically just a matter of time before somebody will manage to generate enough log volumes for a disk to fill up and then things to simply stop working because there's no more available space in which to log anything.
If you're going to do the log backups, though, it can be made quicker and easier if you come prepared and know what you're doing. Each log backup only covers the time period since the previous log backup, so the more often you run these, the smaller, lighter and faster they will be. Yes, this will generate many files if you're dumping each backup to its own file, but the "many files" issue can be mitigated in a restore situation by placing all the relevant files in a folder and then running a script to actually generate the restore script for you. For example something like this:
-- Database restore script.
-- Source: https://www.mssqltips.com/sqlservertip/1584/auto-generate-sql-server-restore-script-from-backup-files-in-a-directory/
-- The script below will read through our backup directory and create the restore script for us.
-- The only two parameters that would need to change are the @dbName and the @backupPath.
USE Master;
GO
SET NOCOUNT ON
-- 1 - Variable declaration
DECLARE @dbName sysname
DECLARE @backupPath NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @fileList TABLE (backupFile NVARCHAR(255))
DECLARE @lastFullBackup NVARCHAR(500)
DECLARE @lastDiffBackup NVARCHAR(500)
DECLARE @backupFile NVARCHAR(500)
-- 2 - Initialize variables
SET @dbName = 'Customer'
SET @backupPath = 'D:\SQLBackups\'
-- 3 - get list of files
SET @cmd = 'DIR /b "' + @backupPath + '"'
INSERT INTO @fileList(backupFile)
EXEC master.sys.xp_cmdshell @cmd
-- 4 - Find latest full backup
SELECT @lastFullBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.BAK'
AND backupFile LIKE @dbName + '%'
SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = '''
+ @backupPath + @lastFullBackup + ''' WITH NORECOVERY, REPLACE'
PRINT @cmd
-- 4 - Find latest diff backup
SELECT @lastDiffBackup = MAX(backupFile)
FROM @fileList
WHERE backupFile LIKE '%.DIF'
AND backupFile LIKE @dbName + '%'
AND backupFile > @lastFullBackup
-- check to make sure there is a diff backup
IF @lastDiffBackup IS NOT NULL
BEGIN
SET @cmd = 'RESTORE DATABASE [' + @dbName + '] FROM DISK = '''
+ @backupPath + @lastDiffBackup + ''' WITH NORECOVERY'
PRINT @cmd
SET @lastFullBackup = @lastDiffBackup
END
-- 5 - check for log backups
DECLARE backupFiles CURSOR FOR
SELECT backupFile
FROM @fileList
WHERE backupFile LIKE '%.TRN'
AND backupFile LIKE @dbName + '%'
AND backupFile > @lastFullBackup
OPEN backupFiles
-- Loop through all the files for the database
FETCH NEXT FROM backupFiles INTO @backupFile
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'RESTORE LOG [' + @dbName + '] FROM DISK = '''
+ @backupPath + @backupFile + ''' WITH NORECOVERY'
PRINT @cmd
FETCH NEXT FROM backupFiles INTO @backupFile
END
CLOSE backupFiles
DEALLOCATE backupFiles
-- 6 - put database in a useable state
SET @cmd = 'RESTORE DATABASE [' + @dbName + '] WITH RECOVERY'
PRINT @cmd
Also, if you're restoring to a point in time, you will need to add a STOPAT clause in your restore script. 😉
But again, the key to success is to come prepared and know what you're doing, meaning you should spend some time playing around with it while there is no fire under your seat. Now is the time to start developing, testing and verifying procedures that you know will work for you and your business, then practice those procedures until you basically know exactly what to do before you even sit down to start looking at it. And then you still practice every once in a while after that, and periodically review your procedures to make sure they're still sound in light of everything that's changed and everything you've learned since you first designed them. If you manage to stay on top of this, there's a pretty good chance that a full-blown disaster won't be such a bad thing after all. 😉
Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)
August 21, 2018 at 12:28 am
One thing to bear in mind if your boss isn't a SQL guy, is that the terminology around SQL Backups is a little different than the usual backup terms and can lead to people thinking things aren't as important or useful as they actually are (I've had this conversation with my IT guy more times than I care to remember!). In the normal world of server backups, everything is a point-in-time snapshot that is either a full backup or a differential. Logs are things that contain some level of history and thus "backing up logs" is something you only do if you're concerned about retaining an audit trail of what is logged.
In SQL world, Log backups are really more closely related to differentials, in that they log what has changed in the database since the last backup. They are subtly different from an SQL Differential backup in that they contain the sequence of changes between points in time, whereas a differential is just a point in time snapshot. This makes log backups far more powerful than differentials as they let you recover things from backups taken "after the fact".
Imagine, for example, that you take a Full Backup at 2pm, with Differentials scheduled every 30 mins. If someone rings up at 2:29 having just deleted all the data from the database, you've no choice but to restore the 2pm full backup and lose 29 mins of work. If you were taking log backups every 30 mins instead of differentials, you could just use the full backup and 2:30 log file to restore the database to just before 2:29, losing the absolute bare minimum. The main reason for using differentials at all is that it can let you "skip over" restoring a whole sequence of log backups since you only need the full backup and latest diff to get to a certain point in time (from which you can continue to fine tune the restore with log backups).
August 21, 2018 at 5:30 am
andycadley - Tuesday, August 21, 2018 12:28 AMIn the normal world of server backups, everything is a point-in-time snapshot that is either a full backup or a differential. Logs are things that contain some level of history and thus "backing up logs" is something you only do if you're concerned about retaining an audit trail of what is logged.
This really bears repeating because I find it the biggest hurdle to understanding among non-technical managers.
August 21, 2018 at 9:22 am
This entire discussion has been about a backup strategy.
It really should be a discussion on a RESTORE strategy.
Have you tested your backup strategy by actually restoring it? The success (or failure) of that exercise may provide your boss with real facts about the methodology.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
August 21, 2018 at 9:31 am
Michael L John - Tuesday, August 21, 2018 9:22 AMThis entire discussion has been about a backup strategy.It really should be a discussion on a RESTORE strategy.
Have you tested your backup strategy by actually restoring it? The success (or failure) of that exercise may provide your boss with real facts about the methodology.
You could say "Backups are worthless, Restores are priceless."
Basically, if you don't test your backups by doing restores to a test server, you may find yourself without any viable backups to restore.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply