snapshot dynamic name - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

snapshot dynamic name

Author

Topic

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-02-26 : 11:40:37

Morning,

I am trying to set up a snapshot database script that will dynamically assign a name to the database. Like every month I have to create a snapshot like so: PYYYYMM so February would be P201002 I have the following script for the snapshot:


CREATE DATABASE P201002 ON
( NAME = Production_Data, FILENAME = 'D:\SQLBackups\ProductionBackup')
AS SNAPSHOT OF Production;
GO

and I have the following to get the right format for the database name:


select 'P'+CAST(YEAR(GETDATE()) AS VARCHAR(4)) 
+ left(CONVERT ( char(20) , getdate() , 101 ),2)as datetime

My question is how can I put them together?

Thanks for any help you can provide.

Laura

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-02-26 : 19:15:20

Put the CREATE DATABASE statement into a variable where you build the string and then EXEC(@variable), so you'll be using dynamic SQL for this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Kristen
Test

22859 Posts

Posted - 2010-02-27 : 00:38:07

" select 'P'+CAST(YEAR(GETDATE()) AS VARCHAR(4))
+ left(CONVERT ( char(20) , getdate() , 101 ),2)as datetime "

Wouldn't that be easier as


select 'P'+CONVERT(char(6), getdate(), 112)

also calling the output column "datetime" may give trouble as that is a reserved word.

But as Tara says re: dynamic SQL:

DECLARE @strSQL nvarchar(4000)

SET @strSQL = 'CREATE DATABASE P' +CONVERT(char(6), getdate(), 112) + ' ON
( NAME = Production_Data, FILENAME = ''D:\SQLBackups\ProductionBackup'')
AS SNAPSHOT OF Production'

PRINT @strSQL

if that works OK then change the PRINT to


EXEC (@strSQL)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources