Easy Backup Script

11 09 2013

Here is a handy backup script for those that do not like to type much. It automatically enumerates the database names and creates a backup of that database in a user set location. Make sure the @path variable is set!


/* Backup all database except for tempdb
Created By: Christopher Provolt
Date: 11 September 2013 */

DECLARE @database VARCHAR(255)
DECLARE @cmd varchar(1000)
DECLARE @path varchar(255)

--set @path variable to the root of where backup files will be created. Ensure following backslash is used.

SET @path = 'E:\Backup\'

DECLARE DatabaseCursor

CURSOR FOR SELECT name FROM master.sys.databases WHERE name NOT IN ('tempdb') AND state = 0

OPEN DataBaseCursor FETCH NEXT FROM DatabaseCursor INTO @database
WHILE @@FETCH_STATUS = 0

BEGIN  select 'Database: ', @database

SET @cmd = 'BACKUP DATABASE ['+@database+'] TO DISK = N'''+@path+@database+'.bak'''
EXEC(@cmd)

FETCH NEXT FROM DatabaseCursor INTO @database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor