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.
David Klee shows how to back up a SQL Server on Linux database over the network:
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.
Adrian Buckman has a script which tracks error messages related to database restorations:
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.
Dave Mason notes that the STOPAT date option when restoring a log backup is temperamental:
There’s nothing I see in the documentation regarding the format for “time“. But there are a couple of examples, including this one:
RESTORE LOG AdventureWorks FROM AdventureWorksBackups WITH FILE=4, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';That string looks suspiciously like a US English date format. I suspect that wouldn’t work for languages that don’t recognize “Apr” as a month. And what if the date is displayed in one of the many date formats used outside of the US? Lets find out!
Dave tried 21 different date formats; click through for the results.
Shane O’Neill “has a friend” who learned an important lesson about the database restore GUI:
GUIs are good for….
…discovery.
They give you the option to script out the configurations you have chosen. If my friend had chosen to script out the restore, rather then clicking “OK” to run it, maybe he would have caught this mistake when reviewing it – rather than overwriting the Live database with 2 week old data and spending a weekend in the office with 3 colleagues fixing it.
Plus if you ever want to ensure that you know something, try and script it out from scratch.
Read the whole thing; good thing that totally didn’t happen to Shane and was just his friend!
Astute SQL-ers may attempt to add a trigger to the
restorehistorytable over in msdb. It’s in thedboschema, which might make you hopeful. We all know triggers in that peskysysschema don’t do a darn thing.You guessed it, restores get tracked there. So there’s, like, something inside SQL telling it when a restore happens.
Guess what, though? A trigger on that table won’t fire. Not
FOR INSERT, notAFTER INSERT, and not nothin’ in between.
Read on for more things that don’t work… Also check out the comments; I think Dave Mason has the best answer there.
David Fowler announces sp_RestoreScript:
I’m sure we’ve all been there, we need to restore a database to 11:34am, four days ago. What’s the first step? To go off hunting down the relevant full and differential backups and then figure out what transaction logs we need to play in and in what order. Well here at SQL Undercover we’ve got the solution to all your restoration woes, the latest addition to the Undercover Toolbox, sp_RestoreScript.
sp_RestoreScript will do all the hard work for you, it’ll hunt down all the backup files that you need to restore your database to a specific time and present you with a nice, simple script to run based on the parameters that you give it.
Click through for the script, which includes great ASCII art.
I had a recent project to enable backup encryption on all our servers. Then question from the storage team came up will this required additional space. Well by then I had already enabled in all our test servers so I wrote a query that would compare the average size of backups before encryption to after encryption. Keep in mind we do keep only two weeks of history in our backup tables so this is a fair comparison. If you don’t have maintenance tasks to clean up your backup history then you should have backup_start_time to the where clauses to get more accurate numbers and setup a maintenance tasks to keep your msdb backup history in check.
Unfortunately, Tracy leaves us in suspense regarding whether they did increase.
Steve Jones shows how to restore a database saved in .bacpac format:
I needed to get the WideWorldImporters sample database for a project and noticed that there was a BACPAC available. I downloaded it and needed to restore this as a database. At least, that’s what many people would think.
However, if you go to the restore dialog, and select Device and then pick your location, there’s no filter for a .bacpac. In fact, if you choose one, it won’t restore. You’ll get the “no backupset selected” error.
Read on for a step-by-step guide showing how to do this.
Kevin Feasel
2017-08-15
Backups
No Comment