SQL 2005 File IO programming

23 Mar 2006 5:11 PM

Mark

We would like to add some capablility in our backup routines to move, copy,
and delete files after backups. For example: After the backup, copy the
backup file to another server, then delete old backup files. We use MSSQL
backups, SQL Litespeed, and red-gate software depending on the server.
Therefore don't use the maintenance plans)

What would be the best way to do this?
SSIS packages or CLR assembly wrapper to create procedures for IO
functionality? Other?

Thanks for your help

23 Mar 2006 5:14 PM

David Browne

Show quote

"Mark" <M***@discussions.microsoft.com> wrote in message
news:B7602B9E-9F98-41B9-9E88-DE5F8932CBE4@microsoft.com...
> We would like to add some capablility in our backup routines to move,
> copy,
> and delete files after backups. For example: After the backup, copy the
> backup file to another server, then delete old backup files. We use MSSQL
> backups, SQL Litespeed, and red-gate software depending on the server.
> Therefore don't use the maintenance plans)
>
> What would be the best way to do this?
> SSIS packages or CLR assembly wrapper to create procedures for IO
> functionality? Other?
>

SSIS.

David

23 Mar 2006 5:56 PM

Mark

Show quote

"David Browne" wrote:

>
> "Mark" <M***@discussions.microsoft.com> wrote in message
> news:B7602B9E-9F98-41B9-9E88-DE5F8932CBE4@microsoft.com...
> > We would like to add some capablility in our backup routines to move,
> > copy,
> > and delete files after backups. For example: After the backup, copy the
> > backup file to another server, then delete old backup files. We use MSSQL
> > backups, SQL Litespeed, and red-gate software depending on the server.
> > Therefore don't use the maintenance plans)
> >
> > What would be the best way to do this?
> > SSIS packages or CLR assembly wrapper to create procedures for IO
> > functionality? Other?
> >
>
>
> SSIS.
>
> David
>
>

Thanks for your answer.
Why did you choose SSIS? How would the package be called from a SQL server
Stored Procedure?

Thanks again,

Mark

24 Mar 2006 12:14 AM

David Browne

Show quote

"Mark" <M***@discussions.microsoft.com> wrote in message
news:BA2B9776-16A4-4888-9767-7050FF97C37F@microsoft.com...
>
>
> "David Browne" wrote:
>
>>
>> "Mark" <M***@discussions.microsoft.com> wrote in message
>> news:B7602B9E-9F98-41B9-9E88-DE5F8932CBE4@microsoft.com...
>> > We would like to add some capablility in our backup routines to move,
>> > copy,
>> > and delete files after backups. For example: After the backup, copy the
>> > backup file to another server, then delete old backup files. We use
>> > MSSQL
>> > backups, SQL Litespeed, and red-gate software depending on the server.
>> > Therefore don't use the maintenance plans)
>> >
>> > What would be the best way to do this?
>> > SSIS packages or CLR assembly wrapper to create procedures for IO
>> > functionality? Other?
>> >
>>
>>
>> SSIS.
>>
>> David
>>
>>
> Thanks for your answer.
> Why did you choose SSIS?

SSIS has primitives to do database maintence tasks, file system tasks,
invoke external executables and and CLR script tasks. SSIS packages can be
run from SQL Agent jobs and fit right in to the DBA's toolset.

>How would the package be called from a SQL server Stored Procedure?

Through the SQL Server Agent. Create a job for the package and kick it off
with sp_start_job, or schedule it, or run it manually.

David

Davud

Show quote

>
> Thanks again,
>
> Mark

23 Mar 2006 6:36 PM

William Stacey [MVP]

I would use CLR proc as the IO namespace is very rich and you can do about
anything.

--
William Stacey [MVP]

Show quote

"Mark" <M***@discussions.microsoft.com> wrote in message
news:B7602B9E-9F98-41B9-9E88-DE5F8932CBE4@microsoft.com...
| We would like to add some capablility in our backup routines to move,
copy,
| and delete files after backups. For example: After the backup, copy the
| backup file to another server, then delete old backup files. We use MSSQL
| backups, SQL Litespeed, and red-gate software depending on the server.
| Therefore don't use the maintenance plans)
|
| What would be the best way to do this?
| SSIS packages or CLR assembly wrapper to create procedures for IO
| functionality? Other?
|
| Thanks for your help

23 Mar 2006 8:26 PM

JT

Consider calling both the backup process and the file copy from SSIS.

Show quote

"Mark" <M***@discussions.microsoft.com> wrote in message
news:B7602B9E-9F98-41B9-9E88-DE5F8932CBE4@microsoft.com...
> We would like to add some capablility in our backup routines to move,
> copy,
> and delete files after backups. For example: After the backup, copy the
> backup file to another server, then delete old backup files. We use MSSQL
> backups, SQL Litespeed, and red-gate software depending on the server.
> Therefore don't use the maintenance plans)
>
> What would be the best way to do this?
> SSIS packages or CLR assembly wrapper to create procedures for IO
> functionality? Other?
>
> Thanks for your help