Home > Database, MSSQL > MS SQL: Backup database Stored Procedure

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

Advertisements
Categories: Database, MSSQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: