Backup databases based on wildcard on database name

Question

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

• John K. N.: SQL Server Maintenance Solution from Ola is about one of the best solutions on the market and amazingly for free, unless you require support.
• Kevin3NF: If the real issue is drive space, then add space, compress backups, or archive older ones to another drive. Failing that you cannot use Maintenance plans with wildcards.

Answer 1

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.

Answer 2

The above answer from @kin is perfect. If at all you are looking for a script that you can use to backup your desired databases then use the below query. I have been using it to configure backup for Express Editions.

Here is the query,i have edited the script accordingly,

DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name
-- specify database backup directory
SET @path = 'D:\SQL\Backup\' 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name like '%foo%' -- Condition here these databases (foo)
OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name 
WHILE @@FETCH_STATUS = 0 
BEGIN 
SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
BACKUP DATABASE @name TO DISK = @fileName 
FETCH NEXT FROM db_cursor INTO @name 
END 
CLOSE db_cursor 
DEALLOCATE db_cursor

Let me know if this helps.

• Kin Shah: You dont need a cursor. Dynamic sql will generate the commands and you can use sp_executesql to run those commands.
• Ramakant Dadhichi: @Kin : Thank you , will try to avoid cursor. It servers the purpose for me so never thought of doing something else. Will follow your advise. Thanks again.