The most satisfying meal is the one you make yourself, and database backup jobs are no different.
Many DBAs use maintenance plans as a “one-stop-shop” for maintaining their SQL Server databases. After all, they do backups, integrity checks, index maintenance… What more could you possibly want?
But the truth of the matter is that maintenance plans are a lot like fast food: Yeah, you get the basics, but what if you have more specific needs in your environment that the “basics” can’t cover? You receive the SQL Server equivalent of a greasy, overworked sixteen-year-old giving you the stink-eye as they throw the “basics” in your bag anyway.
Not fun.
You’re left with two options: You either back up your databases with a third-party tool, or you set up your own backup job with SQL Server Agent. Today, we’ll be discussing the latter.
“But SQL Server Agent requires these things called ‘SCRIPTS’! Where am I going to get these?”
There are plenty of premade maintenance scripts out there (Ola Hallengren’s being one of the most famous and highly recommended), but we’re not here to talk about those. We’re here to make our own home-cooked backup solution.
Great. Now I’m craving Cracker Barrel.
The scripts I’m about to provide are a jumping-off point. They are by no means fully optimized for maximum performance (or any other DBA buzzwords); these are just examples of how even a junior or accidental DBA could throw together an automated backup script using basic T-SQL and programming logic (I use a WHILE loop instead of a cursor, for Pete’s sake!). Your mileage may vary, of course, given that different databases may have different backup needs—these are just scripts for full and log backups, the bare minimum in the backup world. There is plenty of room for customization and especially improvement, but these should help you get started in most cases.
/*
Name: Basic full backup script
Author: Faryl O'Neil Hoover - XTIVIA
Date: 2/28/2021
Can be used in a SQL Server Agent job that runs daily, at minimum.
*/
-- Variables
DECLARE @DBCount int
SELECT @DBCount = count(*) from sys.databases;
DECLARE @myCount int = 0;
DECLARE @dbName nvarchar(max);
DECLARE @SQL nvarchar(max);
WHILE (@myCount <= @DBcount) -- Basic WHILE loop cycling through databases on instance
BEGIN
IF EXISTS -- Just in case databases are skipped (like tempdb, since it can't be backed up)
(SELECT name FROM sys.databases
WHERE database_id = @myCount
AND name != 'tempdb' -- Can't back up tempdb
AND state = 0 -- Make sure databases are online
AND is_read_only = 0) -- Make sure databases aren't read-only
BEGIN
SELECT @dbName = name FROM sys.databases WHERE database_id = @myCount; -- Grab database name to plug into backup script
SET @SQL = 'BACKUP DATABASE ' + @dbname + -- Database backup statement
' TO DISK = ''' + @dbName + '_' + -- Name is automatically plugged in
CONVERT(char(8), getdate(), 112) + -- Date is automatically generated from GETDATE()
'.BAK'' WITH COMPRESSION, CHECKSUM, STATS = 10';
PRINT @SQL; -- For general debugging
EXEC sp_executesql @SQL; -- Backup statement is executed
END
SET @myCount = @mycount + 1 -- Increment the counter
END
/*
Name: Basic log backup script
Author: Faryl O'Neil Hoover - XTIVIA
Date: 2/28/2021
Can be used in a SQL Server Agent job that runs hourly, for instance.
*/
-- Variables
DECLARE @DBCount int
SELECT @DBCount = count(*) from sys.databases;
DECLARE @myCount int = 0;
DECLARE @dbName nvarchar(max);
DECLARE @SQL nvarchar(max);
WHILE (@myCount <= @DBcount) --Basic WHILE statement for cycling through databases
BEGIN
IF EXISTS
(SELECT name FROM sys.databases
WHERE database_id = @myCount
AND name != 'tempdb'
AND recovery_model = 1 -- Main difference from full backup script; only grab databases we can take log backups for
AND state = 0 -- Make sure databases are online
AND is_read_only = 0) -- Make sure databases are not read-only
BEGIN
SELECT @dbName = name FROM sys.databases WHERE database_id = @myCount;
SET @SQL = 'BACKUP LOG ' + @dbname + ' TO DISK = ''' + @dbName +
'_LOG_' +
CONVERT(char(8), getdate(), 112) + '_' +
CONVERT(char(2), datepart(hour, getdate())) + '_' + -- Another main difference from full backup script: Automatically generate hour/minute for file name, since log backups should be taken at least hourly
CONVERT (char(2), datepart(minute, getdate())) +
'.TRN'' WITH COMPRESSION, CHECKSUM, STATS = 10';
PRINT @SQL;
EXEC sp_executesql @SQL;
END
SET @myCount = @mycount + 1
END
One final note: Sometimes, “not enough backups” can turn into “too many backups”—on disk, that is. Depending on your individual circumstances (such as database size and data recovery objectives), you may only need to keep the most recent backups on disk for a quick and easy restore while putting your older backups into long-term storage, such as tape or Grandpa’s shed. Again, your mileage may vary (long-term storage may not even be necessary, depending on your environment), but it bears mentioning as uncontrolled backups can fill up disk space FAST. Regardless, make sure to keep an eye on your disk space and remove older backups when needed.
If you have more specific backup needs and you have nowhere else to turn, our VirtualDBA staff is always here to help! Making sure your data can be recovered in the event of a disaster is our number-one priority, so don’t take chances on irregular and unreliable backups! Let us optimize your environment so you’ll be prepared for anything that comes your way.