| Author |
Topic |
|
mikeb55
Starting Member
11 Posts |
 Posted - 2009-02-02 : 11:17:52
|
| Hello Everyone,I was hoping someone might be able to give me a few ideas on a problem that I have. I get a .bak file every few hours with a number of tables in it. I have the database set up on my server but I need to know what's different between each table and then decide if I want to update my database or not. The main problem is that the .bak file that I get doesn't have an PKs in any of the tables. Neither does the database on my server (I didn't set this up). I tried to use TableDIFF but it says I need PKs on my tables. Is there any other way to do this? The .bak file that I get has a snapshot of a database and every time I get this file, the data changes. I need to keep all of the changes, not just the new snapshot. Replication in this case wasn't possible, and only .bak files can be sent. I would appreciate any help. Sincerely,MB |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-02-02 : 12:13:50
|
| I don't suppose there are date fields you could key off? What's the rowcount in your biggest table?Mike"oh, that monkey is going to pay" |
 |
|
|
mikeb55
Starting Member
11 Posts |
Posted - 2009-02-02 : 12:18:47
|
My biggest table has about 50,000 records in it. There are some date fields, and I haven't thought of using them in my pk, but that's not a bad idea. Anything else that I could use?quote:
Originally posted by mfemenel
I don't suppose there are date fields you could key off? What's the rowcount in your biggest table?Mike"oh, that monkey is going to pay"
Sincerely,MB |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-02 : 13:25:50
|
| Is .bak file Full backup or other backup? |
|
|
|
mikeb55
Starting Member
11 Posts |
Posted - 2009-02-02 : 13:52:48
|
| The .bak is a full backup. Would it matter if it was another type of backup? Maybe I could ask for a different type of backup file.Sincerely,MB |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-02 : 17:04:42
|
| You can try Merge Replication. It doesn't need PK key. |
|
|
|
mikeb55
Starting Member
11 Posts |
Posted - 2009-02-03 : 07:02:46
|
Hmm...I only have the .bak file. How would I use Merge Replication? Not too familiar with it. For example, if I receive File1.bak at 10 AM and File2.bak at 2 PM, and I restore the File1.bak to my database that lets say I would name File1. If File2.bak doesn't contain some of the records from File1.bak, would they be deleted in Merge Replication? I would want only the additions or updates to be updated in my database. THe .bak files are snapshots of certain active orders and if an order is not active it will not be included in the 2nd .bak file. Would the missing records be deleted from my database if I use Merge Replication? I really appreciate everyone's help on this. It's a very important project that i'm working on.quote:
Originally posted by sodeep
You can try Merge Replication. It doesn't need PK key.
Sincerely,MB |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-03 : 09:23:24
|
quote:
Originally posted by mikeb55
Hmm...I only have the .bak file. How would I use Merge Replication? Not too familiar with it. The question is why you are receiving .bak files and restoring everytime. Can't you just implement Replication from Source server to Destination Server or Within instance?.For example, if I receive File1.bak at 10 AM and File2.bak at 2 PM, and I restore the File1.bak to my database that lets say I would name File1. If File2.bak doesn't contain some of the records from File1.bak, would they be deleted in Merge Replication? I would want only the additions or updates to be updated in my database. Yes, you can in Replication.You have to choose DONOT REPLICATE DELETE in Article Properties.THe .bak files are snapshots of certain active orders and if an order is not active it will not be included in the 2nd .bak file. Would the missing records be deleted from my database if I use Merge Replication? I really appreciate everyone's help on this. It's a very important project that i'm working on.quote:
Originally posted by sodeep
You can try Merge Replication. It doesn't need PK key.
Sincerely,MB
|
|
|
|
mikeb55
Starting Member
11 Posts |
Posted - 2009-02-03 : 09:35:36
|
| Unfortunately, we can't setup replication. The vendor will only give us .bak files. Makes no sense to me, but I don't make the decisions. Basically, I have to work with what we have and i need to somehow make it work. Thanks for the tip on the Delete. I'll make sure I choose that. So for now i'm going to do this. I set up 2 databases on my server. One is the master db, with the first file that i'll ever receive. The 2nd .bak file that i'll get, i'll restore it to a 2nd db. I will set up replication between my two databases. When I get subsequent .bak files, i'll restore them to the 2nd db that I created. Replication hopefully will replicate the data to my master database, and if I choose DONOT REPLICATE DELETE in Article Properties, then deletes won't replicate. Hopefully this will allow me to have a master database with all of the changes that I need. What do you guys think? Can this work? Sincerely,MB |
|
|
|
mikeb55
Starting Member
11 Posts |
Posted - 2009-02-03 : 10:55:12
|
This seems to somewhat work, except for the deletes part.I don't see DONOT REPLICATE DELETE in Article Properties. There are no options for UPdate, Insert or Delete. I'm using a Merge Replication. I tried transactional, but for some reason I couldn't set it up. Something about my db. I'm going to attempt to deny delete on all of my tables and see what happens. quote:
Originally posted by sodeep
quote:
Originally posted by mikeb55
Hmm...I only have the .bak file. How would I use Merge Replication? Not too familiar with it. The question is why you are receiving .bak files and restoring everytime. Can't you just implement Replication from Source server to Destination Server or Within instance?.For example, if I receive File1.bak at 10 AM and File2.bak at 2 PM, and I restore the File1.bak to my database that lets say I would name File1. If File2.bak doesn't contain some of the records from File1.bak, would they be deleted in Merge Replication? I would want only the additions or updates to be updated in my database. Yes, you can in Replication.You have to choose DONOT REPLICATE DELETE in Article Properties.THe .bak files are snapshots of certain active orders and if an order is not active it will not be included in the 2nd .bak file. Would the missing records be deleted from my database if I use Merge Replication? I really appreciate everyone's help on this. It's a very important project that i'm working on.quote:
Originally posted by sodeep
You can try Merge Replication. It doesn't need PK key.
Sincerely,MB
Sincerely,MB |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-03 : 23:34:38
|
This makes no sense to me at all  . In your scenerio,I would create 1 job with several steps like:1)Fetch bak file from vendor and restore to staging DB.2)Move data from staging to consistent DB3)Create store procedure to compare records from staging DB and Consistent DB. If records exist - insert otherwise update.Replication is not of use to you because you always have refresh your source DB. Or You can incorporate all steps I have mentioned within SSIS packages and run with jobs. |
|
|
|
mikeb55
Starting Member
11 Posts |
Posted - 2009-02-04 : 06:57:43
|
| Yeah, i agree. I tried it and didn't work. When I refreshed the db, it got all messed up. The main problem is that i can't seem to be able to compare the data between the new and old .bak files. Even with a staging DB and a master db, I can't compare the records. Now, i'm not saying it can't be done, i'm only saying that I don't know of a way.I tried to compare two of my tables, the Orders table from both dbs. This is the query that I usedSelect * from MasterDb.Orders Except Select * from StagingDb.Orders This query didn't seem to work. It returned all records from my MasterDb.orders table, even though I know they repeat in the StagingDb.Orders table. I tried the same thing with two other tables in a TestDb that I created, and it worked just fine. I've tried other ideas too, but can't seem to be able to get the records that are different. Maybe the reason is that both tables don't have a primary key. Each table has around 40 fields and I can ony use 15 of them as my pk. Problem is that when I think I found the right combo for my pk, it turnes out some records have a NULL and the pk can't contain any NULLs. If you have any suggestions on how to compare the two tables, it would be great. Thank you.Sincerely,MB |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-04 : 09:27:50
|
| http://www.sqlis.com/post/Get-all-from-Table-A-that-isnt-in-Table-B.aspx |
|
|
|
mikeb55
Starting Member
11 Posts |
Posted - 2009-02-04 : 09:37:43
|
It seems that you need to join the two tables on their pK. I've tried to join the two tables on every single column and still no luck. quote:
Originally posted by sodeep
http://www.sqlis.com/post/Get-all-from-Table-A-that-isnt-in-Table-B.aspx
Sincerely,MB |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-04 : 09:48:50
|
You can use like:Select Identity(int,1,1) as ID,* into staging tablefrom your table. and then do comparision. |
|
|
|
mikeb55
Starting Member
11 Posts |
Posted - 2009-02-04 : 10:02:08
|
I tried this and I can see the difference between my two tables now. Weird because I manually entered a pk (identity) on both of my tables, but it wasn't coming up with any results when I used the except operator. Only one problem, when i restore my database from the next set of .bak files that i get, the pk will be erased. I can do the same thing and give it a pk by copying the data in a new table, but wouldn't my results be incorrect when I try to compare it with the original set of data? So if my 3rd .bak file contains lets say all of the records from the 2nd file, minus only one record, which happens to be the 10th record, then by copying the data into a new table, my 10th record now is actually my 11th record. Since the pk is based on this autonumber, the results would be incorrect when I do a compare. Am I right or just confused? quote:
Originally posted by sodeep
You can use like:Select Identity(int,1,1) as ID,* into staging tablefrom your table. and then do comparision.
Sincerely,MB |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-04 : 11:07:50
|
quote:
Originally posted by mikeb55
I tried this and I can see the difference between my two tables now. Weird because I manually entered a pk (identity) on both of my tables, but it wasn't coming up with any results when I used the except operator. Only one problem, when i restore my database from the next set of .bak files that i get, the pk will be erased. I can do the same thing and give it a pk by copying the data in a new table, but wouldn't my results be incorrect when I try to compare it with the original set of data? So if my 3rd .bak file contains lets say all of the records from the 2nd file, minus only one record, which happens to be the 10th record, then by copying the data into a new table, my 10th record now is actually my 11th record. Since the pk is based on this autonumber, the results would be incorrect when I do a compare. Am I right or just confused? quote:
Originally posted by sodeep
You can use like:Select Identity(int,1,1) as ID,* into staging tablefrom your table. and then do comparision.
Sincerely,MB
You are absolutely correct. |
|
|
|
|
.bak files instead of replication