2

Is there an easier way to restore data to single table in an MSSQL server, rather taking down the database and restoring the entire database?

USD Matt
  • 5,381
  • 15
  • 23
phill
  • 327
  • 3
  • 13
  • 20

4 Answers4

2

You can restore the database from the backup file to another database name on the same server, or different server, then copy the data over.

If the database was Northwind, something like:

RESTORE DATABASE NorthwindTemporary
FROM DISK="D:\Backups\NorthwindBackup.bak"
WITH MOVE 'Northwind_Log' TO 'D:\SQL\NorthwindTemporary.ldf',
     MOVE 'Northwind_Data' TO 'D:\SQL\NorthwindTemporary.mdf'

StackOverflow: How do I restore a single table from a SQL Server 2005 backup?

Community
  • 1
boflynn
  • 661
  • 10
  • 19
2

There is a third party product, LiteSpeed for SQL Server, which offers this capability of object and even row-level recovery.

K. Brian Kelley
  • 9,034
  • 32
  • 33
  • If your running SQL 2005 or earlier lightspeed will also compress your backups very nicely. its a great product – JoshBerke May 01 '09 at 15:04
1

SQL Server cannot do table-level backups or restores, although you can backup and restore files within a database if you want to

You're best bet is probably to restore the entire backup as a temporary database, and then copy the data you need over to the main database.

Tools like RedGate's SQL Data Compare are good for synchronising data.

codeulike
  • 1,008
  • 5
  • 17
  • 29
0

A better option would be to Generate Scripts from the first original database, and choose 1 table, and Script Data. Then execute this script on your database (where you wish to restore just 1 table).

If you don't have access to the original database, then restore the backup to a new database, create the Generate Scripts.. script from there, and then remove the new database.

Priyanka
  • 1
  • 1