Taking backup of all the databases in SQL Server

Question

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?

Answer 1

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.

Answer 2

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:

• Setting up a Maintenance Plan to Backup Databases
• Back Up Database Task (Maintenance Plan)

• Alan B: Note: Express editions of SQL Server don't offer Maintenance Plan functionality.

Answer 3

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'

Answer 4

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\'

Answer 5

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 

Answer 6

Here is solution using Powershell

Get-SqlDatabase -ServerInstance YourInstanceName |
Where { $_.Name -ne 'tempdb' } |
Backup-SqlDatabase

Which I have mentioned here also How to backup SQL server database

By default, backup is saved at default server backup location under the name databasename.bak

Answer 7

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 = 'DriveNeme:\Backup Folder Name\' 

-- specify filename format

 SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name IN ('model','master') -- exclude these databases
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

Answer 8

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)
 }
 } 
 }