db creation
-
Hi All,
I am trying to set up a script that will automatically create a database with the name dynamically added.
Currently I have it such that
@DBName = Archive_20050822 ( not to fussed on the day)
what I want is for it to be YYYYMM where MM is for next month and NOT current,
@DBName = Archive_200509
Also the create statement doent like the @DBName
I want this in a job such that 5 days before the end of the current month it much create the next database....so there needs to be a check process before....
Any ideas would be much appreciated...
USE master
GO
Declare @Archive_Date varchar(12)
Declare @DBName varchar(20)
Set @Archive_Date = (select CONVERT(char(12), Getdate(),112))
Set @DBName = 'Archive_' + @Archive_Date
CREATE DATABASE @DBName
ON
PRIMARY ( NAME = Data,
FILENAME = 'e:\mssql\data\mssql\data\datadat1.mdf',
SIZE = 1MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Image,
FILENAME = 'e:\mssql\data\mssql\data\Imagedat2.ndf',
SIZE = 1MB,
MAXSIZE = 200,
FILEGROWTH = 20)
LOG ON
( NAME = Archlog1,
FILENAME = 'e:\mssql\data\mssql\data\archlog1.ldf',
SIZE = 1MB,
MAXSIZE = 200,
FILEGROWTH = 20)
GO
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
Why are you archiving the data monthly?? How much data do you have to move to make this a worth while experience?
-
It is for a monthly archive and there is around 3Million rows a month that will be archived.
Hope this helps...
Ford Fairlane
Rock and Roll Detective -
IMHO, archiving data into separate monthly named databases is one of the worst things you could do... they (3rd party provider) did the same thing were I work and it's been nothing short of a nightmare. Every time they want to find data in the archives (they always do and they never know what month it's in), they either have to write one of the most hellashish procedures you ever saw or update a view by adding the new monthly database to a monsterous union. Then, when they do a lookup, it takes forever to resolve even with good indexes in place.
I'd like to recommend that you have 1 archive table for each active table you want to archive in 1 separate archive database. If you must create a view to see everything at once, it won't ever change because it will always look at just the active table and the archive table.
To keep from looking at all that data at once, you could keep 3 to 6 months worth of data (I keep 4) in the active table so that recent history can be gotten to relatively quickly. Then, you could have a weekly or daily archiving routine that will move all data that has reached it's active "life expectancy" to the archive tables. To give you the warm fuzzies, one of our in-house developed processes archives some of our tables. Our larger archive tables weigh in at 140 million records each and the indexes make lookups almost a breeze (that's when they need more than 120 days worth of data). Next, I'll be trying to get them to spool records older than 2 or 3 years to a special near/off-line archive database where the records will get backed up one final time and then deleted.
If you insist on using the particular form of death-by-SQL you've identified in your request, then you could create a boilerplate database and a DTS job to copy it and rename it on the fly. I don't know exactly how to do that because I don't archive into monthly databases.
I gotta say it again... the method you identified is going to open one huge can of worms that will cause you huge maintenance headaches... And wait 'till they say (and, they will), "Will you make a view that combines all of the archives so we can do lookups when we don't know what month they occurred in?" And then there's backups...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Believe me if I could do it any other way I would. You see we need to keep live data for 90 on line, and all archive has to be avalible for 7 years to meet regulatory requirements. So anythingolder than 90 days needs to be removed. Any request for data, and this can only be a static input ie no range has for the last year has to be acsessable iwth in an hour, ie 12 archive db's on line, the rest off line as there is a 48 hr turn around for those.
Hope this helps...
Ford Fairlane
Rock and Roll Detective -
Ok... but the scenario you just stated exactly fits the methods I suggested... whatever.
To answer your original question, you need to use some Dynamic SQL to do what you want... the following code is untested but should be pretty close...
DECLARE @Archive_Date varchar(10)
DECLARE @DBName varchar(14)
DECLARE @SQL1 VARCHAR(8000)
SELECT @Archive_Date = LEFT(CONVERT(VARCHAR(10),DATEADD(mm,1,GETDATE()),112),6)
SELECT @DBName = 'Archive_' + @Archive_Date
SELECT @SQL1 =
'USE Master
GO
CREATE DATABASE '+@DBName+'
ON
PRIMARY ( NAME = data'+@DBName+',
FILENAME = ''e:\mssql\data\mssql\data\data'+@DBName+'.mdf'',
SIZE = 1MB,
MAXSIZE = 200,
FILEGROWTH = 20),
( NAME = Image'+@DBName+',
FILENAME = ''e:\mssql\data\mssql\data\Image'+@DBName+'.ndf'',
SIZE = 1MB,
MAXSIZE = 200,
FILEGROWTH = 20)
LOG ON
( NAME = log'+@DBName+',
FILENAME = ''e:\mssql\data\mssql\data\log'+@DBName+'.ldf'',
SIZE = 1MB,
MAXSIZE = 200,
FILEGROWTH = 20)
GO'
EXEC (@SQL1)
Don't say I didn't warn you about creating monthly named DB's... you ARE opening yourself up for a total world of hurt... good luck.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Dito on the design error, this will be an amazing nightmare when you have 7 years worth of data to union on. You're better off keeping it all in a single table with the data as the clustered index.
-
It shouldn't be too much of a nightmare as the search ability it going to be limited to a specific date and not a range. So all we will need to do is pull the Archive db for that date and retrieve the data there without having to union anything.....I hope
Also, another thing to add, when a request is made for archived data, now it gets really messy, instead of actually linking into the archived database, the data itself will be for that date copied back into production where it will remain for a period of 24 hours.
A clean up process is then ment to come accross and remove any date, how it does this is there is a reference table saying what has been restored and what has not. Now unfortunatly this is not my desing or my idea but I have been giventhe task of implementing it...if any one is interetsed in knowing more about what is probably one of the worst archive ideas let me know and I will post it for comments.
Thanks for the warnings though.
Hope this helps...
Ford Fairlane
Rock and Roll Detective -
LMBO! Both at the design and your great sense of humor which allows you to calmly handle the mess they've heaped upon you! I don't know about anyone else, but your description of the design was enough for me.
As much as I disagree with what they've done to you, post back if you get in a bind. Like Red Green says, "We're all in this together and I pullin' for ya".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Ya good luck with that... you're gonna need it.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply