DB restore puzzle

Hi friends,
I like to write a program to automate the following time
consuming and tedious task:
A database was full backed up and got pruned to keep one
week worth of data daily. Now I need to build an archive
type database using all the backup files I have since the
beginning of last year. This archive database contains all
the history data, just lile the original database never
gets pruned. How to do it?
My current plan is to:
1. loop through the backup files
2. For each back up file, restore it using "restore DB"
command
3, After each database is restored, load the data into my
archive database
4. loop to next backup file
5. When all the back files have been processed, remove the
duplicate from archive database.
Although I have not tried, I can expect one problem I am
going to run into is that I can not restore the database
in loop because the prior restored database "is in use".
Does anyone have any suggestion?
Thanks a thousand times in advance,
New Bee> Although I have not tried, I can expect one problem I am
> going to run into is that I can not restore the database
> in loop because the prior restored database "is in use".
If you don't have a connection yourself in the db and no users has connections to the db, then you
don't have to worry. If you are worried about users having connections, you can use ALTER DATABASE
to set to single user and the ROLLBACK option to kick out current users.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"New Bee" <anonymous@.discussions.microsoft.com> wrote in message
news:0beb01c397da$ac242320$a301280a@.phx.gbl...
> Hi friends,
> I like to write a program to automate the following time
> consuming and tedious task:
> A database was full backed up and got pruned to keep one
> week worth of data daily. Now I need to build an archive
> type database using all the backup files I have since the
> beginning of last year. This archive database contains all
> the history data, just lile the original database never
> gets pruned. How to do it?
> My current plan is to:
> 1. loop through the backup files
> 2. For each back up file, restore it using "restore DB"
> command
> 3, After each database is restored, load the data into my
> archive database
> 4. loop to next backup file
> 5. When all the back files have been processed, remove the
> duplicate from archive database.
> Although I have not tried, I can expect one problem I am
> going to run into is that I can not restore the database
> in loop because the prior restored database "is in use".
> Does anyone have any suggestion?
> Thanks a thousand times in advance,
> New Bee
>