Eric Blinn shows the upside to performing checksum validation during backups:
Since a full backup reads every data page it makes sense that we can ask SQL Server to calculate and compare each checksum during this operation. Even a differential backup reads all of the pages for any extent (a group of 8 data pages) if any one page in it changes so it will validate some pages that didn’t change.
Read on for a demonstration.
Garry Bargsley continues the 12 Days of dbatools series, this time taking a look at taking backups:
We are on the home stretch and I have saved the last third of the series to cover some pretty cool/amazing commands. Not that all 500+ commands are not cool/amazing but these last few are used continuously in my environment and bring automation closer to your finger tips with PowerShell and SQL Server then ever before.
Have you ever been asked to take a database backup before a developer does a deployment? Have you ever been asked to backup a database to restore it to a development or other environment? How about, hey can you take a quick transaction log backup? These can all be accomplished using Backup-DbaDatabase and only changing a couple of parameters each time.
This post is strictly about taking backups, but dbatools is also great about testing backups.
It’s surprisingly difficult to get this information in SQL Server. In fact I was quite stuck trying to figure out how to get this information when I realized that the good people over at Brent Ozar Unlimited already do some checking on this for their sp_Blitz tool.
A quick look at the above code showed me that
dbo.sysssispackageswas what I was looking for. Combine this with:
- 1. Some hack-y SQL for the frequency in human readable format, and
- 2. Some even more hack-y SQL to join on the SQL Agent Job name
And we had pretty much the XML for the Maintenance Plan and the SQL Agent Job Schedule that they were against.
Shane has made the code available as well, so check it out if you have any maintenance plans you’re trying to understand and maybe even get away from.
Lori Brown walks us through the solution to an error she experienced:
I was recently performing an in-place upgrade of SQL 2008 R2 to SQL 2014 on one of my client’s servers. I have done a ton of successful and uneventful in-place upgrades and was surprised when the upgrade failed with the error message: “Failed to create a new folder ‘X:\SQLBackups’. The specified path is invalid (for example, it is on an unmapped drive).” This client had over the years changed from using a local drive for all backups to having backups sent to a network share. So, the X drive really was no longer in existence.
Read on for the solution.
Currently, the output from the following commands is supported:
- Get-DbaAgentJobHistory
- Get-DbaBackupHistory
You will run the above commands as you would normally do but pipe the output to
ConvertTo-DbaTimeline, the same way as you would with any otherConverTo-*PowerShell function. The output is a string that most of the time you will save as file using theOut-Filecommand in order to open it in a browser.
Then, with the ConvertTo-DbaTimeline cmdlet, you can convert that into an HTML page which looks pretty good.
Lori Brown shows how you can see the last time a particular database was restored:
I have a client that uses a lot of disconnected log shipping on a few servers. They do this because they want to have a copy of their database that is actually hosted by software vendors for reporting purposes. So, disconnected log shipping it is! We pull down files that have been FTP’d to the corporate FTP site and apply those logs locally and leave the databases in standby so that they can be queried.
(If you want to review how to set up disconnected log shipping complete with FTP download scripts, all SQL jobs and log shipping monitoring, then hop on over to https://www.sqlrx.com/setting-up-disconnected-log-shipping/ to check it out!)
Of course every so often there is a glitch and one or all databases can get out of synch pretty easily. When we receive a notification that tlogs have not been restored in a while, the hunt is on to see what happened and get it corrected. Since I have disconnected log shipping set up, I can’t get a log shipping report from SSMS that will tell me errors and what was the last log applied. So, I made a query that will give me the most recent file that was restored along with the file name, date, database, etc. I can pull back all databases or by uncommenting a line can filter by a single or multiple databases.
Lori also includes a helpful script.
What happens if I use data compression and backup compression, do I get double compression?
This is a great question, and without diving too deeply into how backup compression works I’m going to do a simple experiment on the WideWorldImporters database. I’ve restored this database to my local SQL Server 2016 instance and I’m simply going to back it up several times under different conditions.
After restoring the database it’s about 3GB in size, so our testing will be on a reasonably small database. It would be interesting to see how the results change as the database size increases, perhaps a future blog post.
Click through for the answer.
The primary reason most transaction logs start to (unexpectedly) grow is that the transaction log hasn’t been backed up. This is usually caused by one of two situations: the log backup job failed, or the log backup job didn’t start. Most options for backing up SQL transaction logs (3rd party software, SQL Agent job, or maintenance plan) will include an alert/notification mechanism for when the job fails. Where some backup options are lacking is in notifying that a (log) backup that should have been taken, wasn’t. If the backup (or management) software doesn’t include an alert for missing backups, SQL Server (2008 & newer) has a solution. Policy Based Management (PBM) includes a policy (Last Log Backup Date) that will send an email notification when the last backup date/time exceeds a specified threshold.
A good use for a woefully underappreciated tool.
Arun Sirpal explains how backups work with Azure SQL Data Warehouse:
The question is how are backups done with Azure SQL DW?
It is very different from Azure SQL DB (which you would expect). Azure SQL DW has a totally different architecture to its classic database counter-part. Restore points are the key here. Automatic ones are taken throughout the day and are kept for seven days only. Worst case scenario is the time between the restore points will be eight hours hence giving an eight hour RPO (Recovery Point Objective).
You can also create manual restore points, as Arun shows.
Adrian Buckman has a script which makes sure your backups are where msdb says they are:
Here is the information that the script provides:
Warn of restores over the top of the database since its last FULL backup
Show database snapshots currently against the database
Show the last FULL, DIFF and LOG backup for the database including the backup durations and backup age.
Backup file information such as backup start/finish time , file path , first LSN , Last LSN , a status column which states whether the log chain is in tact based on First and last LSN but also if the file exists on disk, and finally a file exists column which will tell you if the file still exists on disk.
This is a great script if you take transaction log backups frequently (typically a good idea).
Kevin Feasel
2018-12-19
Backups, Corruption