I recently read something that said using the RESTORE WITH REPLACE command could be faster than dropping a database and then performing a RESTORE, because the shell of the file could be used and therefore skip file initialization. I did not think that was the case, but books online wasn’t clear about the situation, so I went ahead and built a quick test case, using ProcMon from sysinternals. If you aren’t familar with the sysinternals tools, you should be—they are a good way to get under the hood of your Windows Server to see what’s going on, and if you’re old like me, you probably used PSEXEC to “telnet” into a Windows server to restart a service before RDP was a thing.
Read on to see how the processes compare.
Do not forget about the certificate! Warning: The certificate used for encrypting the database encryption key has not been backed up. Imagine if you need to recover the backup and you can’t? You will get the dreaded thumbprint error.
Msg 33111, Level 16, State 3, Line 25 Cannot find server certificate with thumbprint ‘0x78FAB5A2A5D593FD3C4E163C90B745F70AB51233’. Msg 3013, Level 16, State 1, Line 25
RESTORE DATABASE is terminating abnormally.
So make sure you respect this certificate (and the key) and back it up and re-create them on the target server for a successful restore.
In SQL Server 2016 and 2017, there’s no reason not to encrypt backups; the marginal cost is practically nil even if you’re low enough on disk space that you need to do backup compression.
In past months, we discovered some edge scenarios related to backup compression for TDE databases causing backups or restores to fail, hence our recommendations have been
Avoid using striped backups with TDE and backup compression.
If your database has virtual log files (VLFs) larger than 4GB then do not use backup compression with TDE for your log backups. If you don’t know what a VLF is, start here.
Avoid using WITH INIT for now when working with TDE and backup compression. Instead, use WITH FORMAT.
Avoid using backup checksum with TDE and backup compression
When you install a new version of SQL Server, you get new features – and sometimes, you’re not told about them. For example, when 2016’s TDE compression came out, nobody told you, “If you back up across multiple files, your backups might suddenly be compressed.” You didn’t know that you had a new thing to test – after all, I don’t know a lot of DBAs who have the time to test that the new version of SQL Server successfully performs restores. They restore their production databases into the new version, test a few things, and declare victory – but testing restores FROM the new version’s backups isn’t usually on that list.
Keep up to date on those patches.
Defines a list of two servers (PowerShell lists are as easy as “”,””)
Pipes them to the
Gathers information about the last full backups for all of your databases on that instance.
Restores the backups to the Destination with a new name. If no Destination is specified, the originating server will be used.
The database is restored as “dbatools-testrestore-$databaseName” by default, which you can change with the -Prefix parameter.
The internal file names are also renamed to prevent conflicts with original database.
A DBCC CHECKTABLE is then performed.
And the test database is finally dropped.
2. Potentially dangerous separation of duties
Backup tools are often run and controlled by windows admins, who may or may not be the same persons responsible for taking care of databases. Well, surprise: if you’re taking backups you’re responsible for them, and backups are the main task of the DBA, so… congrats: you’re the DBA now, like it or not.
If your windows admins are not ok with being the DBA, but at the same time are ok with taking backups, make sure that you discuss who gets accountable for data loss when thing go south. Don’t get fooled: you must not be responsible for restores (which, ultimately, is the reason why you take backups) if you don’t have control over the backup process. Period.
There is plenty of sound advice in this post. These points also apply to roll-your-own solutions as well, but the main focus is on enterprise backup tools, which are in many cases surprisingly shoddy.
Recently I was looking through the error log on one of my test machines and I spotted some unusual errors:
SQL ERROR: 3634 – The operating system returned the error ‘3(The system cannot find the path specified.)’ while attempting ‘DeleteFile’
SQL ERROR: 18272 – During restore restart, an I/O error occurred on checkpoint file (operating system error (null)). The statement is proceeding but cannot be restarted. Ensure that a valid storage location exists for the checkpoint file.
At first I assumed that I may have tried restoring a database to a location that did not exist but this was not the case, the actual issue was with SQL Server’s Default Backup Location.
Read on for the full explanation.
If you ever restore a backup to a new database, there’s something that you should probably be aware of otherwise you could easily find yourself in this situation.
Let’s have a look at what happens when we try to restore a copy of the SQLUndercover database using SSMS. We’re going to kick this off by right clicking on ‘SQLUndercover’ and selecting restore database.
Read this to avoid a panic attack.
As of SQL Server 2017 RC2, we’ll want to accomplish it in a way that is transparent to SQL Server. (Depending on the RTM version whenever it is released, I might change the recommendation on this.) To do this, we’ll want to create a folder on the local file system that actually maps to a remote network share for SQL Server backups.
SSH into your server without elevated privileges at this point.
The network share is presented from a Windows server with the SMB protocol. Linux can connect to this using a compatible protocol called CIFS, or Common Internet File System. We’ll need to install the packages so we can natively connect. On Ubuntu and other Linux distros, the easiest is with the cifs-utils package. To install from the package manager is as simple as this.
Sadly, that credentials file cannot be encrypted.
We recently started using a third party software to do our in-house SQL backups so that the backup files are stored in a redundant and safe place. To confirm that the software was indeed compressing backups as it stated it would, we wanted to see what each backup size actually was in SQL so that we could compare that to what the software was telling us.
SQL stores lots of handy backup information in msdb in the backupset and backupmediafamily tables.
There’s some useful information in these tables, though make sure you clean them up regularly or else msdb can become enormous.
In one of my previous posts I went over a scenario where an Auto restore job was logging Restore errors to a table and the error that was being inserted was ‘3013 – RESTORE LOG is terminating abnormally’ and this was due to SQL Server only providing the Last most error produced which is stored within ERROR_NUMBER() and ERROR_MESSAGE() at point of error.
I found this error less than useful so I set out to try and log something more meaningful , which I ended up doing for the specific error (4305) which was being encountered at the time, but I wanted to make this better and less specific to the 4305 error.
This is a very interesting post and a good example of using built-in error handling functionality to help automate your processes.