How to take backup of Single table and how to restore?

  • august.tomelleri (7/17/2013)


    As long as your table is not too large, you can use the generate scripts feature to create a copy of table along with all the data. This will create a single script to create a new table along with existing data. You'll probably want to rename the table once you've created your script.

    1. Right click on DB.

    2. Select Tasks.

    3. Select Generate Scripts

    4. Run through the wizard.

    5. There is an option on the properties to script with data.

    yes, large tables present a problem most of the time, but that is why I mentioned the tools from "SMSS Tool Pack " they integrate into MS Server Management Studio nicely

    and when you use "generate insert statement" call will break the scripts into chunks of 80 records or so

    each with in it's own transaction.

    here is an example of such script:

    <Begin>

    USE [MyDevDb];

    SET NOCOUNT ON;

    SET XACT_ABORT ON;

    GO

    SET IDENTITY_INSERT [dbo].[Parts] ON;

    BEGIN TRANSACTION;

    BEGIN TRANSACTION;

    INSERT INTO [dbo].[Parts](columList)

    SELECT 1, 17, 49, N'"A1', NULL, 0, 1, '2013-02-13 00:00:00.000' UNION ALL

    SELECT 2, 17, 49, N'A6', NULL, 0, 1, '2013-02-13 00:00:00.000' UNION ALL

    SELECT 3, 17, 49, N'B1', NULL, 1, 1, '2013-02-13 00:00:00.000' UNION ALL

    SELECT 4, 17, 49, N'B9', NULL, 1, 1, '2013-02-13 00:00:00.000' UNION ALL

    COMMIT;

    RAISERROR (N'[dbo].[Parts]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;

    GO

    BEGIN TRANSACTION;

    INSERT INTO [dbo].[Parts](columList)

    SELECT 83, 34, 6, N'10CDFG89', NULL, 1, 1, '2013-02-13 00:00:00.000' UNION ALL

    SELECT 84, 19, 6, N'10455689H', NULL, 1, 1, '2013-02-13 00:00:00.000' UNION ALL

    SELECT 85, 31, 44, N'10dEwFC0A5', NULL, 1, 1, '2013-02-13 00:00:00.000' UNION ALL

    SELECT 86, 24, 53, N'10afvg11', NULL, 1, 1, '2013-02-13 00:00:00.000' UNION ALL

    COMMIT;

    RAISERROR (N'[dbo].[Parts]: Insert Batch: 2.....Done!', 10, 1) WITH NOWAIT;

    GO

    *

    more batches here

    *

    BEGIN TRANSACTION;

    INSERT INTO [dbo].[Parts](ID, List Of the columns to insert)

    SELECT 19511, 0, NULL, N'XXXX41088LZZZ', NULL, 1, 1, '2013-05-29 00:00:00.000' UNION ALL

    SELECT 19512, 0, NULL, N'XXXX41088PLZZZ', NULL, 1, 1, '2013-05-29 00:00:00.000' UNION ALL

    SELECT 19513, 0, NULL, N'XXXX4329MM110', NULL, 1, 1, '2013-05-29 00:00:00.000' UNION ALL

    SELECT 19514, 0, NULL, N'XXXX58120', NULL, 1, 1, '2013-05-29 00:00:00.000'

    COMMIT;

    RAISERROR (N'[dbo].[Parts]: Insert Batch: 392.....Done!', 10, 1) WITH NOWAIT;

    GO

    SET IDENTITY_INSERT [dbo].[Parts] OFF;

    </End>

  • Think export the table in xls then import it .........is the best solution

  • udayroy15 (7/19/2013)


    Think export the table in xls then import it .........is the best solution

    Are you crazy?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (7/19/2013)


    udayroy15 (7/19/2013)


    Think export the table in xls then import it .........is the best solution

    Are you crazy?

    thanks for making me laugh this morning...:-D

  • Luis Cazares (7/19/2013)


    udayroy15 (7/19/2013)


    Think export the table in xls then import it .........is the best solution

    Are you crazy?

    BWAAA-HAAA!!!! (Sorry, did that come out loud?)

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 16 through 19 (of 19 total)

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