Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MS SQL Server: Create full database backups of all "user" databases. This script overwrites the previous backup, so there's only ever one backup of the db. Fine for development, not so much for production.
DECLARE
@backupMsg varchar(max),
@backupPath varchar(max),
@backupCommand varchar(max),
@dbName varchar(max);
SELECT
@backupMsg = 'Full backup',
@backupPath = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\';
DECLARE cur_dbBackups CURSOR FOR
SELECT
name
FROM
sys.databases
WHERE
name NOT IN ('master', 'model', 'msdb', 'tempdb');
OPEN cur_dbBackups;
FETCH NEXT FROM cur_dbBackups INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Performing full backup of ' + @dbName + ' database...';
SET @backupCommand = 'BACKUP DATABASE ' + @dbName + ' TO DISK = ''' + @backupPath + @dbName + '_full.bak'' WITH STATS, INIT, CHECKSUM';
EXEC(@backupCommand);
PRINT ' Backup of ' + @dbName + ' complete.';
PRINT '';
FETCH NEXT FROM cur_dbBackups INTO @dbName
END
CLOSE cur_dbBackups;
DEALLOCATE cur_dbBackups;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.