Website Design United States, Website Design California, Website Designing United States, Website Designing California

Backup SQL Database to Disk - SQL

This stored procedure will setup a backup device if necessary and backup your SQL database to disk. In addition to the data file, the procedure also backs up the log file.

CREATE PROCEDURE proc_backuptodisk
@db VARCHAR(30), -- Database name
@basepath VARCHAR(255) = 'F:\MSSQL2k\mssql\BACKUP\' -- Base path to the file. Set to your own default
AS
SET NOCOUNT ON

DECLARE @backupname VARCHAR(50) -- Name of backup job
DECLARE @datadevice VARCHAR(32) -- Name of data backup device
DECLARE @filepath VARCHAR(300) -- Physical path to data backup file
DECLARE @logdevice VARCHAR(32) -- Name of log backup device
DECLARE @logpath VARCHAR(300) -- Physical path to log backup file
DECLARE @result smallint -- Result when testing if device exists

-- Ensure @basepath ends with an \
IF RIGHT(@basepath, 1) <> '\'
BEGIN
SET @basepath = @basepath + '\'
END

-- These values should not change
SET @backupname = @db + ' backup ' + CONVERT(VARCHAR(10), GETDATE(), 105)
SET @datadevice = @db + '_1'
SET @filepath = @basepath + @datadevice + '.dat'
SET @logdevice = @db + '_log_1'
SET @logpath = @basepath + @logdevice + '.dat'

-- check to see if the data backup device exists
-- if the data backup device does not exist, create it
IF NOT EXISTS(select * from sysdevices where name = @datadevice)
BEGIN
-- add the data backup device
EXEC SP_ADDUMPDEVICE 'disk', @datadevice,
@filepath
END

-- backup the database
BACKUP DATABASE @db TO @datadevice
WITH INIT, NOUNLOAD,
NAME = @backupname, SKIP

-- if the Recovery Model is simple, skip the log backup
IF (select (status & 8) from master.dbo.sysdatabases where name = @db) = 0
BEGIN
-- check to see if the log backup device exists
-- if the log backup device does not exist, create it
IF NOT EXISTS(select * from sysdevices where name = @logdevice)
BEGIN
-- Add the log backup device
EXEC SP_ADDUMPDEVICE 'disk', @logdevice,
@logpath
END

-- backup the log
BACKUP LOG @db TO @logdevice
WITH INIT, NOUNLOAD
END
GO



WEB DESIGN INDIA
42 B Malviya Nagar , New Delhi-110017

Skype: manmeetsi
Email: support.webdesignindia@gmail.com
Tel: 91-011-40502005, 9810067295

 















 


© 2008-2009 dotnet4all.com