SQL Server 2008 R2 Restore a back up to a new database

Question

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?

Answer 1

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.

Answer 2

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
2. Filter by tables, so you don't waste time on other data
3. You can use the filter to specify specific records

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

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')

Answer 3

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.