It consists of a database to store information about backups of databases in a table with information about the last backups, and a procedure to do the backup. The below script also adds all databases with an database_id higher than 4, i.e. excludes master, model, msdb, and tempdb. If you want specific databases only just change the content of the Managed_Databases table.
USE master;And then we have the procedure, it's some lines of code. Hopefully you can read through it.
GO
CREATE DATABASE Simple_Maintenance;
GO
USE Simple_Maintenance;
GO
CREATE TABLE Managed_Databases
(
[db_id] int NOT NULL,
[db_name] sysname NOT NULL,
Last_Full_Backup datetime2(0) NULL,
Last_Full_Backup_Number int NULL,
Last_Differential_Backup datetime2(0) NULL,
Last_Differential_Backup_Number int NULL
);
GO
INSERT INTO Managed_Databases([db_id], [db_name])
SELECT database_id, name FROM sys.databases WHERE database_id > 4;
GO
USE Simple_Maintenance
GO
CREATE OR ALTER PROCEDURE Make_Backup
@backup_destination varchar(max) = 'F:\Databases\MSSQL13.MSSQLSERVER\MSSQL\Backup',
@full_backup_day int = 7,
@debug int = 1
/*
Day for full backup
1 Monday
2 Tuesday
3 Wednesday
4 Thursday
5 Friday
6 Saturday
7 Sunday
*/
AS
BEGIN
SET DATEFIRST 1;
DECLARE
@database varchar(max),
@name_template varchar(max) = '@database_@type_@number',
@type varchar(max),
@number varchar(max);
DECLARE
@backup varchar(max) = '
BACKUP DATABASE [@database]
TO DISK = ''@backup_destination\@name.BAK''
WITH
STATS = 10,
And finally you just need to run the procedure. If you execute it with default parameters it will run in debug mode and only print out the syntax for doing backups. The prinout do not care if you have a full backup or not when there is a differential backup day. It gives you the syntax.INIT, -- I FORGOT THIS IN THE ORIGINAL RECIPE
COMPRESSION
',
@exec varchar(max),
@db_id int,
@day int = DATEPART(weekday, getdate()),
@name varchar(max);DECLARE dbs CURSOR FOR
SELECT [DB_ID], [DB_NAME] FROM Managed_Databases;OPEN dbs;FETCH NEXT FROM dbs INTO @db_id, @database;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @day = @full_backup_day
BEGIN
SET @type = 'FULL';
SELECT @number = CAST((ISNULL(Last_Full_Backup_Number, 2) % 2) + 1 as varchar(max)) FROM Managed_Databases WHERE [DB_ID] = @db_id;
SET @name = REPLACE(
REPLACE(
REPLACE(
@name_template, '@database',@database),
'@type', @type),
'@number', @number);
SET @exec = REPLACE(
REPLACE(
REPLACE(
@backup, '@database',@database),
'@backup_destination', @backup_destination),
'@name', @name);
END
ELSE
BEGIN
SET @type = 'DIFFERENTIAL';
SELECT @number = CAST((ISNULL(Last_Differential_Backup_Number, 2) % 2) + 1 as varchar(max)) FROM Managed_Databases WHERE [DB_ID] = @db_id;
SET @name = REPLACE(
REPLACE(
REPLACE(
@name_template, '@database',@database),
'@type', @type),
'@number', @number);
SET @exec = REPLACE(
REPLACE(
REPLACE(
@backup, '@database',@database),
'@backup_destination', @backup_destination),
'@name', @name) +
', ' + @type;
END;
IF @debug = 1
BEGIN
PRINT @exec;
END
ELSE
BEGIN
IF @day = @full_backup_day
BEGIN
EXEC (@exec);
UPDATE Managed_Databases
SET
Last_Full_Backup = getdate(),
Last_Full_Backup_Number = CAST(@number as int)
WHERE [DB_ID] = @db_id;
END
ELSE
BEGIN
IF EXISTS(SELECT 1 FROM Managed_Databases WHERE [DB_ID] = @db_id AND Last_Full_Backup IS NOT NULL)
BEGIN
EXEC (@exec);
UPDATE Managed_Databases
SET
Last_Differential_Backup = getdate(),
Last_Differential_Backup_Number = CAST(@number as int)
WHERE [DB_ID] = @db_id;
END;
END;
END;
FETCH NEXT FROM dbs INTO @db_id, @database;
END;
CLOSE dbs;
DEALLOCATE dbs;
END;
The procedure do not have any error checking either, you should probably add try...catch logic, and also checks in the database exists, and are in a backupable state. As I said, I cooked this up for fixing a simple need.
