Samuele E. Locatelli
40 lines
1.5 KiB
Transact-SQL
40 lines
1.5 KiB
Transact-SQL
USE [master]
|
|
GO
|
|
/****** Object: StoredProcedure [dbo].[sp_BackupDatabase] Script Date: 03/11/2009 17:05:39 ******/
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
-- =============================================
|
|
-- Author: Marco Salvi
|
|
-- Create date: 10/03/2009
|
|
-- Description: Stored procedure per backup database
|
|
-- Parametro1: databaseName
|
|
-- Parametro2: backupType F=full, D=differential, L=log
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[sp_BackupDatabase]
|
|
@databaseName sysname, @directory varchar(500), @backupType CHAR(1)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @sqlCommand NVARCHAR(1000)
|
|
DECLARE @dateTime NVARCHAR(20)
|
|
|
|
SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') +
|
|
REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')
|
|
|
|
IF @backupType = 'F'
|
|
SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
|
|
' TO DISK = '''+@directory + @databaseName + '_Full_' + @dateTime + '.BAK'''
|
|
|
|
IF @backupType = 'D'
|
|
SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
|
|
' TO DISK = ''C:\DbBackup\' + @databaseName + '_Diff_' + @dateTime + '.BAK'' WITH DIFFERENTIAL'
|
|
|
|
IF @backupType = 'L'
|
|
SET @sqlCommand = 'BACKUP LOG ' + @databaseName +
|
|
' TO DISK = ''C:\DbBackup\' + @databaseName + '_Log_' + @dateTime + '.TRN'''
|
|
|
|
EXECUTE sp_executesql @sqlCommand
|
|
END |