Table Backup

Question

Table Backup

Subu

Say Hey Kid

Points: 680
More actions
September 16, 2007 at 11:20 pm

#181087

Hi Friends,
How can i take particular table back up in MS SQL SERVER plz help very urgent
Thx

Viewing 7 posts - 1 through 6 (of 6 total)

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

Nimesh T Mathews Right there with Babe Points: 727 More actions · Answer 1

Hi,

You can use DTS import exort wizard to take the table content. Export the content of the table to a temp table. But this will cause problem when the table is having links with other tables using the foreign key/ primary key relation.

Regards

Nimesh

Subu Say Hey Kid Points: 680 More actions · Answer 2

Hi Nimesh,

Thank u for reply,

my prob is also same thing this table related to some another table also my client is asking to me can plz take backup of this table only any body plz give suggestions plz

Thx

Simon Doubt Mr or Mrs. 500 Points: 565 More actions · Answer 3

Subu,

What about moving the table to its own Filegroup, and then doing a Filegroup backup?

This would require some work, though. Aside from moving the table to its own FG, you would have to make sure the db recovery model was set to full, and that you had an existing full database backup, and you would also have to backup the TLogs, so it's more administrative work, but it might work.

Is this a one-time backup?

-Simon

Scottye McClain SSC-Addicted Points: 455 More actions · Answer 4

If this is not a "one-time" backup... then I agree with Simon on this. Create a new filegroup (Properties tab of the database) and a new file (.NDF). Associate the table to the new filegroup (you can move the table over to the filegroup by recreating the clustered index on the new filegroup). Then you can actually run a script to backup that specific data file:

I.E.

You MUST first backup the PRIMARY filegroup:

BACKUP DATABASE MyDatabase FILEGROUP = 'PRIMARY' TO DISK = 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\\BACKUPS\mydatabase_PRIMARY_Backup.bak'

GO

Then backup the desired filegroup:

BACKUP DATABASE MyDatabase FILEGROUP = 'MyDatabase_FG' TO DISK = 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\\BACKUPS\mydatabase_datafile2_Backup.bak'

GO

Also, you must at this point do a Transaction Log backup as restoring this table/filegroup will require restoring the TLog:

BACKUP LOG MyDatabase

TO DISK = 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\\BACKUPS\mydatabase_Log.TRN'

GO

Then, should you need to restore that table/filegroup:

Restore the PRIMARY filegroup

RESTORE DATABASE MyDatabase

FILEGROUP = 'PRIMARY'

FROM DISK = 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\BACKUPS\mydatabase_PRIMARY_Backup.bak'

WITH MOVE 'PRIMARY to 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDATABASE_data.MDF',

MOVE 'mydatabase_Log' to 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\LOGS\MyDATABASE_data.MDF',

STATS = 20,

NORECOVERY,

REPLACE,

PARTIAL

Restore the filegroup you want:

RESTORE DATABASE MyDatabase

FILEGROUP = 'MyDatabase_FG'

FROM DISK = 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\\BACKUPS\mydatabase_datafile2_Backup.bak'

WITH MOVE 'MyDatabase_FG' to 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Mydatabase_FG_data.NDF',

STATS = 20,

REPLACE,

NORECOVERY

Then restore the TLog backup to finish:

RESTORE LOG MyDatabase

FROM DISK = 'D:\Microsoft SQL Server\MSSQL.1\MSSQL\\BACKUPS\mydatabase_Log.TRN'

WITH RECOVERY

Hope this helps...

Simon Doubt Mr or Mrs. 500 Points: 565 More actions · Answer 5

I realize that this is probably much too late now, but you could always use SSIS, or the bcp utility, or a T-SQL INSERT ... SELECT statement to transfer the data from the production database table to another database that would act as a backup container site for the table data that you wanted backed-up.

- Simon

homebrew01 SSC Guru Points: 55539 More actions · Answer 6

Simon Doubt (10/1/2007)
I realize that this is probably much too late now, but you could always use SSIS, or the bcp utility, or a T-SQL INSERT ... SELECT statement to transfer the data from the production database table to another database that would act as a backup container site for the table data that you wanted backed-up.
- Simon

We frequently do this when we need to save a table.

Select * into Table_Saved_20071003

from Table