Backup all databases at a time in SQL Server 2005

This particular script is very useful when you have many databases in your SQL Server 2005 and you need a quick backup of all the databases. Here, the situation may be any thing like you want to format your hard drive due to any problem or you want to move all the databases to another system etc. Just run the following script and all your problem will be solved. One thing to notice here is- you must have a folder or, folder path, in which you want all the backups to be done.

USE master
GO
BEGIN
   DECLARE @DatabaseName NVARCHAR(50)
   DECLARE @BackupPath VARCHAR(400)
   DECLARE @BackUpName VARCHAR(400)
   SET @BackupPath='E:\DB\' ---folder name on your hard drive
   DECLARE cuBackUpAll CURSOR FOR
   SELECT name FROM master.dbo.sysdatabases
   WHERE name NOT IN ('master','model','msdb','tempdb')
   OPEN cuBackUpAll
   FETCH NEXT FROM cuBackUpAll INTO @DatabaseName
   
   WHILE @@FETCH_STATUS=0
   BEGIN
      SET @BackUpName= @BackupPath+@DatabaseName+'.bak'
      BACKUP DATABASE @DatabaseName TO DISK =@BackUpName
      FETCH NEXT FROM cuBackUpAll INTO @DatabaseName
   END
   CLOSE cuBackUpAll
   DEALLOCATE cuBackUpAll
END

2 thoughts on “Backup all databases at a time in SQL Server 2005

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