February 14, 2012 at 12:18 am
Hi, can anyone help me with this?
I am receiving this error:
Msg 102, Level 15, State 1, Procedure BackUpDatabase, Line 26
Incorrect syntax near '@DatabaseName'.
Here's my proc.
Am I missing anything on the drop database statement?
------------
CREATE PROCEDURE [dbo].[BackUpDatabase]
@BackupFilePath VARCHAR(200) = 'C:\DbBackup\SQ2005\',-- path for backup db
@DatabaseName VARCHAR(100) = 'DBTEST',-- database name
@DropDatabase VARCHAR(1) = 'Y'-- (if Y) drop the database after the backup is complete
AS
BEGIN
DECLARE @fileDate VARCHAR(20)-- date used for file name
DECLARE @fileName VARCHAR(500)-- whole file path and file name
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
--Set Filename of the Backup File
SET @fileName = @BackupFilePath + @DatabaseName + '_' + @fileDate + '.BAK'
BACKUP DATABASE @DatabaseName TO DISK = @fileName
IF NOT (ISNULL(@DropDatabase, 'Y') = 'N')
BEGIN
DROP DATABASE @DatabaseName
END
END
-------------
Thank you!
Becca
February 14, 2012 at 1:12 am
You need to create dynamic SQL for this using the EXEC statement (or sp_executeSQL).
Your statement would become:
CREATE PROCEDURE [dbo].[BackUpDatabase]
@BackupFilePath VARCHAR(200) = 'C:\DbBackup\SQ2005\', -- path for backup db
@DatabaseName VARCHAR(100) = 'DBTEST', -- database name
@DropDatabase VARCHAR(1) = 'Y' -- (if Y) drop the database after the backup is complete
AS
BEGIN
DECLARE @fileDate VARCHAR(20); -- date used for file name
DECLARE @fileName VARCHAR(500); -- whole file path and file name
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112);
--Set Filename of the Backup File
SET @fileName = @BackupFilePath + @DatabaseName + '_' + @fileDate + '.BAK';
EXEC ('BACKUP DATABASE ' + @DatabaseName + ' TO DISK = ' + @fileName);
IF NOT (ISNULL(@DropDatabase, 'Y') = 'N')
BEGIN
EXEC ('DROP DATABASE ' + @DatabaseName)
END
END
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 14, 2012 at 1:15 am
Wow! It works!
Thanks to Koen Verbeeck!!!
February 14, 2012 at 1:18 am
ladyblue1075 (2/14/2012)
Wow! It works!Thanks to Koen Verbeeck!!!
No problem, glad to help.
Maybe you could verify the backup first before dropping the database, just to make sure you don't run into problems.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 14, 2012 at 1:22 am
How can I do verification?
I'm having problem now.
There error saying:
Msg 3702, Level 16, State 4, Line 1
Cannot drop database "TA_839" because it is currently in use.
Also, I'm not sure if the back up of database is correct cos it should be a complete backup, not an append to existing. You think I got it right?
February 14, 2012 at 1:31 am
On my Stored procedure, I put
USE [master]
on top of the content.
and yet, i'm getting this error:
Msg 3702, Level 16, State 4, Line 1
Cannot drop database "TA_839" because it is currently in use.
Please help!
February 14, 2012 at 1:36 am
Could be someone else is using it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 14, 2012 at 1:38 am
Yeah, I got it now.
This link helped!
Thanks again!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply