Skip to content

Instantly share code, notes, and snippets.

@jsheely

jsheely/gist:4526139

Last active Nov 28, 2019
Embed
What would you like to do?
Backup your MS SQL Databases. Perfect for SQL Express.
DECLARE @BackupFile varchar(255), @DB varchar(30), @Description varchar(255), @LogFile varchar(50)
DECLARE @Name varchar(30), @MediaName varchar(30), @BackupDirectory nvarchar(200)
SET @BackupDirectory = 'F:\Backups\Databases\'
--Add a list of all databases you don't want to backup to this.
DECLARE Database_CURSOR CURSOR FOR SELECT name FROM sysdatabases WHERE name <> 'tempdb' AND name <> 'model' AND name <> 'Northwind' AND name <> 'msdb' AND name <> 'master'
OPEN Database_Cursor
FETCH next FROM Database_CURSOR INTO @DB
WHILE @@fetch_status = 0
BEGIN
SET @Name = @DB + '( Daily BACKUP )'
SET @MediaName = @DB + '_Dump' + CONVERT(varchar, CURRENT_TIMESTAMP , 112)
SET @BackupFile = @BackupDirectory + @DB + '_' + 'Full' + '_' +
CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
SET @Description = 'Normal' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.'
IF (SELECT COUNT(*) FROM msdb.dbo.backupset WHERE database_name = @DB) > 0 OR @DB = 'master'
BEGIN
SET @BackupFile = @BackupDirectory + @DB + '_' + 'Full' + '_' +
CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
--SET some more pretty stuff for sql server.
SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.'
END
ELSE
BEGIN
SET @BackupFile = @BackupDirectory + @DB + '_' + 'Full' + '_' +
CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
--SET some more pretty stuff for sql server.
SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.'
END
BACKUP DATABASE @DB TO DISK = @BackupFile
WITH NAME = @Name, DESCRIPTION = @Description ,
MEDIANAME = @MediaName, MEDIADESCRIPTION = @Description ,
STATS = 10
FETCH next FROM Database_CURSOR INTO @DB
END
CLOSE Database_Cursor
DEALLOCATE Database_Cursor
@wojtulab

This comment has been minimized.

Copy link

@wojtulab wojtulab commented Nov 26, 2019

change
BEGIN
21SET @name = @db + '( Daily BACKUP )'
SET @MediaName = @db + '_Dump' + CONVERT(varchar, CURRENT_TIMESTAMP , 112)

to
BEGIN
SET @name = @db + '( Daily BACKUP )'
SET @MediaName = @db + '_Dump' + CONVERT(varchar, CURRENT_TIMESTAMP , 112)

@jsheely

This comment has been minimized.

Copy link
Owner Author

@jsheely jsheely commented Nov 28, 2019

Fixed

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.