June 6, 2008 at 12:47 am
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
June 6, 2008 at 1:10 am
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"
June 6, 2008 at 1:13 am
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.:)
Regards,
Raj
June 6, 2008 at 2:00 am
Awesome post Raj
🙂
June 6, 2008 at 2:53 am
Thanks pduplessis
Regards,
Raj
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply