Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Creating snapshots via script.

Status
Not open for further replies.

sparkbyte

Technical User
Sep 20, 2002
879
US
I am having a problem getting a script running.

Code:
declare @dbName as VarChar(max)

if  exists(SELECT * FROM sys.databases WHERE name LIKE 'ErrorLog_%')

	IF  Not Exists(SELECT * FROM sys.databases WHERE name Like 'ErrorLog_%')
		BEGIN
			Set @dbName = 'ErrorLog_' + CONVERT(VARCHAR(max), GETDATE(), 102) +
						  '_' + REPLACE(CONVERT(VARCHAR(12), GETDATE(), 114),':','.')
						  
			Print 'Created Database SnapShot: ' + @dbName

			Create Database @dbName On 
				(Name = ErrorLog, FILENAME = 
				'D:\Microsoft_SQL_Server\MSSQL10_50.MSSQLSERVER\MSSQL\SnapShots\' + @DBname + '.ss')
				As SNAPSHOT OF ErrorLog
		END
	ELSE
		BEGIN
			Set @dbName = (SELECT * FROM sys.databases WHERE name Like 'ErrorLog_%')
			
			Drop @dbName
			
			Print 'Dropped Database SnapShot: ' + @dbName
			
			Set @dbName = 'ErrorLog_' + CONVERT(VARCHAR(max), GETDATE(), 102) +
			  '_' + REPLACE(CONVERT(VARCHAR(12), GETDATE(), 114),':','.')
			  
			Print 'Created Database SnapShot: ' + @dbName

			Create Database @dbName On 
				(Name = ErrorLog, FILENAME = 
				'D:\Microsoft_SQL_Server\MSSQL10_50.MSSQLSERVER\MSSQL\SnapShots\' + @DBname + '.ss')
				As SNAPSHOT OF ErrorLog
		END


[!]Results[/!]

Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '@dbName'.
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '@dbName'.
Msg 102, Level 15, State 1, Line 30
Incorrect syntax near '@dbName'.
[/color red]



Thanks

John Fuhrman
 
try this:

Code:
declare @dbName as nVarChar(max)
declare @SQL as nVarChar(max)
declare @FileName as nVarChar(max)
--if  exists(SELECT * FROM sys.databases WHERE name LIKE 'ErrorLog_%')

    IF  Not Exists(SELECT * FROM sys.databases WHERE name Like 'ErrorLog_%')
        BEGIN
            Set @dbName = 'ErrorLog_' + CONVERT(VARCHAR(max), GETDATE(), 102) +
                          '_' + REPLACE(CONVERT(VARCHAR(12), GETDATE(), 114),':','.')
                          + '.ss'
            Set @FileName = 'D:\Microsoft_SQL_Server\MSSQL10_50.MSSQLSERVER\MSSQL\SnapShots\'
                          + @DBName
                          
            Print 'Created Database SnapShot: ' + @dbName

            Set @SQL = 'Create Database [@dbName] On 
                (Name = ErrorLog, FILENAME = '@FileName')
                As SNAPSHOT OF ErrorLog'
        END
    ELSE
        BEGIN
            Set @dbName = (SELECT Name FROM sys.databases WHERE name Like 'ErrorLog_%')
            
            Set @SQL = 'Drop [@dbName]' + Char(13) + Char(10) + 'GO' + Char(13) + Char(10)
            
            Print 'Dropped Database SnapShot: ' + @dbName
            
            Set @dbName = 'ErrorLog_' + CONVERT(VARCHAR(max), GETDATE(), 102) +
                          '_' + REPLACE(CONVERT(VARCHAR(12), GETDATE(), 114),':','.')
                          
			Set @FileName = 'D:\Microsoft_SQL_Server\MSSQL10_50.MSSQLSERVER\MSSQL\SnapShots\' 
                          + @DBName
                          + '.ss'         
            Print 'Created Database SnapShot: ' + @dbName

            Set @SQL = @SQL + 'Create Database [@dbName] On 
                (Name = ErrorLog, FILENAME = '@FileName')
                As SNAPSHOT OF ErrorLog'
        END      

execute sp_executesql @SQL, N'@DBName varchar(max), @FileName nvarchar(max)', @DBname = @DBName, @FileName = @FileName

When I run this on my server, it complains about my "Standard Edition". However, I suspect this will work for you as long as you are using an edition that supports snapshots.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
George,

Looks like you have been tired when you posted this. There are several errors in this script, although the idea that we need to use dynamic SQL is correct. I've tried to correct them, but there are too many...


PluralSight Learning Library
 
markros, Instead of replying with some rude, public vote of inability to resolve some error or make a reply that simply holds absolutely no value why not refrain or post your own suggestion.
 
I am not sure why did you take this as rude. There are too many problems in that response and as I said, I started correcting them last night, but it was too late and I simply could not finish. I hope George will correct them himself.

PluralSight Learning Library
 
Although I could have stated my response differently as it's not George's fault, obviously. It's the fault of the original script. George tried to correct a few problems in it and I started looking at George's response, not at the original.

Just to point a few problems:
Code:
IF  Not Exists(SELECT * FROM sys.databases WHERE name Like 'ErrorLog_%')

This seems Ok from the first sight, but in reality it only checks that there is a character after ErrorLog as _ has a special meaning in pattern search. If we want to search for ErrorLog_ we need to escape _ and use [_] or /_

This is minor problem, of course.

Now, this line from the original script
Code:
Create Database @dbName On 
                (Name = ErrorLog, FILENAME = 
                'D:\Microsoft_SQL_Server\MSSQL10_50.MSSQLSERVER\MSSQL\SnapShots\' + @DBname + '.ss')
                As SNAPSHOT OF ErrorLog

is not going to work. We need to use dynamic SQL here, e.g.
Code:
declare @SQL nvarchar(max)

set @SQL = 'Create Database ' + quotename(@dbName) +' On 
                (Name = ErrorLog, FILENAME = 
                'D:\Microsoft_SQL_Server\MSSQL10_50.MSSQLSERVER\MSSQL\SnapShots\'' + @DBname + '.ss')
                As SNAPSHOT OF ErrorLog'

print @SQL -- to test

execute (@SQL)

However, the other problem is with the constructed name of the DB. The special characters such as . in the name may cause some problems and also . in the file name is a bit strange. I would remove : and replace it with _ or nothing in the time portion.

So, these are few quick things that are wrong in the original code.




PluralSight Learning Library
 
Thanks everyone!!

Yes, I was exhausted when I posted this.

I got givin a high priority task and could not get back to this, but I will modify wity your suggestions and get back.

Thanks again!!!

Thanks

John Fuhrman
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top