March 22, 2011 at 11:13 am
I have a series of remote databases detached from any network. I need to aggregate the data into a single repository. My plan is to receive .bak files via mail from each of the remote databases, restore those databases into a matching databases on my server here at the home office. Then to replicate each of those to a master database.
However, I have read that a restore on a replicated database can only occur from a backup of that same database.
Is what I am wishing to do possible? If so, is there a guide somewhere? I am a programmer, not a DBA and I'm a little out of my element. I would really prefer to not have to write independent select/insert update statements for each of the tables in my database.
Thank you in advance for your advice and help.
Jason
March 22, 2011 at 11:27 am
jasona22 (3/22/2011)
I have a series of remote databases detached from any network. I need to aggregate the data into a single repository. My plan is to receive .bak files via mail from each of the remote databases, restore those databases into a matching databases on my server here at the home office. Then to replicate each of those to a master database.However, I have read that a restore on a replicated database can only occur from a backup of that same database.
Is what I am wishing to do possible? If so, is there a guide somewhere? I am a programmer, not a DBA and I'm a little out of my element. I would really prefer to not have to write independent select/insert update statements for each of the tables in my database.
Thank you in advance for your advice and help.
Jason
Jason - looking for some clarity before I can answer fully. So, when you say you are going to aggregate the data into a single repository, I'm assuming a single database, is that correct? Also, it sounds like you want to use replication to get all that data from the disparate databases into the "master" database, is that also correct? If the answer is yes to both of those you are fine so far and that should work without problems.
As for restoring a backup of a replicated database, you can restore it anywhere. The only thing that has to be considered is whether you want to keep replication as part of the restore. By default it will eliminate all the replication metadata unless you use the KEEP_REPLICATION option.
Let me know if you can provide more detail and sorry for not getting it clearly based on what you posted.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 22, 2011 at 11:39 am
Jason - looking for some clarity before I can answer fully. So, when you say you are going to aggregate the data into a single repository, I'm assuming a single database, is that correct? Also, it sounds like you want to use replication to get all that data from the disparate databases into the "master" database, is that also correct? If the answer is yes to both of those you are fine so far and that should work without problems.
As for restoring a backup of a replicated database, you can restore it anywhere. The only thing that has to be considered is whether you want to keep replication as part of the restore. By default it will eliminate all the replication metadata unless you use the KEEP_REPLICATION option.
Let me know if you can provide more detail and sorry for not getting it clearly based on what you posted.
Sorry David. In my attempt to be concise, I failed to be clear.
Yes, my goal is to move several databases into a single.
As an example, I have 10 databases that have no network connectivity in remote areas of the world. I plan to receive .bak files from each of these.
I then wish to restore them to matching databases on a SQL Server 2005 instance in my office. Meaning I will have 10 exact copies of those remote databases.
Then I would like those 10 local databases to replicate to a single master database; thus aggregating the data.
I would like this to be repeatable and automatable. So next month, another series of .bak files come in, they get restored to their twin on the local server and replication kicks them to the master.
So far, I have not had any success even restoring a backup of a replicated database back to itself. I read that you cannot restore a replicated backup to a different database or server and expect replication to continue; however, the way it was described is that if I did a backup of a replicated (publisher) and restored it elsewhere that it wouldn't continue replicating. However, I want to restore a non-replicated database onto one setup for replication and then have it replicate to the master.
Am I making sense?
Thank you
March 22, 2011 at 12:09 pm
jasona22 (3/22/2011)
Yes, my goal is to move several databases into a single.As an example, I have 10 databases that have no network connectivity in remote areas of the world. I plan to receive .bak files from each of these.
I then wish to restore them to matching databases on a SQL Server 2005 instance in my office. Meaning I will have 10 exact copies of those remote databases.
Then I would like those 10 local databases to replicate to a single master database; thus aggregating the data.
I would like this to be repeatable and automatable. So next month, another series of .bak files come in, they get restored to their twin on the local server and replication kicks them to the master.
So far, I have not had any success even restoring a backup of a replicated database back to itself. I read that you cannot restore a replicated backup to a different database or server and expect replication to continue; however, the way it was described is that if I did a backup of a replicated (publisher) and restored it elsewhere that it wouldn't continue replicating. However, I want to restore a non-replicated database onto one setup for replication and then have it replicate to the master.
Yes, you are making sense and things are definitely clearer. Not sure there is an "easy" answer but you have some options. Before I go into that though, you can actually restore a replicated database but you would have to be restoring it to the same publisher. There might be some trickery that you could invoke that would allow it to be restored to another publisher but I'm thinking it wouldn't work. Regardless though, replication wouldn't work for the situation that you are describing as the new backups that you will be receiving will not have the replication metadata in them.
So, with that being said, I would consider putting together an SSIS package that would copy all that data across to the master repository. That would be repeatable as the databases would stay consistently named and your destination would always be the same. I'm also assuming that you would be appending data in the master repository which would also make it more in line with an SSIS solution. You could also do it all with T-SQL with scripts as well - which would be my preference.
Let me know if what I shared doesn't make sense or if you would like more detail. Sounds like a fun project though!
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 22, 2011 at 12:17 pm
Thank you David.
I do not know nor really understand SSIS. I can't even spell SSIS <-- note red squiggly
As for manually writing transfer TSQL (or SSIS), that is what I am trying to avoid, but I do understand what you are saying in that the .bak file does not include the metadata.
I guess we have differing ideas of "fun projects". I am a .net developer, so the less SQL I write the better! 🙂
If I am gonna do DBA stuff, then maybe I should get my DBA Cert and get paid more! 🙂
Thanks again
Jason
March 22, 2011 at 12:26 pm
jasona22 (3/22/2011)
Thank you David.I do not know nor really understand SSIS. I can't even spell SSIS <-- note red squiggly
As for manually writing transfer TSQL (or SSIS), that is what I am trying to avoid, but I do understand what you are saying in that the .bak file does not include the metadata.
I guess we have differing ideas of "fun projects". I am a .net developer, so the less SQL I write the better! 🙂
If I am gonna do DBA stuff, then maybe I should get my DBA Cert and get paid more! 🙂
Thanks again
Jason
Smiling. Not sure about the pay thing BUT....
You could write all that in .NET as well. You would certainly have to use some of the classes ( not sure if that is the right term - I am a SQL geek) to do bulk inserting and things of that nature but could be all done within the .NET framework. Should allow you to stay in your comfort zone a good bit anyway. AND, there are many here that could help if you were to get into a bind doing some of that work as well.
Best thing to do is to outline a plan of what you need to do to each table and database. Example as follows;
1. Restore source databases
2. Truncate master repository tables (if that is what you need to do)
3. Bulk insert data from source to master - Loop through all sourced databases
4. etc....
As you work through those steps I think the SQL requirements will become clearer and we can help more on here.
Hope this helps a bit anyway.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply