I have a Microsoft SQL Server 2005 DB server. In the DB server, I have around 250 user databases. I have to take a back up of all these databases. Since, manually taking backup consumes lot of time, I am looking for a Batch script or DB script which will automatically take the backup of all the 250 databases. Can anyone please help on this?

migrated from stackoverflow.com Jul 26 '12 at 7:40

This question came from our site for professional and enthusiast programmers.

  • Do you have to do this once, or on a regular basis? – Jon Seigel Jul 26 '12 at 13:23
  • Are all the backups using the same recovery model? Automating backups are nice but if the backups do not meet your recovery needs for your user's data, the backup means nothing. – Shawn Melton Jul 30 '12 at 7:25

Note: First create a folder on D: drive. (e.g. D:\User_DataBackup\)

Step 1: Create a procedure that are given below.

Create PROCEDURE [dbo].[UserDataBaseBackUp]
AS

BEGIN
SET NOCOUNT ON;

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:\User_DataBackup\' -- as same as your created folder' 
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),104)
DECLARE db_cursor CURSOR FOR
  SELECT name
  FROM MASTER.dbo.sysdatabases
  WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')
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
END

Step 2: Execute the above procedure.

 EXEC [UserDataBaseBackUp]

You can also schedule this procedure. This procedure is tested hopefully it will help.

Develop a Maintenance Plan

SQL Server has this amazing feature where it will create the script and job for you

  • Step 1:
    Right click on Maintenance Plan under Management

    Right Click on Management

  • Step 2:
    Name your Plan

    Name the Plan

  • Step 3:
    Select Database Backup Task

    Database Backup Task

  • Step 4:
    Configure the Task, select Databases, Folder location, Type of Backup (Full, differential, Transaction log) connection etc.

    Configure The Backup Task

  • Step 5:
    Configure the Job Schedule

    Configure the Job Schedule


I recommend having a separate plan for system-databases and your user-databases to avoid any kind of problem

Helping Links:

  • Note: Express editions of SQL Server don't offer Maintenance Plan functionality. – Alan B Sep 17 '15 at 10:32

I know for a fact that maintaining a lot of databases with SSMS could be a little confusing, but it's straight forward.

I can recommend a more effective way, that is using Ola Hallengren's maintenance script. It is very cool and very effective. And you can do much more than just backing up all databases, you can do all types of maintenance procedures.

For example, you can backup all databases, compress them and encrypt them with a certificate of your choice, by using a command like this (and they are all optional, and encryption and compression will not work on SQL Server 2005 but I think it will show the flexibility and strength of Ola's script):

EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Compress = 'Y',
@Encrypt = 'Y',
@EncryptionAlgorithm = 'AES_256',
@ServerCertificate = 'MyCertificate'
  • I also recommend Ola Hallengrens scripts as they are very good and flexible. However, the example given will not work on the databases user1493004 asks to backup. Backup compression was introduced in SQL2008 and encryption in SQL2014. user1493004 uses SQL2005 databases. – Peter Elzinga Oct 20 '15 at 6:47
  • that's true, just wanted an example to show the strength of this script. thanks for your comment. :) – Sina Hassanpour Oct 20 '15 at 6:49
  • 1
    I use Ola's scripts, and can say that they work really well. Also, if you have questions, Ola Hallengren will answer them. I would never manually write my own scripts, when these work so well. – CaM Sep 18 '17 at 13:49

Working on the answer by JP, I've added a parameter to pass the destination directory (and quite possibly add more options):

Create PROCEDURE [dbo].[UserDataBaseBackUp] (
  @OutputDir varchar(255)
) AS
BEGIN
    SET NOCOUNT ON;

    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 = @OutputDir
    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),104)
    PRINT 'Starting Backups'
    DECLARE db_cursor CURSOR FOR
        SELECT name FROM MASTER.dbo.sysdatabases
            WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')
        OPEN db_cursor
            FETCH NEXT FROM db_cursor INTO @name
            WHILE @@FETCH_STATUS = 0 BEGIN
                SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
                    PRINT 'Starting Backup For ' + @name
                    BACKUP DATABASE @name TO DISK = @fileName WITH FORMAT
                FETCH NEXT FROM db_cursor INTO @name
            END
        CLOSE db_cursor
    DEALLOCATE db_cursor
    PRINT 'Backups Finished'
END
GO

So then:

EXEC UserDataBaseBackUp @OutputDir = 'F:\Backups\SQL Databases\'

You can use SELECT Statement or CURSOR like this:

DECLARE @PathForBackUp VARCHAR(255)
SET @PathForBackUp = 'F:\Backup\User DB\'

SELECT 'BACKUP DATABASE [' + name + '] TO  DISK = N''' + @PathForBackUp + '' + name + '.bak''
WITH NOFORMAT, NOINIT,  NAME = N''' + name + '_FullBackUp'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 5'
FROM sys.databases
WHERE database_id > 4

OR

DECLARE @DBName VARCHAR(255)  
DECLARE @PathForBackUp VARCHAR(255) 
DECLARE @FileName VARCHAR(255)  
DECLARE @DateFile VARCHAR(255)
DECLARE @SQL NVARCHAR(2048) 
SET @PathForBackUp = 'F:\Backup\User DB\'  
SET @DateFile = REPLACE(REPLACE(CONVERT(VARCHAR(20),GETDATE(),120) ,' ','T'), ':','') 

DECLARE BACKUPING CURSOR FOR   
SELECT name  
FROM master.dbo.sysdatabases WHERE dbid > 4 

OPEN BACKUPING    
FETCH NEXT FROM BACKUPING INTO @DBName    
WHILE @@FETCH_STATUS = 0    

BEGIN    
        SET @FileName = @PathForBackUp + @DBName + '_' + @DateFile + '.BAK'  
    SET @SQL = 'BACKUP DATABASE '+@DBName+ ' TO DISK = '''+@FileName+''' WITH COMPRESSION ' 
    PRINT @SQL 
    EXECUTE sp_executesql @sql   
    FETCH NEXT FROM BACKUPING INTO @DBName  

END    

CLOSE BACKUPING    
DEALLOCATE BACKUPING 

You could also use PowerShell as shown in Use PowerShell to Backup All User Databases by Buck Woody:

# Performs a Full backup followed by a transaction log backup on all user databases

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")  | out-null
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'BWOODY1\SQL2K8'
$bkdir = $s.Settings.BackupDirectory
$dbs = $s.Databases
$dbs | foreach-object {
    $db = $_

    if ($db.IsSystemObject -eq $False -and $db.IsMirroringEnabled -eq $False) {
        $dbname = $db.Name
        $dt = get-date -format yyyyMMddHHmmss
        $dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
        $dbbk.Action = 'Database'
        $dbbk.BackupSetDescription = "Full backup of " + $dbname
        $dbbk.BackupSetName = $dbname + " Backup"
        $dbbk.Database = $dbname
        $dbbk.MediaDescription = "Disk"
        $dbbk.Devices.AddDevice($bkdir + "\" + $dbname + "_db_" + $dt + ".bak", 'File')
        $dbbk.SqlBackup($s)
        if ($db.DatabaseOptions.RecoveryModel -ne 'Simple') {
            $dt = get-date -format yyyyMMddHHmmss
            $dbtrn = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
            $dbtrn.Action = 'Log'
            $dbtrn.BackupSetDescription = "Trans Log backup of " + $dbname
            $dbtrn.BackupSetName = $dbname + " Backup"
            $dbtrn.Database = $dbname
            $dbtrn.MediaDescription = "Disk"
            $dbtrn.Devices.AddDevice($bkdir + "\" + $dbname + "_tlog_" + $dt + ".trn", 'File')
            $dbtrn.SqlBackup($s)
            }
        }     
    }

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.