Post reply

Backup and Restore data only

  • February 20, 2009 at 1:46 pm

    #201472

    Hi, I am wondering if it is possible to backup table data only so that when I restore, I do not overwrite destination database's stored procedures.

    Thanks,

    WH

  • February 20, 2009 at 2:14 pm

    #947924

    Not using BACKUP and RESTORE. You'd have to export the data to files and then import those files.

  • February 20, 2009 at 6:01 pm

    #948051

    Lynn Pettis (2/20/2009)

    Not using BACKUP and RESTORE. You'd have to export the data to files and then import those files.

    Nah - that's too much work. You'd have to save all of the create statements for the tables, keys, constraints, indexes, foreign keys, etc...

    What you could do is script out all of the code in the destination first, restore the database, then run the scripts to recreate all of the code.

    Now, I wouldn't do either of those - I would use Redgates SQL Compare to create a snapshot of my existing database. Perform the restore then use SQL Compare to compare the restored database to the snapshot and synchronize the differences from the snapshot.

    But, that's just me 🙂

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • February 23, 2009 at 7:35 am

    #948587

    Many thanks to both replies. We used RedGate tool as well. I could not believe that SQL Server does not provide such simple functionality.

  • February 23, 2009 at 8:10 am

    #948616

    You could use Idera's SQL backup tool with object level recovery. Then you can backup the database, and restore just the selected tables. IIRC, it will restore just the data, so if the table does not exist, it will not restore indexes.

  • February 26, 2009 at 3:40 am

    #950490

    Wont the redgate tool internally using a ETL process?

    Using the tool would make it easy for the end user to accomplish the task.

    "Keep Trying"

  • March 5, 2009 at 8:29 am

    #954082

    Thanks for the suggestions.

  • March 5, 2009 at 10:04 am

    #954164

    if we need table data we just restore to a different server with another db name and copy the data over

    if you need to do this on a constant basis then you have management/security issues and you should lock down access to your db

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply