Com esta procedure fica fácil fazer backup de seus databases n SQl Server 2000/2005. Ao chamar a procedure você precisa informar apenas o tipo de backup e o caminho onde o arquivo de backup deve ser armazenado.
Sintaxe:
Use master
GO
Exec usp_backup_database <Tipobackup> , <Caminho>
Go
Tipobackup
1 – Executa backup Completo subscrevendo o arquivo antigo.
2 – Executa backup Diferencial subscrevendo o arquivo antigo.
3 – Executa backup Log subscrevendo o arquivo antigo para os banco que estão no modelo de recuperação Full.
4 – Executa backup Log adcionando no arquivo antigo para os banco que estão no modelo de recuperação Full.
— Código da Procedure
USE master
go
CREATE PROCEDURE usp_backup_database @typebackup tinyint , @directorypath varchar(2000)
AS
— @typebackup = 1 bkfull with init
— @typebackup = 2 bkdiff with init
— @typebackup = 3 bklog with init
— @typebackup = 4 bklog with noint
declare @databasename varchar(300),
@backupsql varchar(8000),
@fullpath varchar(2500),
@recoverymodel varchar(15),
@dbstatus varchar(10)
DECLARE database_cursor cursor for
SELECT d.name
FROM sysdatabases d
WHERE d.name NOT IN (‘master’,’tempdb’,’model’,’msdb’)
open database_cursor
fetch next from database_cursor
into @databasename
WHILE @@fetch_status = 0
BEGIN
SET @DbStatus = (SELECT CONVERT(VARCHAR (10),DATABASEPROPERTYEX(@databasename,’Status’)))
IF @DbStatus = ‘ONLINE’
BEGIN
set @fullpath = ”
set @fullpath = @directorypath
if (select @typebackup) = 1 — backup full with init
begin
set @backupsql = ”
set @backupsql = @backupsql + ‘backup database ‘ + @databasename + ‘ to disk = N”’ + @fullpath + ‘BKFULL_’ + @databasename + ‘.bak” with init’
exec (@backupsql)
end
if (select @typebackup) = 2 — backup diff with init
begin
set @backupsql = ”
set @backupsql = @backupsql + ‘backup database ‘ + @databasename + ‘ to disk = N”’ + @fullpath + ‘BKDIFF_’ + @databasename + ‘.bak” with init , differential’
exec (@backupsql)
end
if (select @typebackup) = 3 — backup log with init
begin
set @recoverymodel = ”
set @recoverymodel = ( select cast( databasepropertyex(@databasename,’recovery’) as varchar(15) ) )
IF ( select @recoverymodel ) = ‘FULL’
begin
set @backupsql = ”
set @backupsql = @backupsql + ‘backup log ‘ + @databasename + ‘ to disk = N”’ + @fullpath + ‘BKLOG_’ + @databasename + ‘.bak” with init ‘
exec (@backupsql)
end
end
if (select @typebackup) = 4 — backup log with noinit
begin
set @recoverymodel = ”
set @recoverymodel = ( select cast( databasepropertyex(@databasename,’recovery’) as varchar(15) ) )
IF ( select @recoverymodel ) = ‘FULL’
begin
set @backupsql = ”
set @backupsql = @backupsql + ‘backup log ‘ + @databasename + ‘ to disk = N”’ + @fullpath + ‘BKLOG_’ + @databasename + ‘.bak” with noinit ‘
exec (@backupsql)
end
end
end
fetch next from database_cursor
into @databasename
END
close database_cursor
deallocate database_cursor
go