Backup all Databases in MSSQL – SQL Script

If you have a server you would have been presented at one point with the need to backup all databases in one go. Here’s a script that would do just that.


CREATE PROCEDURE Tblbackup

AS

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

— specify database backup directory
SET @path = ‘D:SQLBackup’
— specify filename format
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’ )
— exclude these databases
DECLARE @DatabaseFolder NVARCHAR(255)
SET @DatabaseFolder = @path + @fileDate
EXEC master.sys.Xp_create_subdir      @DatabaseFolder;

    OPEN db_cursor
FETCH next FROM db_cursor INTO @name

    WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @fileDate +  + @name + ‘.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