Backup up System databases only and excluding the User databases

1

How would I go about excluding the users databases without adding them one by one using script below?

DECLARE @name NVARCHAR(256) -- database name
DECLARE @path NVARCHAR(512) -- path for backup files
DECLARE @fileName NVARCHAR(512) -- filename for backup
DECLARE @fileDate NVARCHAR(40) -- used for file name

-- specify database backup directory
SET @path = '\stillwater\Backup\VT-OLAP02\System'

-- specify filename format
SELECT @fileDate = CONVERT(NVARCHAR(20),GETDATE(),112) + '_' + REPLACE(CONVERT(NVARCHAR(20),GETDATE(),108),':','')

DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('APIPReportingDSS','DailyBrickAndMortarDSSNextGenBI','DailyIDEA_TestDatabase', '', '','tempdb') -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping

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 WITH STATS=10, COMPRESSION

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

2

I know you mentioned in an earlier thread that you didn't want to use Ola Hallengren's backup solution, but this is what the code would look like if you did:

EXEC DatabaseBackup
@Databases=N'ALL_DATABASES,-APIPReportingDSS,-DailyBrickAndMortarDSSNextGenBI,-DailyIDEA_TestDatabase'
,@DirectoryStructure=NULL
,@Filename=N'{DatabaseName}_{Year}{Month}{Day}_{Hour}{Minute}{Second}.{FileExtension}'
,@Directory='\\stillwater\Backup\VT-OLAP02\System'
,@BackupType='FULL'
,@Compress='Y'
,@Execute='Y'  -- change to 'N' to print the BACKUP command only

The filename pattern matches the one you want. All you need to do is put a "-" in front of databases to exclude. His code also checks for all aspects that would prevent a database from being backed up. For instance, his code would have reported that your UNC path is invalid.

I'm not sure what reservations you have against his backup scripts, but I'm comfortable saying they're unfounded. It's a battle tested solution used by thousands of people every day. I can't remember the last time I didn't use his scripts to do maintenance, it's well over 8 years ago.

3

There are only 4 system databases : master, model, msdb &tempdb. (also check for
is_distributor = 1 if you have replication). Why not just change your query to simply

WHERE name IN ('master', 'model', 'msdb', 'tempdb')