A user who is using SQL Server express edition, came to me and ask how to take All Database Backup at a time. Here is a simple script to take all database backup at a specified location.

DECLARE @name VARCHAR(50— database name
DECLARE 
@path VARCHAR(256––– path for backup files
DECLARE 
@fileName VARCHAR(256–– filename for backup
DECLARE 
@fileDate VARCHAR(20–– used for file name
SET 
@path ‘D:\DBBACKUP\’
SELECT @fileDate CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE 
db_cursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN (‘master’,‘model’,‘msdb’,‘tempdb’)

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
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE 
db_cursor
DEALLOCATE db_cursor

Advertisements

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