April 18, 2011 at 11:57 pm
Hi ,
This might be of some help.
http://www.sqlservercentral.com/Forums/Topic1064590-391-2.aspx#bm1076255
Regards
Jody
April 19, 2011 at 2:25 pm
try this....create SP
then call it this way....EXEC BackupDbs 'Database_LIVE', 'F:\Backup', 'Database','bak', 'Y', 'N'
USE [master]
GO
/****** Object: StoredProcedure [dbo].[BackupDb] Script Date: 04/19/2011 15:21:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[BackupDb]
@db_name sysname
,@folder nvarchar(255)
,@backup_type varchar(13)
,@backup_extension varchar(10)
,@with_checksum char(1) = 'Y'
,@do_verification char(1) = 'Y'
AS
DECLARE @sql nvarchar(4000)
DECLARE @filename nvarchar(255)
DECLARE @full_path_and_filename nvarchar(1000)
DECLARE @err_msg nvarchar(2000)
DECLARE @crlf varchar(2)
SET @crlf = CHAR(13) + CHAR(10)
--Verify valid backup type
IF @backup_type NOT IN('DATABASE', 'LOG', 'DIFFERENTIAL')
BEGIN
SET @err_msg = 'Backup type ' + @backup_type + 'is not valid. Allowed values are DATABASE, LOG and DIFFERENTIAL'
RAISERROR(@err_msg, 16, 1)
RETURN -101
END
--Make sure folder name ends with '\'
IF RIGHT(@folder, 1) <> '\'
SET @folder = @folder + '\'
--Make file extension starts with '.'
IF LEFT(@backup_extension, 1) <> '.'
SET @backup_extension = '.' + @backup_extension
--Construct filename
SET @filename = @db_name + '_backup_' + REPLACE(REPLACE(REPLACE(CONVERT(char(16), CURRENT_TIMESTAMP, 120), '-', ''), ' ', ''), ':', '')
--Construct full path and file name
SET @full_path_and_filename = @folder + @filename + @backup_extension
--Construct backup command
SET @sql = 'BACKUP ' + CASE @backup_type WHEN 'LOG' THEN 'LOG' ELSE 'DATABASE' END + ' ' + QUOTENAME(@db_name) + @crlf
SET @sql = @sql + 'TO DISK = ' + QUOTENAME(@full_path_and_filename,'''') + @crlf
SET @sql = @sql + 'WITH ' + @crlf
SET @sql = @sql + 'RETAINDAYS=1,' + @crlf
SET @sql = @sql + ' INIT,' + @crlf
SET @sql = @sql + ' NAME = ' + QUOTENAME(@filename,'''') + ',' + @crlf
SET @sql = @sql + 'COMPRESSION,' + @crlf
IF @backup_type = 'DIFFERENTIAL'
SET @sql = @sql + ' DIFFERENTIAL,' + @crlf
IF @with_checksum <> 'N'
SET @sql = @sql + ' CHECKSUM,' + @crlf
--Add backup option below if you want to!!!
--Remove trailing comma and CRLF
SET @sql = LEFT(@sql, LEN(@sql) - 3)
--PRINT @sql
EXEC(@sql)
IF @do_verification = 'Y'
RESTORE VERIFYONLY FROM DISK = @full_path_and_filename
Viewing 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply