May 6, 2009 at 11:30 am
Hi,
Need some pointers on SSIS.
I want to create a package which will take backup of daily on server s1 for batabase d1. The backup will be placed on server s2 and database d2.
I have got access to both servers. Can any one tell me how to do this ?
Thanks
Kin
May 6, 2009 at 11:53 am
Please explain what you want to "backup". Since you want to use SSIS, I assume you want to copy data from one database to another.
Greg
May 7, 2009 at 3:56 am
Hi greg,
I want to develop a package which will take backup of one table of database d1 on server s1 to database d2 on server s2. I know that we can schedule a package with help of schedular but how to transfer the data between servers. And this task will be repeated every night and data will be appended for every day.
Thanks
Kin
May 7, 2009 at 11:40 am
Sounds like what you need is an SSIS package with a Data Flow Task that transfers data from the d1 database to the d2 database. In that case, you would create an OLEDB source connection for s1.d1 and an OLEDB destination connection for s2.d2.
After creating the package, create a SQL Agent job and put the package execution in a job step. Schedule the job to run daily.
You can find details about all this in BooksOnLine and in dozens of articles here and elsewhere on the web.
Greg
May 12, 2009 at 5:30 am
Hi Greg,
It worked perfectly.Thanks.
One more smal ques.
How to take incremental backup. I have some fields of datetime type in my tables. I want to take 6 hours backup?
May 12, 2009 at 9:24 am
If you're still talking about copying data with SSIS, just use a query in the Data FLow task that compares the current time with what's in the datetime column in the table.
If you're talking about real backups, look at differential backups which backup changes to a database since the last full backup? See BOL http://msdn.microsoft.com/en-us/library/ms175477(SQL.90).aspx for details.
Greg
May 13, 2009 at 2:36 am
Hi Greg,
I used the query in data flow task and it is workign. But the records fetched nby query has to be inserted in destination(Oledb destination). I have oledbsouirce and oledb destination. How can we catch the result set returned by query to insert it in Oledb destination?
May 13, 2009 at 9:32 am
You need to put a Data Conversion Transformation or a Copy Column Transformation between the source and destination. That's what specifies the movement of data from the source table to the destination table.
Greg
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply