backup_mssql

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_BackupDatabase]    Script Date: 05/10/2011 09:46:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================  
-- Author: Edgewood Solutions  
-- Create date: 2007-02-07  
-- Description: Backup Database  
-- Parameter1: databaseName  
-- Parameter2: backupType F=full, D=differential, L=log
-- =============================================  
ALTER PROCEDURE [dbo].[sp_BackupDatabase]   
       @databaseName sysname, @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 = ''d:\Backup\MSSQL\' + @databaseName + '_Full_' + @dateTime + '.BAK'' WITH FORMAT'
         
       IF @backupType = 'D'  
               SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +  
               ' TO DISK = ''d:\Backup\MSSQL\' + @databaseName + '_Diff_' + @dateTime + '.BAK'' WITH FORMAT, DIFFERENTIAL'
         
       IF @backupType = 'L'  
               SET @sqlCommand = 'BACKUP LOG ' + @databaseName +  
               ' TO DISK = ''d:\Backup\MSSQL\' + @databaseName + '_Log_' + @dateTime + '.TRN'''  
         
       EXECUTE sp_executesql @sqlCommand  
END