automation ... ! :)

Question

automation ... ! :)

kamrul3d

Grasshopper

Points: 15
More actions
March 5, 2009 at 4:56 am

#200564

Here is my requirement,
i have around 5000 backup file(full database backup) for the last 2 years.No i wants to create a data warehouse(simple) for my database.
my initial tables are
1. item(id,description)
2. category(id,name,pid)
and there is a column changes occurs in different version of my database... for example when the image functionality is added to we have changed the table item(id,description) to item(id,description,image_link)... and so on.
now i wants all the items information for the last 2 years(using 5000 backup files)
what will be the process?
any sample scripts are highly appreciated.
Thanks
**********
[
from my observation ...
Procedure may be...
[*] create new database for warehouse purpose and another one for restore purpose.
[1] use current item table structure
[2] column should support null
(current_tables_columns-initial_tables_columns)
[3] extra one column for database version(i.e .bak file name)
... here i might use cursors for looping purpose of my backup files(5000)
[4] restore one database
[5] if new id then
insert new record
else
update previous record
I'm new to administrating please help me!
]

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323547 More actions · Answer 1

interesting...

there must be a business reason why you are going thru each file, instead of just using the last backup...You basically need the history of changes(if any) between backups, right?

this is the only part I'm not understanding:

and there is a column changes occurs in different version of my database... for example when the image functionality is added to we have changed the table item(id,description) to item(id,description,image_link)... and so on. ?

what are we after here? SCHEMA changes where columns got added, or DATA changes where the "description" in the table[item] was changed between backups. is this for one specific table or ALL tables?

if you can provide the real SELECT statement you want to extract from each db, i can help , but this part is opaque to me.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tosscrosby SSCoach Points: 18044 More actions · Answer 2

kamrul3d (3/5/2009)
Here is my requirement,
i have around 5000 backup file(full database backup) for the last 2 years.No i wants to create a data warehouse(simple) for my database.
my initial tables are
1. item(id,description)
2. category(id,name,pid)
and there is a column changes occurs in different version of my database... for example when the image functionality is added to we have changed the table item(id,description) to item(id,description,image_link)... and so on.
now i wants all the items information for the last 2 years(using 5000 backup files)
what will be the process?
any sample scripts are highly appreciated.
Thanks
**********
[
from my observation ...
Procedure may be...
[*] create new database for warehouse purpose and another one for restore purpose.
[1] use current item table structure
[2] column should support null
(current_tables_columns-initial_tables_columns)
[3] extra one column for database version(i.e .bak file name)
... here i might use cursors for looping purpose of my backup files(5000)
[4] restore one database
[5] if new id then
insert new record
else
update previous record
I'm new to administrating please help me!
]

You guys taking the same class (or have the same prof)??

http://www.sqlservercentral.com/Forums/Topic669071-24-1.aspx

-- You can't be late until you show up.

kamrul3d Grasshopper Points: 15 More actions · Answer 3

Lowell (3/5/2009)
interesting...
there must be a business reason why you are going thru each file, instead of just using the last backup...You basically need the history of changes(if any) between backups, right?
this is the only part I'm not understanding:
and there is a column changes occurs in different version of my database... for example when the image functionality is added to we have changed the table item(id,description) to item(id,description,image_link)... and so on. ?
what are we after here? SCHEMA changes where columns got added, or DATA changes where the "description" in the table[item] was changed between backups. is this for one specific table or ALL tables?
if you can provide the real SELECT statement you want to extract from each db, i can help , but this part is opaque to me.

[/i]

schema are changes where columns are added...and this is for specific one table.

"You basically need the history of changes(if any) between backups, right?">> not only the changes all items(deleted between different bak files) information.

what procedure should be the best to achieve my goal...?

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 4

check out Visual Studio 2005 for databases or one of the open source code version control apps like CVS. but i think they only work if you use them all along. i think you might have to do things the old fashioned way and spend a few months doing this manually