Restore a single table in MS SQL 2005
Page 1 of 1
20100812
Restore a single table in MS SQL 2005
For my work I had to restore one table from a backup. Now SQL doesn't have this function anymore since version 6.5 or so. But what if you can't restore the full database for whatever reason. Well here is how I did it, might not be the best solution, but it worked for me.
First I took the latest complete backup and restored it into a temporary directory, this way the whole database is restored including the table you need. Now first check if the table is indeed the one you need and all data is available, would be a shame to do it for nothing.
Next rename or delete the old table, just to be sure.
Next we start a new query and fill in SELECT * INTO OriginalDB.dbo.Table FROM RestoredDB.dbo.Table and run the query. In my case it was the following.
Now all data and the schema has been copied, however you still miss indexes, foreign keys, statistics etc. More on this later.
First I took the latest complete backup and restored it into a temporary directory, this way the whole database is restored including the table you need. Now first check if the table is indeed the one you need and all data is available, would be a shame to do it for nothing.
Next rename or delete the old table, just to be sure.
Next we start a new query and fill in SELECT * INTO OriginalDB.dbo.Table FROM RestoredDB.dbo.Table and run the query. In my case it was the following.
- Code:
SELECT * INTO DOQument.dbo.PSETPOS FROM DOQ2.dbo.PSETPOS
Now all data and the schema has been copied, however you still miss indexes, foreign keys, statistics etc. More on this later.
thierry- Posts : 2238
Join date : 2009-08-18
Age : 42
Location : Zoetermeer
Similar topics
» Use a JOIN in an UPDATE statement MS SQL 2005
» Select data from multiple tables and join them in MS SQL 2005
» Select data from multiple tables and join them in MS SQL 2005
Permissions in this forum:
You cannot reply to topics in this forum