Now the jobs are not going to run as they are as they have CmdExec steps and this is not supported in SQL on Linux so we have to make some changes to the steps. As I blogged previously, this is really easy using PowerShell
First we need to grab the jobs into a variable. We will use Get-SQLAgentJobHistory from the sqlserver module which you need to download SSMS 2016 or later to get. You can get it from https://sqlps.io/dl As we are targeting a Linux SQL Server we will use SQL authentication which we will provide via Get-Credential and then take a look at the jobs
It’s not “point, click, done,” but Rob shows you certainly can do it.
Compressing your backups has very few downsides. It’s usually faster (the additional time for compression is less than the time saved by reduced IO) and of course, the backups are smaller. I have run into a few issues, however.
On one occasion there was 75gb free on a drive, the last full backup was only 50gb and the database had not grown significantly in size. Interestingly there was an error when we tried to run a backup. Not enough disk space.
Read on for the explanation as well as the solution.
Ok great let’s check this backup file using a cool tool (XVI32). I really want the contact number of a guy called SQLDOUBLEG because I need his help tuning my SQL Servers so I go looking for a text string, hopefully the phone number will be close by.
As Arun points out, you don’t need TDE to get encrypted backups, but it does the job.
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.
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.
When 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.
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.
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:
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).
- Automation – Complete autopilot, no human interaction.
- Report job status – Accurate reporting in the event the job failed, the CHECKDB failed or the restore failed.
- 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.
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).