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

Advertisements

Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




%d bloggers like this: