I have a full back up of a database, I want to update some records in some table in the current database from its old backup without affecting other records.

What is the best way to do this?

Basically I want to get some records from the old backup update old values to the current database without doing a restore, what are my options?


You cannot access the records in a database backup without restoring it to a database. You can however restore it to a different database from the one that it was backed up from.

The following link illustrates how to do this using Transact-SQL: http://msdn.microsoft.com/en-us/library/ms190447(v=sql.105).aspx

and in Management studio: http://msdn.microsoft.com/en-us/library/ms177429(v=sql.105).aspx

Then once you have the new database you can write SQL to join the two instances (obviously security considerations will need to be taken into account first). E.g

SELECT Table1.*, Table1_Backup.* FROM Table1 
INNER JOIN DB_Restore.dbo.Table1 AS Table1_Backup ON Table1.ID = Table1_Backup.ID

or similar.

As @Flem mentions you could then use the MERGE statement to merge the two datasets, however you want.

| improve this answer | |

You can use a third party tool for this that can read database backups and compare them to a live database (or to other database backups). Try ApexSQL Data Diff.

  1. Compare a live database to a database backup enter image description here
  2. Filter by tables, so you don't waste time on other data
  3. You can use the filter to specify specific records

enter image description here

enter image description here

If this is not possible, then compare all records and tables, and once you see them in the grid, manually select the records you want to script

enter image description here

4 Generate the script to modify the records

You'll get the script such as this

DELETE FROM [HumanResources].[Shift] WHERE [ShiftID]=6

UPDATE [HumanResources].[Shift] SET [ModifiedDate]='20020601 00:00:00.000' WHERE [ShiftID]=1

INSERT INTO [Sales].[ShoppingCartItem] ([ShoppingCartItemID], [ShoppingCartID], [Quantity], [ProductID], [DateCreated], [ModifiedDate]) VALUES (2, N'14951', 3, 862, '20071211 17:54:07.603', '20071211 17:54:07.603')
| improve this answer | |

I think the MERGE statement is your best option. To use this, you will need to be able to query both databases in one query (i.e. they are on the same SQL server instance or you can setup linked servers.

| improve this answer | |
  • but i am stuck with adding the database to the same sql instance, its not working, i created a new database tried to load the backup to it and no luck, how would you do it normally? – user1570048 Dec 28 '12 at 11:13
  • @user1570048. You don't have to create a DB then restore over it. You can restore to a new DB. What error do you get when restoring? – Paul Fleming Dec 28 '12 at 11:18
  • nvm it worked, i hate those security things :D – user1570048 Dec 28 '12 at 11:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy