Possible to do a backup/Restore of Tables Only??

  • Hi

    I am wondering if its possible to do a backup of tables only (2008) and a restore of the tables in (2014)?

    Thanks

  • No.

    You could bcp every table out to flat file, script the structures and recreate the DB, but that's a huge amount of work.

    What are you trying to achieve here, and why does a normal SQL backup not work for you.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Not using regular backup/restore inside of SQL, however there are things you can do.

    *Use BCP to put the data out to a flat file, and load it back in again.

    *Use the Export Data wizard to move the data directly (right click the database -> tasks - > export wizard

    *Create an SSIS package to move the data

    *Create a linked server and do a select into.



    Shamless self promotion - read my blog http://sirsql.net

  • I think some third-party utilities, for example LiteSpeed, allow you to do object-level restores. I've never used that functionality myself, so I don't know how well it suits your requirement or how well it works.

    John

  • In order to do this you would have to have your tables on separate file groups (Not on PRIMARY) and perform backups of each file group. You could then restore a filegroup. BUT...

    Yes, always a but.

    You cannot restore the PRIMARY group and see data that was added before the restoral in the other groups. You will have to restore all of the other groups in order to see the data.

    At best this is a kluge way of doing this. If you have multiple tables on a file group, you will have to restore all of the tables on that file group, which could cause you even more headaches.

    Additionally, Red Gate Software has tools available, one of which will allow object level restoral. You can select the table(s) that you wish to restore from a backup file. Personally, I don't like it because it can take a very long time to read the database if you have a large one. I find it easier and faster to just restore the database and apply the T-Logs. But you can download a trial edition which is good for 14 days and play with it.

    Hope this helps.

    Steve Jimmo
    Sr DBA
    “If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

  • Why?

    If you just move the tables, but not the data, what's the point. You could simply script the database. If you move the tables, but not the foreign keys, constraints & indexes, the data is in place, but the behavior of the system is going to be pretty weird when you go to query it. If you don't move the stored procedures & views you're going to have to rewrite all the queries again.

    I just don't see the point of only moving part of a database. What is it that you're trying to achieve?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • If you just move the tables, but not the data, what's the point. You could simply script the database. If you move the tables, but not the foreign keys, constraints & indexes, the data is in place, but the behavior of the system is going to be pretty weird when you go to query it. If you don't move the stored procedures & views you're going to have to rewrite all the queries again.

    I just don't see the point of only moving part of a database. What is it that you're trying to achieve?

    Grant, I totally agree. The headaches are many and the rewards usually low. If you don't restore the T-Logs, you will definitely have issues.

    The only place where I do file dumps is on a datawarehouse that would be horrible to restore. But the file dumps do not occur until after all loads are completed and the data is steady.

    But the person asking the question has options, and regardless I feel should know the options available. Then they can make their own choices.

    Just my .02 🙂

    Steve Jimmo
    Sr DBA
    “If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

  • sjimmo (9/7/2016)


    If you just move the tables, but not the data, what's the point. You could simply script the database. If you move the tables, but not the foreign keys, constraints & indexes, the data is in place, but the behavior of the system is going to be pretty weird when you go to query it. If you don't move the stored procedures & views you're going to have to rewrite all the queries again.

    I just don't see the point of only moving part of a database. What is it that you're trying to achieve?

    Grant, I totally agree. The headaches are many and the rewards usually low. If you don't restore the T-Logs, you will definitely have issues.

    The only place where I do file dumps is on a datawarehouse that would be horrible to restore. But the file dumps do not occur until after all loads are completed and the data is steady.

    But the person asking the question has options, and regardless I feel should know the options available. Then they can make their own choices.

    Just my .02 🙂

    No argument on the options. There are many. I'm just trying to understand what it is that we're trying to achieve. That will more accurately suggest which options are the better ones.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I just moved a bunch of tables from SQL2012 to SQL2016 using the Generate Scripts option from the database context menu in Management Studio. I chose the Schema and Data option in the advanced options. My database was tiny though! 30 tables max and only half had any data in. The rest had between a couple of hundred and a few thousand rows in. Only took a couple of minutes.

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

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