Post reply

How to backup a table with data?

  • November 7, 2011 at 11:28 pm

    #245699

    I want to backup a table with data in SQL Express 2005. How to generate a script with data?

    "Here is a test to find out whether your mission in life is complete. If you're alive, it isn't. "

    Richard Bach

  • November 8, 2011 at 3:02 am

    #1404327

    have a look at the procedure here, it will generate the insert script with data for you.

    http://vyaskn.tripod.com/code.htm#inserts"> http://vyaskn.tripod.com/code.htm#inserts

  • November 8, 2011 at 3:16 am

    #1404341

    another way is the SSMS addin from http://www.ssmstoolspack.com/ also has the ability to generate insert statements from The SSMS GUI;

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 8, 2011 at 3:22 am

    #1404347

    1)select * into new_tablename from existing_tablename (this will create a new table with data same as in existing table)

    2) insert into new_tablename select * from existing_tablename(this will work only if you have created a backup table of existing table having same structure)

  • November 8, 2011 at 4:20 am

    #1404356

    Interesting.

    I don't have time to develop a fully working script, but this will generate SELECT statements of the data from your table.

    Note, it's reliant on the size of VARCHAR(MAX), so big tables won't work.

    DECLARE @yourTableName VARCHAR(100)

    SET @yourTableName = '' --Your table name here

    DECLARE @SQL AS VARCHAR(MAX)

    SELECT @SQL = COALESCE(@SQL + '+'',''+', '') + sql_code

    FROM (SELECT 'QUOTENAME('+name+','''''''')' AS sql_code

    FROM (SELECT name

    FROM sys.columns

    WHERE object_id = OBJECT_ID(@yourTableName)) a ) b

    SELECT @SQL = 'SELECT ''SELECT ''+ ' + @SQL + ' + '' UNION ALL'' FROM ' + @yourTableName

    IF object_id('tempdb..#resultHolder') IS NOT NULL

    BEGIN

    DROP TABLE #resultHolder

    END

    CREATE TABLE #resultHolder (ID INT IDENTITY, code VARCHAR(MAX))

    INSERT INTO #resultHolder

    EXEC (@SQL)

    UPDATE #resultHolder

    SET code = REPLACE(code,' UNION ALL','')

    WHERE ID = (SELECT MAX(ID) FROM #resultHolder)

    SET @SQL = NULL

    SELECT @SQL = COALESCE(@SQL + ' ','') + code

    FROM (SELECT code

    FROM #resultHolder) a

    You'd then use the SELECT statements held in @SQL to insert into your new table.

    Can I just note that there are many better ways of performing back-ups of specific tables.

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • November 9, 2011 at 7:12 pm

    #1405489

    Why don't use old and reliable bcp utility?

    You can export your table into file by something like

    bcp Table out FileName.dat -S server -d database -n -T

    and then import it back changing out to in.

    That's all. Simple, isn't it?

    Andrei.

  • November 9, 2011 at 8:43 pm

    #1405491

    some good ideas above. The key is do you want the table backed up to move to another database, or stored outside of SQL Server or are you trying to copy the table inside SQL Server in case changes to the table need to be rolled back?

  • November 10, 2011 at 7:51 am

    #1405766

    Along those same lines, I want to back up or copy table and data to restore on a different server in a different database. Any good suggestions here? It is also going from Server 2005 to Server 2008.

  • November 10, 2011 at 8:01 am

    #1405777

    dave.chapman (11/10/2011)

    Along those same lines, I want to back up or copy table and data to restore on a different server in a different database. Any good suggestions here? It is also going from Server 2005 to Server 2008.

    just the data, or the script for the table, which might include indexes, foreign keys, default constraints, check constraints? that's one more thing to cosnider, aside from the data itself.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 10, 2011 at 8:04 am

    #1405782

    Yes anything underneath the table.

  • November 10, 2011 at 8:16 am

    #1405802

    dave.chapman (11/10/2011)

    Along those same lines, I want to back up or copy table and data to restore on a different server in a different database. Any good suggestions here? It is also going from Server 2005 to Server 2008.

    Import wizard is probably the easiest way.. you can also include contraints and keys in the options

  • November 10, 2011 at 8:26 am

    #1405819

    Dave

    This is what I would do to copy table from SQL 2005 box to SQL 2008 box

    1) Script out the table in SQL 2005 and create the table on SQL 2008

    2) Copy the data using Import/Export Wizard in SQL Server or use bcp

    3) Script out the indexes as scripting the table will not script the indexes and create them on SQL 2008 box

  • November 10, 2011 at 8:30 am

    #1405826

    Thanks. Believe that will work fine.

Viewing 13 posts - 1 through 12 (of 12 total)

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