Back up all MS SQL databases at once

This article will discuss how to backup all MS SQL databases with one script. A separate file will be created for each database.

1. Log into your server through Remote Desktop Connection.
2. Open SQL Server Management Studio and select the server name.
   
3. Click the New Query button and enter in the following data:

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 = 'C:\Backup\' 

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 

4. Make sure that the directory in the SET @path line exists. If the directory (in this case C:\Backup) does not exist, create the directory else the script will fail.
5. Click the Execute! button and the script will execute.
6. Once finished, a dialog box will appear stating such. Now all databases are backed up in C:\Backup with the database name as the file name.
   
   

   To take the full backup of all the databases in MS SQL server :

   1. Log into your server through Remote Desktop Connection.
   2. Open SQL Server Management Studio and select the server name.
   3. Click the New Query button and enter in the following data:
      -----------------------------------------------------------------
      DECLARE @BackupFile varchar(255), @DB varchar(30), @Description varchar(255), @LogFile varchar(50)
      DECLARE @Name varchar(30), @MediaName varchar(30), @BackupDirectory nvarchar(200)
      SET @BackupDirectory = 'C:\Backup\'
      --Add a list of all databases you don't want to backup to this.
      DECLARE Database_CURSOR CURSOR FOR SELECT name FROM sysdatabases WHERE name <> 'tempdb' AND name <> 'model' AND name <> 'master'
      OPEN Database_Cursor
      FETCH next FROM Database_CURSOR INTO @DB
      WHILE @@fetch_status = 0
      
          BEGIN
              SET @Name = @DB + '( Daily BACKUP )'
              SET @MediaName = @DB + '_Dump' + CONVERT(varchar, CURRENT_TIMESTAMP , 112)
              SET @BackupFile = @BackupDirectory + + @DB + '_' + 'Full' + '_' +
                  CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
              SET @Description = 'Normal' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.'
      
              IF (SELECT COUNT(*) FROM msdb.dbo.backupset WHERE database_name = @DB) > 0 OR @DB = 'master'
                  BEGIN
                      SET @BackupFile = @BackupDirectory + @DB + '_' + 'Full' + '_' +
                          CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
                      --SET some more pretty stuff for sql server.
                      SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.'
                  END   
              ELSE
                  BEGIN
                      SET @BackupFile = @BackupDirectory + @DB + '_' + 'Full' + '_' +
                          CONVERT(varchar, CURRENT_TIMESTAMP , 112) + '.bak'
                      --SET some more pretty stuff for sql server.
                      SET @Description = 'Full' + ' BACKUP at ' + CONVERT(varchar, CURRENT_TIMESTAMP) + '.'
                  END
                  BACKUP DATABASE @DB TO DISK = @BackupFile
                  WITH NAME = @Name, DESCRIPTION = @Description ,
                  MEDIANAME = @MediaName, MEDIADESCRIPTION = @Description ,
                  STATS = 10
              FETCH next FROM Database_CURSOR INTO @DB
      END
      CLOSE Database_Cursor
      DEALLOCATE Database_Cursor
      -----------------------------------------------------------------
   4. Make sure that the directory in the SET @path line exists. If the directory (in this case C:\Backup) does not exist, create the directory else the script will fail.
   5. Click the Execute! button and the script will execute.
   6. Once finished, a dialog box will appear stating such. Now all databases are backed up in C:\Backup with the database name as the file name.