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?
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 onMaintenance Plan
underManagement
Step 2:
Name your PlanStep 3:
Select Database Backup TaskStep 4:
Configure the Task, select Databases, Folder location, Type of Backup (Full, differential, Transaction log) connection etc.Step 5:
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'
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
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
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)
}
}
}