January 9, 2014 at 12:07 am
Good morning!
I have written a small script to snapshot databases that our developers can use prior to making a major change in their test environment. Unfortunately it seems a little reluctant to work as it should!
This is the script:
declare @DBName varchar(50);
declare @Filename varchar(150);
declare @SnapshotID varchar(10);
declare @FilePath varchar(100);
set @DBName= N'Transfer_DPL';
set @SnapshotID= 1;
set @FilePath = N'H:\DBSnapshots\';
set @Filename = @FilePath + @DBName + @SnapshotID + '.snap';
select 'Snaphot Path' as Item, @Filename as Value
union
select 'Database Name', @DBName;
begin
CREATE DATABASE [@DBName]
ON (
NAME= [@DBName],
FILENAME= [@Filename]
);
end;
It parses with no problem and when I check the variables as entered the following is returned:
Snaphot PathH:\DBSnapshots\Transfer_DPL1.snap
Database NameTransfer_DPL
The database name is corrent and the path it refers to is correct.
The problem occurs when I execute the script to create the snapshot.
The following error is returned:
Msg 5105, Level 16, State 2, Line 17
A file activation error occurred. The physical file name '@Filename' may be incorrect. Diagnose and correct additional errors, and retry the operation.
Msg 1802, Level 16, State 1, Line 17
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
I can't figure out where the problem is. Can anyone offer any advice?
Cheers!
Kev
January 9, 2014 at 1:30 am
Your CREATE DATABASE needs to be Dynamic SQL if you're going to pass parameters like that.
Also, if you're trying to create a Database Snapshot then the syntax is a little out: http://sqlrambling.net/2013/12/12/snapshot-backup-basic-example/
January 9, 2014 at 1:49 am
BrainDonor (1/9/2014)
Your CREATE DATABASE needs to be Dynamic SQL if you're going to pass parameters like that.Also, if you're trying to create a Database Snapshot then the syntax is a little out: http://sqlrambling.net/2013/12/12/snapshot-backup-basic-example/
Hmmm.....I am not a developer and the term Dynamic SQL doesn't mean much to me! Is there an easy way to rewrite what I have to accomplish the task?
And I have looked through the link you sent but I'll be damned if I can see my mistake in the syntax!
Thanks!
January 9, 2014 at 1:53 am
You can't use variables for file names. You need to build up the command as a string and execute it - dynamic SQL.
This is not allowed
NAME= [@DBName],
FILENAME= [@Filename]
That's saying you want a name of "@DBName" with a file name of "@FileName", not the values the variables contain.
Your script is also not creating a snapshot, it's creating a normal database, you're missing the portions of the command that specify that it's a snapshot. See the link Brain provided or look in Books Online
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
January 9, 2014 at 2:00 am
Blind as a bat! Thanks Gail!
January 9, 2014 at 2:09 am
Dynamic SQL is executed by using 'EXECUTE' - http://technet.microsoft.com/en-us/library/ms188332.aspx
or
SP_EXECUTESQL - http://technet.microsoft.com/en-us/library/ms188001.aspx
You'll find plenty of articles on these commands within SSC, come back if you have further questions on this.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply