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

score 3 · Answer 1 · answered Jul 27 '17 at 14:12

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.

score 1 · Answer 2 · answered Jul 27 '17 at 14:53

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.

Backup databases based on wildcard on database name

2 Answers2