Monday, February 8, 2010

Script for taking backup of all the databases in the SQL Server

Hello, do you want to take back up of all the databases from you DB server, ok below is the script for that.
What all you have to do is provide the path where you want to store all the backups and exclude some database which you dont want to take the backup.

Here you go .........

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 = 'E:\AllDBBackups\'

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','MyTestDBIdontWantToTakeBackupOfThisDB')

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 with init

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor



Now go to your folder location and check you will have all the databases .bak file which will have current date postfix in the file name.

2 comments:

  1. good one .....do u know how to take a backup from remote machine?.....

    ReplyDelete
  2. you can still run above script from remote machine, only the thing is backup file location should be of local server .e.g \\servername\C$\DBBackups\

    ReplyDelete