A Fine Slice Of SQL Server
Sander Stad shows how to test database restorations en masse using dbatools:
Testing your backups is a tedious job and it takes a lot of time which I as a DBA don’t have. I don’t have the time to restore a database, run a DBCC command for every database that’s backed up.
There is a solution and it’s called “Test-DbaLastBackup” which is part of the dbatools module.
Rob Sewell also has a recent post on the topic.
Comments closedRob Sewell shows how to restore a slew of databases using one dbatools command:
In my lab I had installed SQL 2016 on a server running Server 2016 TP5 which expired so I needed to re-install Windows and therefore needed to restore all of my user databases again. This was so easy using the dbatools module that I thought it was worth sharing to show how easy your disaster recovery process could be.
Having re-installed Windows and SQL and copied the backup files back to the server (although I could have used a network location), I then had to restore all of the user databases.
This is how I restored all of my user databases using the dbatools module command Restore-SQLBackupFromDirectory
Read on for the answer, as well as a warning that the upcoming dbatools 1.0 release may change some things.
Comments closedWhen you restore a full backup, does it restore to when you started the backup job— or when it completed?
In this episode, I give you the super-short answer. (Spoiler: a point near the end of when the backup was running.) For the full answer, complete with a detailed timeline to help you understand the nitty gritty, read “Understanding SQL Server Backups” by Paul Randal
Click through for the video as well as a bit more information on tail log backups.
Comments closedMala Mahadevan describes a bad backup situation:
I dug into the script – it was simple – it pulled an alphabetical list of databases from system metadata and proceeded to back them up. It didn’t do this one simple thing – leave TEMPDB off the list. So when the backups got down to TEMPDB, they promptly failed. Now as a smart person – I should have just communicated this to her and had it fixed quietly. But, I was young and rather hot headed at that time. It amazed me that a DBA with several years of experience did not know that TEMPDB cannot be backed up. So, I waited until the team meeting the next day. And when the said job failure came up again – I said that I knew the reason and stated why. I also added that this was a ‘very basic thing’ that junior DBAs are supposed to know. I was stopped right there. It did not go down well. Her face was flaming red because a consultant showed her up in front of her boss in a bad light. She said she would talk to her boss and collegues the next day (several of whom were nodding their heads disapprovingly at me) and meeting was abruptly adjourned.
In this case, I don’t think there were any good actors.
Comments closedTim Spann explains how to perform backup/recovery operations and disaster recovery using Hadoop:
You can mirror datasets with Falcon. Mirroring is a very useful option for enterprises and is well-documented. This is something that you may want to get validated by a third party. See the following resources:
- Hive DR with Falcon.
- Data movement and integration (this overview from Hortonworks is very useful for practical data movement between and within the cluster).
- Falcon details (in-depth presentation).
Tim shows several recovery options, making it useful reading if you use Hadoop as a source system for anything (or if you can’t afford it to be down for a 2-3 day period as you recover data).
Comments closedRequirements
- Automation – Complete autopilot, no human interaction.
- Report job status – Accurate reporting in the event the job failed, the CHECKDB failed or the restore failed.
Solution
- Use dbaltools cmdlets for restore and CHECKDB operations
- Use SQL Agent Job automation, logging and alerting
So let’s walk through this implementation together.
You won’t get a turnkey solution from this blog post, but you will get a good process to follow.
Comments closedAndy Galbraith has some advice for backups:
The workaround to this is to back the databases up to one tool and then to *copy* those backup files to the other tool. The best recommendation I have in these situation is always to run “regular” SQL Server Agent job backups to a file location (either a local or network drive) and then to have your third-party tool use file backup for the actual BAK/TRN files from that location *rather* than running the “database agent” on the third party tool to backup the database directly.
In this model the third party tool never directly touches SQL Server – in this client’s environment you would run SQL Server Agent jobs similar to the current maintenance plan (although something better than maintenance plans, such as Ola Hallengren’s scripts or maybe MinionBackup) to backup to actual disk (in thiscase the B: drive) and then use Arcserve to backup the files that have been written on the B: drive.
There’s good advice around not using multiple tools to take backups. This eliminates the possibility of needing to track down backups from two separate devices in order to restore to a point in time (e.g., if some of the log backups are on one device and some on the other).
Comments closedKenneth Fisher shows how to see which backups you have in a particular .bak file:
Restoring a backup file is pretty easy right?
RESTOREDATABASE[Test]FROMDISK ='C:\backups\backup.bak';Ok, but what if more than one database backup is stored in that single backup file? Didn’t know you could do that?
Yep. You can.
Read on for a couple good points regarding those backup files.
Comments closedBrent Ozar shares his thoughts on the recent Gitlab outage:
You can read more about the details in GitLab’s outage timeline doc, which they heroically shared while they worked on the outage. Oh, and they streamed the whole thing live on YouTube with over 5,000 viewers.
There are so many amazing lessons to learn from this outage: transparency, accountability, processes, checklists, you name it. I’m not sure that you, dear reader, can actually put a lot of those lessons to use, though. After all, your company probably isn’t going to let you live stream your outages. (I do pledge to you that I’m gonna do my damnedest to do that ourselves with our own services, though.)
There are some good pointers in here.
Comments closedArun Sirpal investigates a slow backup with an interesting wait stat:
You get this wait type when the checkpoint process realizes that it’s saturating the I/O subsystem. (https://www.sqlskills.com/help/waits/sleep_bpool_flush/), so it seemed that my backup could not even issue a checkpoint successfully. With that thinking I decided to do a manual checkpoint which just made the problem worse.
Read on for more details.
Comments closed