July 13, 2013 at 10:44 pm
Hi...
How to take backup of Single table and how to restore? is there any query like database backup?
shivakumar...
July 13, 2013 at 11:03 pm
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.
July 14, 2013 at 1:16 pm
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).
July 14, 2013 at 2:34 pm
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]
July 14, 2013 at 10:53 pm
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
Change is inevitable... Change for the better is not.
July 15, 2013 at 1:44 am
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]
July 15, 2013 at 8:16 am
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" 😉
July 15, 2013 at 8:58 am
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
July 15, 2013 at 9:25 am
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
July 15, 2013 at 10:02 am
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" 😉
July 15, 2013 at 10:24 am
At present time I mean 2008.
We did not moved to 2012 yet 🙂 so I am not sure what options are exists for it...
July 15, 2013 at 12:21 pm
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
Change is inevitable... Change for the better is not.
July 15, 2013 at 12:55 pm
agreed, and thanks.
I do tend to take things a bit more literally,
ignoring the fact that question might just be educational.
July 17, 2013 at 6:48 am
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.
July 17, 2013 at 7:28 am
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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply