Tuesday 21 April 2009

MS SQL: Backup database Stored Procedure

Here is a stored procedure to create a full backup of the database.
Run the code on the master database.
To backup a db run:
sp_backupdatabase 'databasename'
To verify the backup we need to use use CHECKSUM.

USE "master"
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE "dbo"."sp_backupdatabase" (
@DBName nvarchar(50)
)
WITH ENCRYPTION
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),':','')
SET @sqlCommand = 'BACKUP DATABASE ' + @DBName + ' TO DISK = ''D:\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\' + @DBName + '_backup_' + @dateTime + '.BAK'' WITH CHECKSUM'
EXECUTE sp_executesql @sqlCommand
END

No comments: