Need To BackUp Only Some Tables

Question

Post reply

Need To BackUp Only Some Tables

raju.tanneeru

Right there with Babe

Points: 763
More actions
May 26, 2010 at 8:41 am

#221606

Hi,
i do have daily(differential) & weekly (full) backups running on my DB Server.
but i do have a scenario where i need to take a backup of few tables before doing some kind of DB operations.
is it possible to do that without disturbing the daily backups ?? If any one has any script or way of doing it then please let me know
thank you

Viewing 9 posts - 1 through 8 (of 8 total)

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

Renuka__ Hall of Fame Points: 3656 More actions · Answer 1

Hi,

If it is on the same instance and data inside tables is small, then

select * into "Backup Table Name" from "source table"

But it will copy only the data not the schema (constaints etc.,).

If you want to copy along with the schema, then generate 'Create To' script for the source table and create a new backup table using this script. And copy the data using Insert -

Insert into "New Backup Table" select * from "Source Table"

If there are many tables and the data is large, try with import/export wizard selecting only the required tables.

Thank you

[font="Verdana"]Renuka__[/font]

raju.tanneeru Right there with Babe Points: 763 More actions · Answer 2

Thanks for the quick reply

but i want this backup task should be scheduled, so i cant use wizard. that is the reason that i am looking to write some script in a better way and add it as a agent job

so i am looking for script

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

select * into "Backup Table Name" from "source table"

This will create a new table and populate it with the result set of the select from source table. Put this in a T-SQL job step to run it on a schedule. You could put a DROP statement first so the table created in the previous job run will be deleted before creating a new one.

Greg

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

There is no function within backups that allows you to backup tables only.

You could move these tables to a different file group and try doing a file group only backup.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Do you have log backups running currently?

Another alternative is to use filegroups and move your specific tables into a separate filegroup. Then you can backup that filegroup individually.

Or as has been mentioned you can copy the tables into newtables.

Both of these methods can be scripted and executed as a job. However, you will see your db grow if you do the table copy too frequently without some sort of additional maintenance.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

homebrew01 SSC Guru Points: 55548 More actions · Answer 6

Greg Charles (5/26/2010)
select * into "Backup Table Name" from "source table"
This will create a new table and populate it with the result set of the select from source table. Put this in a T-SQL job step to run it on a schedule. You could put a DROP statement first so the table created in the previous job run will be deleted before creating a new one.

Or append the date to your backup table name if you need to retain the earlier backup table.

You could also create a separate database and put your backup tables in there instead of your production database.

CirquedeSQLeil asked about backups ... why no log backups ? Is this a production database ?

raju.tanneeru Right there with Babe Points: 763 More actions · Answer 7

Thanks to every one for your replies

with all inputs and googling finally i am end up with doing this

--Source DB: ScourceDB

--Destination DB: DesDB [this DesDB is only to take backup of these files only]

USE ScourceDB

--For TABLE1

IF EXISTS(SELECT NAME FROM DesDB.SYS.TABLES WHERE NAME = 'Address')

DROP TABLE DesDB.DBO.Address

SELECT * INTO DesDB.DBO.[Address] FROM [Address]

--For TABLE2

IF EXISTS(SELECT NAME FROM DesDB.SYS.TABLES WHERE NAME = 'Product')

DROP TABLE DesDB.DBO.Product

SELECT * INTO DesDB.DBO.[Product] FROM [Product]

--TABLE3

--TABLE4

--TABLE5

-- Finally take the backup of Destination Database i.e. DesDB

DECLARE @filename VARCHAR(255)

DECLARE @currDate datetime

set @currDate = convert(varchar,getdate(),112)

SET @filename = 'E:\DesDB_IMPDATA_' + convert(varchar,dateadd(y, 2 - datepart(dw, @currDate), @currDate),112) + '.bkp'

BACKUP DATABASE DesDB

TO DISK = @filename

WITH INIT, NOUNLOAD, NAME = N'DesDB',

NOSKIP, STATS = 10, NOFORMAT

RESTORE VERIFYONLY FROM DISK = @filename

please let me know if any issues with above script, i made a agent job with this script

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

You're welcome and thanks for posting back your solution.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events