Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DataBase Backup for selected tables through SSIS 2005


DataBase Backup for selected tables through SSIS 2005

Author
Message
kvaramu2005
kvaramu2005
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 142
Dear friends

Before starting execution of my package i have to take the Backup
for selectd Tables Thorugh the package..

Is there any way for this..

Can i use Script for this?
If S plz guide me.

Thnks
koti
ChiragNS
ChiragNS
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2951 Visits: 1865
Hi

you cannot "backup" some tables. what you can do is import the data of the tables you want to backup to another set of tables.

"Keep Trying"
rajdba
rajdba
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 430
Individual table backups can be done by below methods

Package


1. Script the structure of your table.
2. Make Package to export the data from the table



File And FileGroups


1. Create new Files and Filegroups
2. Change the Filegroup of that table to new file group at table properties.
3. Take the Backup of the Filegroups



I had faced the above requirement before and at that Time I found the Below script from DatabaseJournal


Stored Procedure:


use master
go
Create procedure SP_BKUPTables
@Tablelist varchar(2000),
@BackupPath varchar(2000)
as
set nocount on
--Objective: To backup one or more tables
--Created by: MAK
--Date Created : May 2, 2003
--Error Checking
declare @count1 int
declare @i int
declare @table varchar(128)
declare @query varchar(1000)
declare @length int
declare @errorflag tinyint
declare @backupdbname varchar(38)
declare @Tablelist2 varchar(2000)
set @errorflag =0
set @backupdbname ="["+convert(varchar(36),newid())+"]"

If replace(@Tablelist," ","")=""
begin
set @errorflag=1
end
If replace(@Backuppath," ","")="" begin
set @Backuppath =(select replace(replace(filename,"tempdb.mdf","")," ","") +
"Mytable.TBL" from sysfiles where fileid =1)
end

Print "USP_BKUPTables Parameters"
Print "_________________________"
Print "Developed by : MAK [Muthusamy Anantha Kumar]"
Print ""
print "Database Name: "+ db_name()
print "Table List : "+ @tablelist
print "Backup Path : "+ @backuppath

Create table #backuptable (id int identity(1,1),name varchar(128))
set @Tablelist2 =@Tablelist +","
set @length =len(@tablelist2)
while @length >0
begin
insert into #backuptable select replace(left(@Tablelist2,charindex(',',@Tablelist2,1)),',','')
set @Tablelist2 = replace(@Tablelist2 ,left(@Tablelist2,charindex(',',@Tablelist2,1)),"")
set @length =len(@tablelist2)
end
if (select count(*) from #backuptable) <> (select count(*) from
sysobjects where name in (select name from #backuptable) and type='u')
begin
set @errorflag =1
Print "Error********: One or More tables not found"
end

set @i=1
--select * from #backuptable
if @errorflag =0
begin
print " "
print "Creating temporary database..."
print " "

exec ("create database "+ @backupdbname )
checkpoint

select @count1 = count(*) from #backuptable
print @count1
print " "
print "Enabling Select into bulk copy..."
print " "
set @query = "sp_dboption "+ @backupdbname+ ",'select into/bulkcopy',true"
--print @query
exec (@query)
print " "
print "Copying Data..."
print " "
while @i <= @count1
begin
select @table = name from #backuptable where id = @i
print "Copying "+@Table+"..."
set @query = "select * into "+ @backupdbname +".dbo."+ @table + " from " + @table
exec (@query)
-- print @query
set @i=@i+1
end

print " "
print "Backuping Database..."
print " "
exec (" backup database "+ @backupdbname + " to disk = '" + @BackupPath + "' with init")

print " "
print "Dropping Temporary Database..."
print " "
exec ("Drop database "+@backupdbname )
end


Usage:

Use SNAR
go
SP_BKUPTables 'SNAR_Request,mytable1,employee','d:\mytable.bak'
Go

Process:

The basic concept of this procedure is to:

Create a temporary database on the fly (uses uniqueidentifier as databasename)
Enable temporary database to handle 'Select into/bulk copy'
Copy all the tables in the list to the temporary database
Take a backup of the temporary database. If database path is not given (SP_BKUPTables 'SNAR_request','') then it takes the tempdb's path as the default path for backup file location.
Delete the temporary database




Hope So it is Helpful.Smile
Regards,
Raj
pduplessis-723389
pduplessis-723389
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1569 Visits: 400
Awesome post Raj

Smile
rajdba
rajdba
SSC-Enthusiastic
SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)SSC-Enthusiastic (130 reputation)

Group: General Forum Members
Points: 130 Visits: 430
Thanks pduplessis
Regards,
Raj
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search