Sql-server – Backup databases based on wildcard on database name

backupsql serversql-server-2016

We have a MS SQL 2016 databse server with a number of databases that need backing up. We are using a maintenance plan to backup all databases at the moment but running low on space on one of our drives.

We have the following databases:

foo1
foo2
..
..
foo10
fred01
fred02

How would we go about swapping out the T-SQL in the maintenance plan task to only backup the databases foo* and not the fred databases?

I could select the databases manually but over time new foo databases are added and I want to ensure that these are automatically backed up without needing to update the maintenance plan.

Thanks

Best Answer

Use T-sql to query sys.databases and backup your databases. one free solution is to use Ola's backup solution

A. Back up all user databases, using checksums and compression; verify the backup; and delete old backup files

EXECUTE dbo.DatabaseBackup
@Databases = '%foo%', -- only databases that will have foo in name
@Directory = '\\server\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y',
@CleanupTime = 24

Change parameters as per your needs.

Related Solutions

Sql-server – Backup database and clean old backup question

You should probably move from maintenance plans to SQL Agent Jobs and leverage a script like those written by http://ola.hallengren.com/. You would create a Job Step for each database. It will give you the flexibility that you are looking for.

UPDATE: My info above was incorrect. You can use the Ola.Hallengren.com script to do what you are looking for without having to add a job step for each database. After reviewing the script info I saw this:

From: http://ola.hallengren.com/sql-server-backup.html

CleanupTime

Specify the time, in hours, after which the backup files are deleted. If no time is specified, then no backup files are deleted.

Backup files are deleted after each database is backed up and verified. Backup files are deleted only if the backup and verification of the database were successful.

DatabaseBackup has a check to verify that transaction log backups that are newer than the most recent full or differential backup are not deleted. This is to guarantee that you can always perform a point-in-time restore.

Sql-server – SQL Server Maintenance Plan TSQL query to log the backup databases

You can get the backup date for each database backed up after the plan started from

--INSERT INTO YourBackupLogTable
--     (DatabaseName, LastBackupStartDate, LastBakupFinishDate, LastBackupDurationSecs)
SELECT db.name AS DatabaseName
    , MAX(lb.backup_start_date) AS LastBackupStartDate
    , MAX(lb.backup_finish_date) AS LastBakupFinishDate
    , DATEDIFF(S, MAX(lb.backup_start_date), MAX(lb.backup_finish_date) ) AS LastBackupDurationSecs
FROM sys.databases AS db
JOIN msdb.dbo.backupset lb ON lb.database_name = db.name
WHERE lb.backup_start_date >= (SELECT MAX(l.start_time) AS Start 
                                 FROM msdb.dbo.sysmaintplan_plans p
                           INNER JOIN msdb.dbo.sysmaintplan_log l ON p.id = l.plan_id
                                WHERE name = 'Your backup plan')
GROUP BY db.name

Best Answer

Related Solutions

Sql-server – Backup database and clean old backup question

Sql-server – SQL Server Maintenance Plan TSQL query to log the backup databases

Related Question