3

I have a backup of a SQL Server database that I would like to restore. The issue is that the server that I would restore it to already have a database with the same name, layout etc, which contains data.

Is there some easy way to merge data form the backup into the existing database so that only missing rows are inserted?

4
  • 1
    Take a look at red-gate.com/products/sql-development/sql-data-compare in Pro edition.
    – Kuba Wyrostek
    Commented Jan 26, 2016 at 12:23
  • 1
    You can specify a different name for the database when you restore it - you can then do cross-database queries to find missing data. If you have Visual Studio you can use the data compare in there too. (RedGate DataCompare is very easy to use but is pricey).
    – Rikalous
    Commented Jan 26, 2016 at 13:44
  • One strategy is to create a new UUID (called uniqueidentifier in MS SQL) and use that as the index. By using UUIDs, the inevitable database merge goes a lot better. Good article on CodingHorror about this: blog.codinghorror.com/primary-keys-ids-versus-guids Commented Nov 24, 2016 at 18:51
  • How big are the databases? Commented Apr 5, 2017 at 19:13

3 Answers 3

1

For your case i would recommend dbForge Server Data Compare for SQL Server from Devart. It looks very similar to the Redgate SQL Data Compare mentioned by Moisés González.

It allows you to compare your database with the backup file directly or with a second database.

It shows you the differences between the databases:

  • only in backup / source database
  • only in target database
  • diffent rows / fields

You can select the data to restore or generate scripts for inserting in your current database.

I use this software for our customers to restore deleted rows and even a restore of 5 million rows and update of another 5 million rows have been done very quickly. It works very well and can execute also very large repair scripts (the largest one i have generated was about 10 gig).

There is a 30 day trial available which may fit your needs. So you can give it a try without any risk.

0

You could also create a different database and load it all in there first then use the SQL Server import export utility and append all the rows to the original DB then use this query to delete the duplicates.

WITH CTE AS(SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7],
            RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1)
FROM dbo.Table1
)
DELETE FROM CTE WHERE RN > 1

Disable Constraints if you have primary and foreign key constraints

-- Disable all table constraints
 ALTER TABLE YourTableName NOCHECK CONSTRAINT ALL

-- Enable all table constraints
 ALTER TABLE YourTableName CHECK CONSTRAINT ALL

-- ----------

-- Disable single constraint
 ALTER TABLE YourTableName NOCHECK CONSTRAINT YourConstraint

-- Enable single constraint
 ALTER TABLE YourTableName CHECK CONSTRAINT YourConstraint

-- ----------

-- Disable all constraints for database
 EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- Enable all constraints for database
 EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
2
  • That won't work for any table with a PRIMARY KEY or a UNIQUE INDEX. Most tables in a decently designed database have either one of those.
    – TT.
    Commented Jan 27, 2016 at 18:25
  • If you have constraints in place then you can run a script to temprarily disable constraints. Please see edit. Just disable then enable constraints.
    – Wes Palmer
    Commented Jan 28, 2016 at 14:07
0

My suggestion here is that you can restore the database in the same server with another name and later use a compare tool like Redgate SQL Data Compare or the Idera Comparison Toolset (both fully functional during the 14 days trial period) to find the differences between tables in different databases and generate a script with the insert-updates-deletes needed.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.