[2005] Backup

[shakti5385]

Hi all
I want to take backup of the sql server database.
How it will done??
Thanks

[Dean_Reedy]

Here is a Stored Procedure I call. I did not create it but it does work for MS SQL 2005 express.

set ANSI_NULLS ON
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 = ''C:\POS\Backup\' + @databaseName + '_Full_' + @dateTime + '.BAK''' 
        
       IF @backupType = 'D' 
               SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName + 
               ' TO DISK = ''C:\POS\Backup\' + @databaseName + '_Diff_' + @dateTime + '.BAK'' WITH DIFFERENTIAL' 
        
       IF @backupType = 'L' 
               SET @sqlCommand = 'BACKUP LOG ' + @databaseName + 
               ' TO DISK = ''C:\POS\Backup\' + @databaseName + '_Log_' + @dateTime + '.TRN''' 
        
       EXECUTE sp_executesql @sqlCommand 
END

I call it by:

exec master.dbo.sp_POS_BackupDatabase 'POS','F'

Note: Stored Procedure must be created in the Master DB.

Good luck,

Dean