Backups Not Performed Recently
Backups Not Performed Recently
Before we start working on anything, we need to know all our user databases are getting backed up. We check to see if there’s been a full backup in the last 7 days.
Often, someone set up maintenance plans to back up specific databases by checking their names. They checked all of the database names that were present at the time, but then down the road, other people added more databases – without knowing they needed to check more boxes to get ’em backed up.
Or maybe your users add databases all the time, but your backup tool doesn’t catch them until a weekend full backup. Good news, we can fix that too – keep reading.
In rare cases, like for multi-terabyte databases, sometimes there’s a full backup happening just once per month, and differential backups are done more frequently.
Are Your Backups Less Perfect Than You Thought? Check Here.
To see how much data you could lose per database over the last couple of weeks, run this query:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE TABLE #backupset (backup_set_id INT, database_name NVARCHAR(128), backup_finish_date DATETIME, type CHAR(1), next_backup_finish_date DATETIME); INSERT INTO #backupset (backup_set_id, database_name, backup_finish_date, type) SELECT backup_set_id, database_name, backup_finish_date, type FROM msdb.dbo.backupset WITH (NOLOCK) WHERE backup_finish_date >= DATEADD(dd, -14, GETDATE()) AND database_name NOT IN ('master', 'model', 'msdb'); CREATE CLUSTERED INDEX CL_database_name_backup_finish_date ON #backupset (database_name, backup_finish_date); UPDATE #backupset SET next_backup_finish_date = (SELECT TOP 1 backup_finish_date FROM #backupset bsNext WHERE bs.database_name = bsNext.database_name AND bs.backup_finish_date < bsNext.backup_finish_date ORDER BY bsNext.backup_finish_date) FROM #backupset bs; SELECT bs1.database_name, MAX(DATEDIFF(mi, bs1.backup_finish_date, bs1.next_backup_finish_date)) AS max_minutes_of_data_loss, 'SELECT bs.database_name, bs.type, bs.backup_start_date, bs.backup_finish_date, DATEDIFF(mi, COALESCE((SELECT TOP 1 bsPrior.backup_finish_date FROM msdb.dbo.backupset bsPrior WHERE bs.database_name = bsPrior.database_name AND bs.backup_finish_date > bsPrior.backup_finish_date ORDER BY bsPrior.backup_finish_date DESC), ''1900/1/1''), bs.backup_finish_date) AS minutes_since_last_backup, DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date) AS backup_duration_minutes, CASE DATEDIFF(ss, bs.backup_start_date, bs.backup_finish_date) WHEN 0 THEN 0 ELSE CAST(( bs.backup_size / ( DATEDIFF(ss, bs.backup_start_date, bs.backup_finish_date) ) / 1048576 ) AS INT) END AS throughput_mb_sec FROM msdb.dbo.backupset bs WHERE database_name = ''' + database_name + ''' AND bs.backup_start_date > DATEADD(dd, -14, GETDATE()) ORDER BY bs.backup_start_date' AS more_info_query FROM #backupset bs1 GROUP BY bs1.database_name ORDER BY bs1.database_name DROP TABLE #backupset; GO |
For each of your databases, it lists the maximum amount of time you went between backups over the last two weeks. To see the backup history for each database, copy/paste the query from the “more_info_query” column and run that – it’ll list the full backup history.
Time to ask some tough questions, eh?
To Fix the Problem
Choose your own adventure, and then scroll down
- Set up full backups (details on how to do this below)
- Increase your backup frequency/fix your backup schedules to close any gaps you have
- Use Ola Hallengren’s @ChangeBackupType to automatically do fulls for new databases
1. How to Set Up Full Backups in SQL Server
For servers using maintenance plans, you can edit maintenance plans in SQL Server Management Studio under Management, Maintenance Plans. Your server may have multiple maintenance plans, like one for system databases and one for user databases.
If you’re implementing backups for the first time, consider using Ola Hallengren’s free maintenance scripts. They’re not quite as easy as maintenance plans, but they’re much more flexible. Ola’s scripts even work with third party backup compression products.
Implementing Full Backups With Ola Hallengren’s solution
The first thing you’ll want to do is point your backups away from a local path that also hosts Windows system or other SQL data files. If you don’t have one of those, point it to a network share. This flag is on line 33 or so of the MaintenanceSolution.sql download. If you don’t see it immediately, just search for the @BackupDirectory parameter. This can be changed in the Agent Job as well, which I’ll cover later.
|
1 |
SET @BackupDirectory = N'\\Make\Me\Yours' |
After running the script (just hit F5), you should have a set of brand spankin’ new jobs listed under SQL Agent:
The ones you want to focus on are the FULL backup jobs. You’ll want to schedule your Differential and Log backups differently, according to your RPO and RTO. You can check out Kendra’s overview on log backups here.
Start off with a test run before scheduling by right clicking on the job, and selecting “Start Job at Step…”
You should see this when it completes. If it fails, investigate any errors. They usually have to do with disk space, or permission to write to the path specified.
If this works correctly, go ahead and schedule your job by right clicking and selecting properties. Select the Schedule page, then click “New” down at the bottom
That should get you here:
That’s why I pick a time that’s not midnight on the nose to schedule these things.
This is also where you can alter the backup path and other job variables:
And where you would set up notifications for failures:
For the full list of tweaks, click on over here.
2. Increase Your Backup Frequency
If you’re reading this and thinking, “My backups should probably be running more often,” then you’re probably right. Management assumes you’re never going to lose data – which isn’t realistic. We just need to get our capabilities to match up with management’s goals.
Check out these posts:
- The 9 Letters That Get DBAs Fired
- Back Up Transaction Logs Every Minute. Yes, Really.
- Choosing Your RPO and RTO (Video)
3. Taking Full Backups On Demand
If you need to protect new databases quickly after they’re created, check out Ola Hallengren’s backup scripts.
He has a @ChangeBackupType flag you can pass in – when set to Y, then when it finds a new database during a log backup job, and it can’t take a log backup (since no full has been taken yet), it’ll automatically take a full backup for you.