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

Question

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

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

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

Vlad-207446 Right there with Babe Points: 773 More actions · Answer 1

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;

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>

udayroy15 Ten Centuries Points: 1076 More actions · Answer 2

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

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

Geoff A SSChampion Points: 11511 More actions · Answer 4

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

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 5

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)