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
@vCountInt,
@vNumDBsInt,
@vDBNameVarchar(255),
@vBackupPathVarchar(255),
@vFileNameVarchar(100),
@vCreateStringVarchar(1000),
@vBackupStringVarchar(1000),
@vDeleteStringVarchar(1000),
@vNewPathVarchar(1000),
@vErrorStringVarchar(1000)
Set @vBackupPath = '\\IDIBackup\SQL_Dev'
Declare
@vDBListTable(
DBID INT NOT NULL IDENTITY(1, 1),
DatabaseNameVarchar(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 
