CREATE PROC dbo.sp_BackupDb
(
@DbName SYSNAME ,
@BackupType SYSNAME ,
@BackupDirectory SYSNAME =
"C:\" ,
@Extension NVARCHAR(25) ,
@IncludeSecondsInTimestamp BIT = 1
)
AS
DECLARE
@OurString NVARCHAR(1000) ,
@DateString NVARCHAR(25) ,
@TimeString NVARCHAR(25)
SET
@DateString = CONVERT(VARCHAR, GETDATE(), 112)
SET
@TimeString = REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '')
SELECT @OurString = @BackupDirectory +
@DbName + '_' + @BackupType + '_'
+
@DateString + '_' + @TimeString + @Extension
PRINT
@OurString
DECLARE
@SqlCommand NVARCHAR(4000)
SET
@SqlCommand = 'BACKUP DATABASE [' +
@DbName + '] TO DISK = ' + @OurString
IF
@BackupType = 'DIFF'
OR
@BackupType = 'DIFFERENTIAL'
SET
@SqlCommand = @SqlCommand + ' WITH DIFFERENTIAL'
EXEC sp_executesql @SqlCommand
GO
--TO TEST
EXEC dbo.sp_BackupDb 'MASTER','FULL','C:\','.BAK',0
--===SHOW THE FOLLOWING ERROR====
C:\MASTER_FULL_20120105_214203.BAK
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'C:'.
--===ADD A PRINT AND THE ' & ' AROUND THE FILE PATH. EACH
NEEDS ''''(4)======
ALTER PROC dbo.sp_BackupDb (
@DbName SYSNAME
,
@BackupType SYSNAME
,
@BackupDirectory SYSNAME='C:'
,
@Extension NVARCHAR(25)
,
@IncludeSecondsInTimestamp BIT = 1
)
AS
DECLARE @OurString NVARCHAR(1000)
,
@DateString NVARCHAR(25)
,
@TimeString NVARCHAR(25)
SET @DateString = CONVERT(VARCHAR, GETDATE(), 112)
SET @TimeString = REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '')
SELECT @OurString =
@BackupDirectory
+
@DbName + '_'
+
@BackupType + '_'
+
@DateString + '_'
+
@TimeString
+
@Extension
PRINT @OurString
DECLARE @SqlCommand NVARCHAR(4000)
SET @SqlCommand = 'BACKUP DATABASE [' +
@DbName + '] TO DISK = ' + ''''+@OurString+''''
print @sqlCommand
IF @BackupType = 'DIFF' OR
@BackupType = 'DIFFERENTIAL'
SET
@SqlCommand = @SqlCommand + ' WITH DIFFERENTIAL'
EXEC sp_executesql @SqlCommand
GO
EXEC dbo.sp_BackupDb 'MASTER','FULL', 'A:\','.BAK',0
--===========SEE THE CORRECT RESULTS BELOW=====
C:\MASTER_FULL_20120105_214110.BAK
BACKUP DATABASE [MASTER]
TO DISK = 'C:\MASTER_FULL_20120105_214110.BAK'
Processed 376 pages for database 'MASTER', file 'master' on file 1.
Processed 3 pages for database 'MASTER', file 'mastlog' on file 1.
BACKUP DATABASE successfully processed 379 pages in 0.701
seconds (4.218 MB/sec).
