Copying instead of backing up SQL 2005 db

I just found out that our backup software (MS DPM) is conflicting with the
SQL Maintenance jobs. We can't use back up database task in maintenace jobs.
Is there a way to copy a database? I'd like to add this to maintenance jobs
as a t-sql script, or sql server agent job.
Thanks in advance...
The BACKUP command has an option named COPY_ONLY that allows to perform
database backups without affecting the normal sequence of full, differential
and transaction log backups.
See more details on COPY_ONLY on BACKUP on BOL.
Hope this helps,
Ben Nevarez
"Artunc" wrote:

> I just found out that our backup software (MS DPM) is conflicting with the
> SQL Maintenance jobs. We can't use back up database task in maintenace jobs.
> Is there a way to copy a database? I'd like to add this to maintenance jobs
> as a t-sql script, or sql server agent job.
> Thanks in advance...
|||My question would be why are you trying to back it up twice then? How
exactly is it conflicting?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Artunc" <Artunc@.discussions.microsoft.com> wrote in message
news:96113D5A-2B6E-4EDC-9D3E-91979FC7E3B5@.microsoft.com...
>I just found out that our backup software (MS DPM) is conflicting with the
> SQL Maintenance jobs. We can't use back up database task in maintenace
> jobs.
> Is there a way to copy a database? I'd like to add this to maintenance
> jobs
> as a t-sql script, or sql server agent job.
> Thanks in advance...
|||No other reson than having a second set of backup for importand dbs.
This is the error I am getting in DPM when backup maintenance job and DPM
backup job run for the same databases:
"DPM tried to do a SQL log backup, either as part of a backup job or a
recovery to latest point in time job. The SQL log backup job has detected a
discontinuity in the SQL log chain for SQL Server 2005 database database
SQLSVR\DB1 since the last backup. All incremental backup jobs will fail until
an express full backup runs. (ID 30140 Details: Internal error code:
0x80990D11)"
"Andrew J. Kelly" wrote:

> My question would be why are you trying to back it up twice then? How
> exactly is it conflicting?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Artunc" <Artunc@.discussions.microsoft.com> wrote in message
> news:96113D5A-2B6E-4EDC-9D3E-91979FC7E3B5@.microsoft.com...
>

Copying DTS Packages to new server

Is there any way/an easy way to copy dts packages from one server to another? I cant seem to find a way to script them, is there any other way other than recreating them on the new server?
Regards JimPackages are saved in msdb..sysdtspackages - you just need to copy the relevant rows from that table.
Note that sysdtspackages is undocumented so this may not work in the future.

If you prefer this is an SP to load all packages and save them to files - just change the save to save to a server. This will lose the graphics as there is no context for it and no way at present to load the stream.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_SavePackages]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_SavePackages]
GO

Create procedure s_SavePackages
@.Path varchar(128)
as
/*

*/

set nocount on

declare @.objPackage int
declare @.PackageName varchar(128)
declare @.rc int
declare @.ServerName varchar(128)
declare @.FileName varchar(128)
declare @.FilePath varchar(128)
declare @.cmd varchar(2000)

select @.ServerName = @.@.ServerName ,
@.FilePath = @.Path

if right(@.Path,1) <> '\'
begin
select @.Path = @.Path + '\'
end

-- create output directory - will fail if already exists but ...
select @.cmd = 'mkdir ' + @.FilePath
exec master..xp_cmdshell @.cmd

create table #packages (PackageName varchar(128))
insert #packages
(PackageName)
select distinct name
from msdb..sysdtspackages

select @.PackageName = ''
while @.PackageName < (select max(PackageName) from #packages)
begin
select @.PackageName = min(PackageName) from #packages where PackageName > @.PackageName

select @.FileName = @.FilePath + @.PackageName + '.dts'

exec @.rc = sp_OACreate 'DTS.Package', @.objPackage output
if @.rc <> 0
begin
raiserror('failed to create package rc = %d', 16, -1, @.rc)
return
end

exec @.rc = sp_OAMethod @.objPackage, 'LoadFromSQLServer' , null,
@.ServerName = @.ServerName, @.Flags = 256, @.PackageName = @.PackageName
if @.rc <> 0
begin
raiserror('failed to load package rc = %d, package = %s', 16, -1, @.rc, @.PackageName)
return
end

-- delete old file
select @.cmd = 'del ' + @.FileName
exec master..xp_cmdshell @.cmd, no_output

exec @.rc = sp_OAMethod @.objPackage, 'SaveToStorageFile', null, @.FileName
if @.rc <> 0
begin
raiserror('failed to save package rc = %d, package = %s', 16, -1, @.rc, @.PackageName)
return
end

exec @.rc = sp_OADestroy @.objPackage
end
go