Restore a table from a backup set

Discussion in 'Databases' started by imchaz, Nov 18, 2009.

Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.
  1. I have a backup set using the backup tool that you provide. But today, I was testing out a script and I accidently overwrote table records.

    How can I use my backup.zip file to restore a table? I dont want to restore the whole database.

    Thanks.
     
  2. dmitri

    dmitri DiscountASP.NET Staff

    You can attach this backup to an SQL Server on different machine, script the table, save T-SQL code of your scripted table, and then run this script on the SQL Server instance where you want to restore the table.
     
  3. I have it loaded on local SQL Server Express 2005 instance. I see my table that I need. But I am unsure how to get the particular table in TSQL script form to use all the records in it to my SQL Server hosted here.
     
  4. Nevermind. I figured it out. I used a DTS utility to import the table records.

    Thanks.
     
  5. dmitri

    dmitri DiscountASP.NET Staff

    Hello. This is how it can be done in Management Studio 2008:

    * In Object Explorer right-click on the database folder. From the drop-down menu select Task / Generate Script.

    * Click Next. Select the database and click Next.

    * Set the following options to True: "Script Data" (since you want your records to be included) , "Script Drop" (since you want to drop the existing table in the target database). Set "Script for Server Version" to "SQL Server 2005." Leave other option as they are unless your table has other depended ojects such as indexes, triggers, etc. Please refer to the Books Online for the detail on each option.

    * Click Next, select Tables check box, and click Next.

    * Now you can select tables you want to script and click Next.

    * You have the options how you want your script to be generated: to file, to clipboard, or to new query window. Select your desired option and hit Next and then Finish.

    * Now you can run this script inside your target database. Do not forget to change USE [YourDatabase] statement to use a right database
     
  6. dmitri

    dmitri DiscountASP.NET Staff

    I am glad you found the solution. I suggest to get familiar with scripting database objects as it is a good practice to keep scripts for your objects as a backup solution. It is a must practice when you objects are encrypted since you cannot view the code of encrypted stuff.
     
  7. I will save this for future use. Thanks again.
     
Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.

Share This Page