Script to backup all databases

,

A proc which gets a list of all databases (Exluding "excluded" ones) and backs them up.
The backup files are stored in the directory \DatabaseName\DatabaseName.bak
UNC names are supported provided SQL Server has write access to the location.

It deletes backups older than x (Current 3) days.
This can be changed.


Add a job to execute the script of proc on a daily basis.

CREATE PROCEDURE GlobalBackup
as

Set NoCount On

Declare
	@vCount		Int,
	@vNumDBs	Int,
	@vDBName	Varchar(255),
	@vBackupPath	Varchar(255),
	@vFileName	Varchar(100),
	@vCreateString	Varchar(1000),
	@vBackupString	Varchar(1000),
	@vDeleteString	Varchar(1000),
	@vNewPath	Varchar(1000),
	@vErrorString	Varchar(1000)

Set @vBackupPath = '\\IDIBackup\SQL_Dev'

Declare
	@vDBList	Table(
				DBID INT NOT NULL IDENTITY(1, 1),
				DatabaseName	Varchar(256)
				)
Insert Into @vDBList
Select Name From master.dbo.SysDatabases Where Name Not IN ('TempDB')
Set @vNumDBs = @@RowCount
Set @vCount = 1

While @vCount < @vNumDBs
	Begin
		Select @vDBName = DatabaseName From @vDBList Where DBID = @vCount
		Set @vNewpath = @vBackupPath + '\' + @vDBName + '\'
		Set @vDeleteString = 'Del "' + @vNewPath + @vDBName + ' ' + Convert(Varchar(12), DateAdd(Day, -3, GetDate()), 104) + '.bak"' + ' /F /Q'
		Exec xp_CMDShell @vDeleteString, NO_OUTPUT
		Set @vCreateString = 'MD ' + @vNewPath
		Exec xp_CMDShell @vCreateString, NO_OUTPUT
		Set @vFileName =  @vDBName + ' ' + Convert(Varchar(12), GetDate(), 104) + '.bak'
		Set @vBackupString = 'BACKUP DATABASE [' + @vDBName + '] TO DISK = ''' + @vNewPath + @vFileName + ''' WITH  INIT ,  NOUNLOAD ,  NAME = N''' + @vDBName + ''',  NOSKIP ,  STATS = 50,  NOFORMAT'
--Print @vBackupString
		Exec (@vBackupString)
		If @@Error <> 0
			Begin
				Set @vCount = @vNumDBs
				Set @vErrorString = 'net send * SQL Backup failed on (' + @vDBName + ')! Please view event log!'
				Exec XP_CMDShell @vErrorString
			End
		Set @vCount = @vCount + 1
	End

Rate

Share

Share

Rate