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

  • Hi...

    How to take backup of Single table and how to restore? is there any query like database backup?

    shivakumar...

  • I don't that there is something as single table backup. But you can always dump data into another table using SELECT INTO..

    If you want to move data between instances you can always dump data into a csv file(Using Export Wizard) and bulk load it back into your target database using OPENROWSET.

  • Another solution would be to use bcp that will allow you to export tables to text files or import these text files as well.

    If you like graphic interfaces you could use SSIS as well (basically the back end of Import/Export wizard).

    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
  • There was such a feature in SQL Server 6.x. It was removed, because it was really a bad feature which permitted users to screw up their databases big time - I've seen it happen.

    Anyway, there were some suggestions, but it helps if you tell us what you really want to achieve.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Although it's not technically a backup of a single table, you could move the clustered index (which is really the table itself with a B-Tree in the rafters) and the other indexes to a separate Filegroup and then backup and restore that Filegroup.

    --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)

  • Jeff Moden (2013-07-14)


    Although it's not technically a backup of a single table, you could move the clustered index (which is really the table itself with a B-Tree in the rafters) and the other indexes to a separate Filegroup and then backup and restore that Filegroup.

    But note in this case that you cannot use this to copy a single table from one database to another. Nor can you restore the filegroup to get back an earlier version of the table, while keeping the database at the rest of the current version. SQL Server is very careful to make sure that you have a transactionally consistent database.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • gaddamshiva (7/13/2013)


    Hi...

    How to take backup of Single table and how to restore? is there any query like database backup?

    shivakumar...

    if you want to just copy the data then use

    select * into A_New_Table from The_Old_Table

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • gaddamshiva (7/13/2013)


    Hi...

    How to take backup of Single table and how to restore? is there any query like database backup?

    shivakumar...

    This is a procedure I've been working on...it's totally experimental so use at your own risk. But it will copy a table with data, keys, indexes, etc, as options. Some usage examples are included in the code.

    If it works for you (or for anyone else who wants to try it) please let me know. Any feedback or suggestions will be appreciated. At some point I hope to have this published to the scripts section for general dissemination.

    CREATE PROCEDURE [dbo].[UTIL_CopyTable]

    @SourceCatalogName SYSNAME

    ,@SourceSchemaName SYSNAME

    ,@SourceTablename SYSNAME

    ,@TargetCatalog SYSNAME

    ,@TargetSchema SYSNAME

    ,@TargetTable SYSNAME

    ,@isCreatePK BIT

    ,@isCreateIndexes BIT

    ,@isCreateDefaults BIT

    ,@isPopulate BIT

    AS

    BEGIN

    SET NOCOUNT ON

    /*

    EXEC dbo.UTIL_CopyTable

    @SourceCatalogName = 'LocalTestDB'

    ,@SourceSchemaName = 'dbo'

    ,@SourceTablename = 'Tabs'

    ,@TargetCatalog = 'tempdb'

    ,@TargetSchema = ''

    ,@TargetTable = '#DynamicTempTable'

    ,@isCreatePK = 1

    ,@isCreateIndexes = 1

    ,@isCreateDefaults = 1

    ,@isPopulate = 1

    EXEC dbo.UTIL_CopyTable

    @SourceCatalogName = 'LocalTestDB'

    ,@SourceSchemaName = 'dbo'

    ,@SourceTablename = 'Tabs'

    ,@TargetCatalog = 'LocalTestDB'

    ,@TargetSchema = 'dbo'

    ,@TargetTable = '[!DynamicTempTable]'

    ,@isCreatePK = 1

    ,@isCreateIndexes = 1

    ,@isCreateDefaults = 1

    ,@isPopulate = 1

    EXEC dbo.UTIL_CopyTable

    @SourceCatalogName = 'LocalTestDB'

    ,@SourceSchemaName = 'dbo'

    ,@SourceTablename = 'Tabs'

    ,@TargetCatalog = 'LocalTestDB'

    ,@TargetSchema = 'dbo'

    ,@TargetTable = 'AAADynamicTempTable'

    ,@isCreatePK = 1

    ,@isCreateIndexes = 1

    ,@isCreateDefaults = 1

    ,@isPopulate = 1

    */

    DECLARE

    @strSQL NVARCHAR(MAX)

    ,@strColumns NVARCHAR(MAX)

    ,@strCreateIndex NVARCHAR(4000)

    ,@IndexUniqueID NVARCHAR(50)

    ,@index_column_name NVARCHAR(255)

    ,@type_desc NVARCHAR(50)

    ,@is_primary_key BIT

    ,@is_descending BIT

    ,@is_included_column BIT

    ,@suffix NVARCHAR(60)

    ,@SourceFullPath SYSNAME

    ,@TargetFullPath SYSNAME

    ,@ROW_ID INT

    ,@TABLE_CATALOG NVARCHAR(50)

    ,@TABLE_SCHEMA NVARCHAR(50)

    ,@TABLE_NAME NVARCHAR(50)

    ,@COLUMN_NAME NVARCHAR(50)

    ,@ORDINAL_POSITION INT

    ,@IS_IDENTITY BIT

    ,@IDENTITY_SEED INT

    ,@IDENTITY_INCREMENT INT

    ,@IDENTITY_CURRENT INT

    ,@DATA_TYPE VARCHAR(50)

    ,@CHARACTER_MAXIMUM_LENGTH INT

    ,@NUMERIC_PRECISION INT

    ,@NUMERIC_PRECISION_RADIX INT

    ,@NUMERIC_SCALE INT

    ,@DATETIME_PRECISION INT

    ,@COLUMN_DEFAULT VARCHAR(255)

    ,@IS_NULLABLE VARCHAR(3) -- YES/NO

    ,@ID INT

    /* Unable to use #TempTable with dynamic SQL */

    IF @TargetCatalog = 'tempdb'

    SET @TargetTable = '#'+@TargetTable

    SET @SourceFullPath =

    CAST(@SourceCatalogName AS NVARCHAR(50))+'.'+

    +CAST(@SourceSchemaName AS NVARCHAR(50))+'.'+

    +CAST(@SourceTablename AS NVARCHAR(50))

    SET @TargetFullPath =

    CAST(@TargetCatalog AS NVARCHAR(50))+'.'+

    +CAST(@TargetSchema AS NVARCHAR(50))+'.'+

    +CAST(@TargetTable AS NVARCHAR(50))

    IF OBJECT_ID('tempdb..#SpaceUsed') IS NOT NULL

    DROP TABLE #SpaceUsed

    CREATE TABLE #SpaceUsed (

    [name] VARCHAR(50) NULL,

    [rows] INT NOT NULL,

    [reserved] VARCHAR(50) NULL,

    [data] VARCHAR(50) NULL,

    [index_size] VARCHAR(50) NULL,

    [unused] VARCHAR(50) NULL)

    INSERT INTO #SpaceUsed

    EXEC sp_spaceused @SourceFullPath

    IF OBJECT_ID('tempdb..#Tally') IS NOT NULL

    DROP TABLE #Tally

    CREATE TABLE #Tally (

    [N] INT NOT NULL,

    PRIMARY KEY ([N]))

    ;WITH E1(N)

    AS (

    SELECT

    1

    UNION ALL

    SELECT

    1

    UNION ALL

    SELECT

    1

    UNION ALL

    SELECT

    1

    UNION ALL

    SELECT

    1

    UNION ALL

    SELECT

    1

    UNION ALL

    SELECT

    1

    UNION ALL

    SELECT

    1

    UNION ALL

    SELECT

    1

    UNION ALL

    SELECT

    1

    ), --10E+1 or 10 rows

    E2(N)

    AS (

    SELECT

    1

    FROM

    E1 a

    ,E1 b

    ), --10E+2 or 100 rows

    E4(N)

    AS (

    SELECT

    1

    FROM

    E2 a

    ,E2 b

    ), --10E+4 or 10,000 rows max

    cteSysIndex(idxCount)

    AS (

    SELECT TOP(1)

    ISNULL(NULLIF(MAX(index_id) OVER (PARTITION BY OBJECT_ID),0),1) AS idxCount

    FROM

    sys.indexes

    WHERE

    object_id = OBJECT_ID(@SourceFullPath)

    ),

    cteSysCols(colCount)

    AS (

    SELECT TOP(1)

    ISNULL(NULLIF(MAX(ORDINAL_POSITION) OVER (PARTITION BY TABLE_NAME),0),1) AS colCount

    FROM

    INFORMATION_SCHEMA.COLUMNS

    WHERE

    TABLE_CATALOG = @SourceCatalogName

    AND TABLE_SCHEMA = @SourceSchemaName

    AND TABLE_NAME = @SourceTablename

    ),

    cteTableRows(tblRowCount)

    AS (

    SELECT

    ISNULL(NULLIF([rows],0),1) AS tblRowCount

    FROM

    #SpaceUsed

    ),

    cteTally(N)

    AS (

    SELECT TOP (

    SELECT TOP(1)

    i.idxCount+c.colCount+r.tblRowCount+1

    AS N

    FROM

    cteSysIndex i

    OUTER APPLY

    cteSysCols c

    OUTER APPLY

    cteTableRows r

    )

    ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM E4

    )

    INSERT INTO #Tally

    SELECT N FROM cteTally

    --SELECT * FROM #SpaceUsed

    --SELECT * FROM #Tally

    --RETURN

    IF OBJECT_ID('tempdb..#MetaData') IS NOT NULL

    DROP TABLE #MetaData

    CREATE TABLE #MetaData (

    [ID] INT IDENTITY(1,1) NOT NULL,

    [ROW_ID] INT NULL,

    [TABLE_CATALOG] NVARCHAR(50) NULL,

    [TABLE_SCHEMA] NVARCHAR(50) NULL,

    [TABLE_NAME] NVARCHAR(50) NULL,

    [COLUMN_NAME] NVARCHAR(50) NULL,

    [ORDINAL_POSITION] INT NULL,

    [IS_IDENTITY] BIT NULL,

    [IDENTITY_SEED] INT NULL,

    [IDENTITY_INCREMENT] INT NULL,

    [IDENTITY_CURRENT] INT NULL,

    [DATA_TYPE] NVARCHAR(50) NULL,

    [CHARACTER_MAXIMUM_LENGTH] INT NULL,

    [NUMERIC_PRECISION] INT NULL,

    [NUMERIC_PRECISION_RADIX] INT NULL,

    [NUMERIC_SCALE] INT NULL,

    [DATETIME_PRECISION] INT NULL,

    [COLUMN_DEFAULT] NVARCHAR(50) NULL,

    [IS_NULLABLE] VARCHAR(3) NULL,

    PRIMARY KEY (ID))

    IF OBJECT_ID('tempdb..#TableIndexes') IS NOT NULL

    DROP TABLE #TableIndexes

    CREATE TABLE #TableIndexes (

    [rownum] INT NOT NULL,

    [grouprow] INT NULL,

    [colname] NVARCHAR(50) NULL,

    [indexname] NVARCHAR(50) NULL,

    [type_desc] NVARCHAR(50) NULL,

    [index_id] NVARCHAR(50) NULL,

    [column_id] INT NULL,

    [is_primary_key] BIT NULL,

    [is_descending_key] INT NULL,

    [is_included_column] INT NULL,

    PRIMARY KEY (rownum))

    IF OBJECT_ID('tempdb..#SQLStrings') IS NOT NULL

    DROP TABLE #SQLStrings

    CREATE TABLE #SQLStrings (

    [ID] INT IDENTITY(1,1) NOT NULL,

    [strSQL] NVARCHAR(MAX) NULL,

    [strType] NVARCHAR(255) NULL,

    PRIMARY KEY (ID))

    IF @isPopulate <> 1

    SET @isPopulate = 0

    /* Create one row in the target with columns from the source */

    IF @TargetCatalog = 'tempdb'

    BEGIN

    --its a temp table so safe to delete

    SET @strSQL = N'

    IF OBJECT_ID('''+@TargetFullPath+''') IS NOT NULL

    DROP TABLE '+@TargetTable+'

    SELECT TOP(1) *

    INTO '+CAST(@TargetFullPath AS NVARCHAR(100))+'

    FROM '+CAST(@SourceFullPath AS NVARCHAR(100))

    END

    ELSE

    BEGIN

    SELECT @suffix =

    CAST(CAST(DATEPART(minute,GETDATE()) AS NVARCHAR(20))

    +CAST(DATEPART(second,GETDATE()) AS NVARCHAR(20))

    +CAST(DATEPART(millisecond,GETDATE()) AS NVARCHAR(20))

    AS NVARCHAR(60))

    IF OBJECT_ID(@TargetFullPath) IS NOT NULL

    BEGIN

    SET @TargetTable = REPLACE(REPLACE(@TargetTable,'[',''),']','')

    SET @TargetTable = @TargetTable+'_'+@suffix

    SET @TargetTable = '['+@TargetTable+']'

    SET @TargetFullPath =

    CAST(@TargetCatalog AS NVARCHAR(50))+'.'+

    +CAST(@TargetSchema AS NVARCHAR(50))+'.'+

    +CAST(@TargetTable AS NVARCHAR(50))

    END

    SET @strSQL = N'

    SELECT TOP(1) *

    INTO '+CAST(@TargetFullPath AS NVARCHAR(100))+'

    FROM '+CAST(@SourceFullPath AS NVARCHAR(100))

    END

    --PRINT @strSQL

    EXEC sp_executeSQL @strSQL

    SET @strSQL = ''

    SET @strColumns = ''

    ;WITH MetaData AS

    (

    SELECT

    ROW_ID = ROW_NUMBER() OVER (ORDER BY ORDINAL_POSITION)

    ,TABLE_CATALOG = @SourceCatalogName

    ,TABLE_SCHEMA = @SourceSchemaName

    ,TABLE_NAME = @SourceTablename

    ,COLUMN_NAME = iso.COLUMN_NAME

    ,ORDINAL_POSITION = iso.ORDINAL_POSITION

    ,(CASE

    WHEN so.is_identity = 1 THEN 1

    ELSE 0

    END) AS IS_IDENTITY

    ,IDENTITY_SEED =

    (CASE

    WHEN so.is_identity = 1 THEN IDENT_SEED(iso.TABLE_CATALOG+'.'+iso.TABLE_SCHEMA+'.'+iso.TABLE_NAME)

    ELSE NULL

    END)

    ,IDENTITY_INCREMENT =

    (CASE

    WHEN so.is_identity = 1 THEN IDENT_INCR(iso.TABLE_CATALOG+'.'+iso.TABLE_SCHEMA+'.'+iso.TABLE_NAME)

    ELSE NULL

    END)

    ,IDENTITY_CURRENT =

    (CASE

    WHEN so.is_identity = 1 THEN IDENT_CURRENT(iso.TABLE_CATALOG+'.'+iso.TABLE_SCHEMA+'.'+iso.TABLE_NAME)

    ELSE NULL

    END)

    ,DATA_TYPE = iso.DATA_TYPE

    ,CHARACTER_MAXIMUM_LENGTH =

    (CASE

    WHEN iso.DATA_TYPE = 'VARCHAR' THEN iso.CHARACTER_MAXIMUM_LENGTH

    WHEN iso.DATA_TYPE = 'NVARCHAR' THEN iso.CHARACTER_OCTET_LENGTH

    ELSE NULL

    END)

    ,NUMERIC_PRECISION =

    (CASE

    WHEN iso.DATA_TYPE = 'DECIMAL' THEN iso.NUMERIC_PRECISION

    ELSE NULL

    END)

    ,NUMERIC_PRECISION_RADIX =

    (CASE

    WHEN iso.DATA_TYPE IN ('FLOAT','NUMERIC') THEN iso.NUMERIC_PRECISION_RADIX

    ELSE NULL

    END)

    ,NUMERIC_SCALE =

    (CASE

    WHEN iso.DATA_TYPE = 'DECIMAL' THEN iso.NUMERIC_SCALE

    ELSE NULL

    END)

    ,DATETIME_PRECISION =

    (CASE

    WHEN iso.DATA_TYPE IN ('DATE','DATETIME','DATETIME2','DATETIMEOFFSET','TIME') THEN iso.DATETIME_PRECISION

    ELSE NULL

    END)

    ,COLUMN_DEFAULT = iso.COLUMN_DEFAULT

    ,IS_NULLABLE = iso.IS_NULLABLE

    FROM

    INFORMATION_SCHEMA.COLUMNS AS iso

    LEFT OUTER JOIN

    (

    SELECT DISTINCT

    c.name

    ,c.is_identity

    FROM

    sys.columns AS c

    ) so

    ON iso.COLUMN_NAME = so.name

    AND so.is_identity = 1

    WHERE

    iso.TABLE_CATALOG = @SourceCatalogName

    AND iso.TABLE_SCHEMA = @SourceSchemaName

    AND iso.TABLE_NAME = @SourceTablename

    )

    INSERT INTO #MetaData

    SELECT DISTINCT

    ROW_ID

    ,TABLE_CATALOG

    ,TABLE_SCHEMA

    ,TABLE_NAME

    ,COLUMN_NAME

    ,ORDINAL_POSITION

    ,IS_IDENTITY

    ,IDENTITY_SEED

    ,IDENTITY_INCREMENT

    ,IDENTITY_CURRENT

    ,DATA_TYPE

    ,CHARACTER_MAXIMUM_LENGTH

    ,NUMERIC_PRECISION

    ,NUMERIC_PRECISION_RADIX

    ,NUMERIC_SCALE

    ,DATETIME_PRECISION

    ,COLUMN_DEFAULT

    ,IS_NULLABLE

    FROM

    MetaData

    ORDER BY

    ROW_ID

    SELECT

    @strColumns = @strColumns + N',['+COLUMN_NAME+']'

    FROM

    #MetaData

    SET @strColumns = STUFF(@strColumns,1,1,'')

    ;WITH cteIndexes AS

    (

    SELECT

    ROW_NUMBER() OVER (ORDER BY ic.index_id, col.column_id) AS rownum

    ,ROW_NUMBER() OVER (PARTITION BY ic.index_id ORDER BY ic.index_id) AS grouprow

    ,col.name AS colname

    ,ind.name AS indexname

    ,ind.type_desc

    ,ic.index_id

    ,col.column_id

    ,ind.is_primary_key

    ,ic.is_descending_key

    ,ic.is_included_column

    FROM

    sys.indexes ind

    LEFT OUTER JOIN sys.index_columns ic

    ON ind.object_id = ic.object_id

    AND ind.index_id = ic.index_id

    LEFT OUTER JOIN sys.columns col

    ON ic.object_id = col.object_id

    AND ic.column_id = col.column_id

    LEFT OUTER JOIN sys.tables t

    ON ind.object_id = t.object_id

    CROSS APPLY

    #Tally AS t2

    WHERE

    t.is_ms_shipped = 0

    AND ind.object_id = OBJECT_ID(@SourceFullPath)

    AND ind.index_id = t2.N

    )

    INSERT INTO #TableIndexes

    SELECT

    rownum

    ,grouprow

    ,colname

    ,indexname

    ,type_desc

    ,index_id

    ,column_id

    ,is_primary_key

    ,is_descending_key

    ,is_included_column

    FROM

    cteIndexes

    INSERT INTO #SQLStrings (strSQL,strType)

    SELECT

    N'ALTER TABLE '+@TargetTable+' ALTER COLUMN '+COLUMN_NAME+' '+

    +UPPER(DATA_TYPE)+

    CASE DATA_TYPE

    WHEN 'varchar' THEN '('+ISNULL(NULLIF(CAST(CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(5)),-1),'MAX')+')'

    WHEN 'nvarchar' THEN '('+ISNULL(NULLIF(CAST(CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(5)),-1),'MAX')+')'

    WHEN 'decimal' THEN '('+CAST(NUMERIC_PRECISION AS NVARCHAR(5))+','+CAST(NUMERIC_SCALE AS NVARCHAR(5))+')'

    WHEN 'numeric' THEN '('+CAST(NUMERIC_PRECISION_RADIX AS NVARCHAR(5))+','+CAST(NUMERIC_SCALE AS NVARCHAR(5))+')'

    ELSE ''

    END+

    CASE IS_NULLABLE

    WHEN 'NO' THEN ' NOT NULL'

    ELSE ' NULL'

    END

    ,N'ALTER TABLE' AS strType

    FROM

    #MetaData

    --for testing

    --SELECT * FROM #SQLStrings

    --RETURN

    IF @isCreatePK = 1

    BEGIN

    /* Build the primary key */

    ;WITH cteKeys AS

    (

    SELECT DISTINCT

    rownum

    ,grouprow

    ,colname

    ,indexname

    ,type_desc

    ,index_id

    ,column_id

    ,is_primary_key

    ,is_descending_key

    ,is_included_column

    FROM

    #TableIndexes

    )

    INSERT INTO #SQLStrings

    SELECT

    REPLACE(REPLACE(

    N'IF EXISTS (SELECT * FROM sys.indexes

    WHERE object_id = OBJECT_ID(N'''+

    CASE

    WHEN @TargetCatalog = 'tempdb' THEN 'tempdb..'

    ELSE ''

    END+

    'PK_'+@TargetTable+''') AND name = N'''+

    CASE

    WHEN @TargetCatalog = 'tempdb' THEN 'tempdb..'

    ELSE ''

    END+

    'PK_'+@TargetTable+''')

    ALTER TABLE '+@TargetTable+' DROP CONSTRAINT PK_'+

    REPLACE(REPLACE(REPLACE(@TargetTable,'[',''),']',''),'!','')+'

    ALTER TABLE '+@TargetTable+' ADD CONSTRAINT PK_'+

    REPLACE(REPLACE(REPLACE(@TargetTable,'[',''),']',''),'!','')

    +' PRIMARY KEY CLUSTERED ('+cols_primary_key+')'

    ,'[[','['),']]',']') AS strSQL

    ,N'CREATE PRIMARY KEY' AS strType

    FROM

    (

    SELECT

    ISNULL(STUFF((

    SELECT

    (',['+colname +']')+

    CASE is_descending_key

    WHEN 1 THEN ' DESC'

    ELSE ' ASC'

    END

    FROM

    cteKeys

    WHERE

    is_primary_key = 1

    AND index_id = t.N

    ORDER BY

    column_id

    FOR XML PATH(''))

    ,1,1,''),'')

    AS cols_primary_key

    ,index_id

    FROM

    cteKeys

    CROSS APPLY

    #Tally t

    WHERE

    t.N = index_id

    ) k

    WHERE

    k.index_id = 1

    /* End building the primary key */

    END

    --for testing

    --SELECT OBJECT_ID('tempdb..##DynamicTempTable')

    --SELECT OBJECT_ID('tempdb..PK_##DynamicTempTable')

    --SELECT * FROM #SQLStrings

    --RETURN

    IF @isCreateIndexes = 1

    BEGIN

    /* Build the indexes */

    ;WITH cteIndexes AS

    (

    SELECT DISTINCT

    rownum

    ,grouprow

    ,colname

    ,indexname

    ,type_desc

    ,index_id

    ,column_id

    ,is_primary_key

    ,is_descending_key

    ,is_included_column

    FROM

    #TableIndexes

    )

    INSERT INTO #SQLStrings

    SELECT

    REPLACE(REPLACE(

    N'CREATE '+type_desc+' INDEX ['

    + prefix+'__'

    +REPLACE(REPLACE(@TargetTable,'[!',''),']','')+'__'+REPLACE(CAST(NEWID() AS NVARCHAR(36)),'-','')

    +CAST(DATEPART(minute,GETDATE()) AS NVARCHAR(10))

    +CAST(DATEPART(second,GETDATE()) AS NVARCHAR(10))

    +CAST(DATEPART(millisecond,GETDATE()) AS NVARCHAR(10))

    +CAST(CONVERT(DECIMAL(6,0),ABS(CHECKSUM(NEWID()))%100000) AS NVARCHAR(10))

    + '] ON ['+@TargetTable+'] ('+cols+')'

    + CASE

    WHEN is_included_column = 0 THEN ''

    ELSE ' INCLUDE ('+cols_included+')'

    END

    ,'[[','['),']]',']') AS strSQL

    ,N'CREATE INDEX' AS strType

    FROM

    (

    SELECT

    rownum

    ,index_id

    ,indexname

    ,column_id

    ,type_desc

    ,is_primary_key

    ,is_descending_key

    ,is_included_column

    ,(CASE

    WHEN is_primary_key = 1 THEN 'PK'

    ELSE 'IX'

    END) AS prefix

    ,(SELECT

    ISNULL(STUFF((

    SELECT

    (',['+colname +']')

    FROM

    cteIndexes

    WHERE

    is_primary_key = 0

    AND is_included_column = 0

    AND index_id = t.N

    ORDER BY

    column_id

    FOR XML PATH(''))

    ,1,1,''),'')

    ) AS cols

    ,(SELECT

    ISNULL(STUFF((

    SELECT

    (',['+colname +']')

    FROM

    cteIndexes

    WHERE

    is_included_column = 1

    AND index_id = t.N

    ORDER BY

    column_id

    FOR XML PATH(''))

    ,1,1,''),'')

    ) AS cols_included

    FROM

    cteIndexes

    CROSS APPLY

    #Tally t

    WHERE

    t.N = index_id

    ) i

    WHERE

    i.column_id = 1

    AND i.is_primary_key = 0

    ORDER BY

    i.index_id

    ,i.column_id

    /* End building indexes */

    END

    IF @isCreateDefaults = 1

    BEGIN

    /* Build the defaults */

    ;WITH cteDefaults AS

    (

    SELECT

    ROW_NUMBER() OVER (ORDER BY ORDINAL_POSITION) AS rownum

    ,COLUMN_NAME

    ,COLUMN_DEFAULT

    ,ORDINAL_POSITION

    FROM

    #MetaData

    )

    INSERT INTO #SQLStrings

    SELECT

    REPLACE(REPLACE(

    N'IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''DF_'+@TargetTable+''') AND type = ''D'')

    BEGIN

    ALTER TABLE '+@TargetTable+' DROP CONSTRAINT DF_'+

    REPLACE(REPLACE(REPLACE(@TargetTable,'[',''),']',''),'!','')+'

    END

    ALTER TABLE '+@TargetTable+' ADD DEFAULT ('+COLUMN_DEFAULT+')

    FOR ['+COLUMN_NAME+']','[[','['),']]',']') AS strSQL

    ,N'CREATE DEFAULT' AS strType

    FROM

    (

    SELECT DISTINCT

    COLUMN_NAME

    ,COLUMN_DEFAULT

    ,ORDINAL_POSITION

    FROM

    cteDefaults

    WHERE

    COLUMN_DEFAULT IS NOT NULL

    ) i

    ORDER BY

    i.ORDINAL_POSITION

    /* End building defaults */

    END

    --for testing

    --SELECT * FROM #SQLStrings

    --RETURN

    DECLARE ExecStrSql CURSOR

    FOR

    SELECT DISTINCT

    ID

    ,strSQL

    FROM

    #SQLStrings

    ORDER BY

    ID

    OPEN ExecStrSql

    FETCH NEXT FROM ExecStrSql

    INTO @ID,@strSQL

    WHILE @@FETCH_STATUS = 0

    BEGIN

    --PRINT @strSQL

    EXEC(@strSQL)

    SET @strSQL = ''

    FETCH NEXT FROM ExecStrSql

    INTO @ID,@strSQL

    END

    CLOSE ExecStrSql

    DEALLOCATE ExecStrSql

    /* The table structure is built so clear the table */

    SET @strSQL = N'

    IF OBJECT_ID('''+@TargetFullPath+''') IS NOT NULL

    TRUNCATE TABLE '+@TargetTable

    --PRINT @strSQL

    EXEC(@strSQL)

    IF @isPopulate = 1

    BEGIN

    SELECT

    @TargetFullPath AS TargetTable

    ,@SourceFullPath AS SourceTable

    ,rows

    ,reserved

    ,data

    ,index_size

    ,unused

    FROM #SpaceUsed

    SET @strSQL = '

    SET IDENTITY_INSERT '+@TargetTable+' ON;

    INSERT INTO '+@TargetTable+'

    ('+@strColumns+')

    SELECT '+@strColumns+'

    FROM '+CAST(@SourceSchemaName AS NVARCHAR(50))+'.'+CAST(@SourceTablename AS NVARCHAR(50))+';

    SET IDENTITY_INSERT '+@TargetTable+' OFF;

    SELECT * FROM '+@TargetTable+';'

    --PRINT @strSQL

    EXEC(@strSQL)

    END

    END

     

  • it's strange that no one asked a fairly obvious question(s) as

    #1. why exactly OP needs to backup single table?

    #2. what is the reason for such need and how it will be used?

    #3. How often it needs to be done.

    I think before the original question can be answered properly, this information is needed.

    so let see a few possible scenarios and solutions. (some of the scenarios are based on answers provided by other users and simply added here for logical explanation.)

    Scenario 1: OP needs to change table data during code drop(deployment) and wants to have a rollback functionality "Just in case"

    for that "BCP" or other bulk export might not work cleanly and/or reliably, not mentioning fast.

    an in-place table copy is much faster. however even in-place copy can be difficult with large tables.

    in the past, when I worked with such scenario, a DBA would setup a table replication process into a copy of the table as per request from DEV team. we would provide DBA with a list of tables that we want to have a back up for and by the time of deployment we had a fairly full table that we would just drop all the missing records into at the time when scripts are run. (reason for that was that we had a very small time window for replication and deployment and we had to minimize the time to as less as possible. so the DB would be taken off line 20 min before deployment and we needed to guaranty all data integrity.)

    the backup table would than be renamed with deployment date attached and stored for 2 weeks in DB.

    after 3 weeks it would be moved onto backup DB (yes we had a special DB instance just for on line back up store) and hold there for 3-6 month.

    after that it would be dumped onto tape storage as part of the full DB backup and removed.

    if table is small, a "select into " can be used directly at the time of the deployment and a backup table created just than.

    Scenario 2: OP wants to backup table and take it outside the DB into a file. speed is not important .

    "BCP" or other backup process can be used here. data is secured and verified as needed.

    import is designed based on what the backup process you end up with.

    FYI: regardless of what OP needs are (what scenario is at play)

    if you are working with SQL2010 and have tools from SMSS Tool Pack installed, or have access to an SQL tools like Tools from RedGate software

    or ApexSql to name a few.

    you can generate a scripts form the table that can be used in automated deployment later if needed.

    all of the tolls mentioned have the ability to create a text based script files that will include

    checking if table exist,

    create table if needed.

    drop/disable any/all indexes, defaults,constrains, checks (including scripting all of that along with the main table)

    generate data creation SQL scripts that are mostly universal and can be used across almost any version of the SQL DB (NOTE: almost any version with in a reasonable expectations of course.)

    many of the tools and utilities can be very fine-tunned to generate scripts tunned for your needs/wants

  • Vlad-207446 (7/15/2013)


    if you are working with SQL2010

    Do you mean 2008 of 2012?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • At present time I mean 2008.

    We did not moved to 2012 yet 🙂 so I am not sure what options are exists for it...

  • Vlad-207446 (7/15/2013)


    it's strange that no one asked a fairly obvious question(s) as

    #1. why exactly OP needs to backup single table?

    #2. what is the reason for such need and how it will be used?

    #3. How often it needs to be done.

    I think before the original question can be answered properly, this information is needed.

    Considering just exactly what the OP asked, I took it as an interview question... and it's a very common question, ideed. In such a case, the more different options you can present to the interviewer, the better off you'll be. 😀

    On that note, thanks for adding a couple of methods and the reasoning behind those methods.

    --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)

  • agreed, and thanks.

    I do tend to take things a bit more literally,

    ignoring the fact that question might just be educational.

  • 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.

  • Erland Sommarskog (7/15/2013)


    Jeff Moden (2013-07-14)


    Although it's not technically a backup of a single table, you could move the clustered index (which is really the table itself with a B-Tree in the rafters) and the other indexes to a separate Filegroup and then backup and restore that Filegroup.

    But note in this case that you cannot use this to copy a single table from one database to another. Nor can you restore the filegroup to get back an earlier version of the table, while keeping the database at the rest of the current version. SQL Server is very careful to make sure that you have a transactionally consistent database.

    Thanks, Erland. Not sure what I was thinking. It must have been something else but I swear that I was able to do this with a Read Only FileGroup. It was quite a while back so maybe it's a failing memory that led me to a bad suggestion.

    --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 15 posts - 1 through 15 (of 19 total)

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