2

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

2
  • 1
    SQL Server Maintenance Solution from Ola is about one of the best solutions on the market and amazingly for free, unless you require support.
    – John K. N.
    Jul 27, 2017 at 14:10
  • 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.
    – Kevin3NF
    Jul 27, 2017 at 14:17

2 Answers 2

3

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.

1

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.

2
  • 1
    You dont need a cursor. Dynamic sql will generate the commands and you can use sp_executesql to run those commands.
    – Kin Shah
    Jul 27, 2017 at 14:57
  • @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. Jul 27, 2017 at 15:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.