Sql-server – Help with best practice of merging databases

backupmergerestoresql-server-2005

Bad news, our website server (Windows 2003) crashed because of dead RAID controller.

Luckily few hours later our backup server was up and website was live again.

Hopefully tomorrow our original server will be fixed and I'm looking for the best practice to merge our data (SQL Server 2005).

This is the situation right now:

Original server has backed up data till 16th 01:30AM (night.bak)
Original server has data which is not backed up till 17th 00:30AM
so basically there is 23 hours missing on the night.bak

I restored the night.bak to the temporary server and since 17th 03:00 new data is being added to the temporary server.

Tomorrow I wish to take data from the temporary server (17th 03:00 till 19th) and put it back on the original server.

I believe I can't make differential backup on the temporary server and restore it on the original server because last backup on original server has timestamp of 16th 01:30AM but I don't really know so I'm asking here.

My main concern is to preserve data of joined tables that share index keys.

I don't know how to reply to the answer so I'm replying here :

I'm testing Red-Gate's SQL Data Compare (which is by the way 14 days trial and not 30) and it seems good to add / update / delete rows very easily.

But this is the problem:

old database has 120 records on tableA (primary key 1-120) when I left it
new temp database doesn't have all 120 records because only 100 records were backed up so, it has only 100 records (primary key 1-100)

Since we continue using the temp database it now has 140 records (primary key 1-100 from original database and 101-140 from temp database).

The Compare would want to insert 40 new records to the tableA but it cannot use 101-120 key because it's already exists on the original database so I guess it will try to update and destroy them.

And anyway, it cannot insert with correct keys because, for example, I have table tbl_users (code(index), firstname, email) and table tbl_priceoffers (code(index), usercode(from tbl_users), price).

Red-Gate generate script to insert tbl_priceoffers before tbl_users
but even if it was the correct order (I can edit), it cannot insert tbl_priceoffers row without having @@identity from the recently inserted tbl_users row.

Best Answer

Your best bet is to use Redgate's data compare to compare and insert data in the database your application is pointing to.

I have used this method in similiar situation and trust me ... this tool is a life saver.

You can dowload a 30 day full trial version and then purchase it if it suits your needs.

Related Solutions

Sql-server – SQL Select taking too much time to execute

I think in a million records query, you have to avoid things like OUTER JOINS. I suggest you use UNION ALL Instead of LEFT JOIN. As long as I think CROSS APPLY is more efficient than sub-query in the select clause I will modify the query written by Conard Frix, which I think is correct.

now: when I started to modify your query I noticed that you have a WHERE clause saying: JoinedTable.WhereColumn IN (1, 3). in this case, if the field is null the condition will become false. then why are you using LEFT JOIN while you are filtering null valued rows? just replace LEFT JOIN With INNER JOIN, I guarantee that it will become faster.

about INDEX:

please note that when you have an index on a table, say

table1(a int, b nvarchar)

and your index is :

nonclustered index ix1 on table1(a)

and you want to do something like this:

select a,b from table1
where a < 10

in your index you have not included the column b so what happens?

if sql-server uses your index, it will have to search in the index, called "Index Seek" and then refer to main table to get column b, called "Look Up". This procedure might take much longer than scanning the table itself: "Table Scan".

but based on the statistics that sql-server has, in such situations, it might not use your index at all.

so first of all check the Execution Plan to see if the index is used at all.

if yes or no both, alter your index to include all columns that you are selecting. say like:

nonclustered index ix1 on table1(a) include(b)

in this case Look Up will not be needed, and your query will execute so much faster.

Sql-server – INSERT performance difference between temporary tables and table variable

The obvious difference between the two plans is that the fast one is parallel and the slower one serial.

This is one of the limitations of plans that insert into table variables. As mentioned in the comments (and it seems as though it had the desired effect) you could try doing

INSERT INTO @DATA ( ... ) 
EXEC('SELECT .. FROM ...')

to see if that gets around the limitation.

Best Answer

Related Solutions

Sql-server – SQL Select taking too much time to execute

Sql-server – INSERT performance difference between temporary tables and table variable

Related Question