DTS & JOBS BACKUP

Question

DTS & JOBS BACKUP

Ram:)

SSCrazy

Points: 2246
More actions
July 2, 2010 at 1:28 pm

#226411

Hi,
I need clarification on the procedure what I am following to copy all dts packages and jobs..
I am exporting data from
sysjobschedules, sysjobhistory, sysjobs, sysjobservers, sysjobsteps tables in msdb database to .txt file and importing to destination msdb database.. same as to dts packages.
For time being it is working fine..
But I am not sure whether this is wright..
Please suggest
🙂

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

What is the purpose of the copy. for DR?

As this is SQL 2000 you can get away with backing up the source msdb database, restoring it over the target msdb database and then running:

update msdb..sysjobs set originating server = 'target server name'

---------------------------------------------------------------------

Ram:) SSCrazy Points: 2246 More actions · Answer 2

I agree with you..

But I don't know what happend the existing dts packages and jobs in destination database if restore the backup..

Please suggest

🙂

Steve Jones - SSC Editor SSC Guru Points: 739010 More actions · Answer 3

The packages are stored in msdb, just like jobs, so they'll be there.

However, if you have coded in a server name instead of (local) or 127.0.0.1, then they might need to be edited to work.

george sibbald SSC Guru Points: 104210 More actions · Answer 4

If you have existing jobs and packages in the destination database they would be overwritten, so if you want to keep them restore is out.

so for jobs keep to what you are doing. This scripts outputs all DTS packages to file:

if exists (select 1 from sysobjects where name = 'usp_backupDTS' and type = 'P')

drop proc usp_backupDTS

go

CREATE PROC dbo.usp_backupDTS

/***********************************************************************************

usp_backupDTS

Creates a set of SQL statements, each of which will backup one package to a

structured storage file (.DTS) in a special backup directory for DR purposes..

-----------------------------------------------------------------------------------

CHANGE HISTORY

-----------------------------------------------------------------------------------

DATE WHO COMMENT

---------- ----------- ------------------------------------------------------------

27/02/2007 Carl Start.

***********************************************************************************/

AS

SET NOCOUNT ON

DECLARE @TARGETDIR varchar(1000), @SQL varchar(300)

CREATE TABLE #SQL (SQLStatement varchar(300))

SET @TARGETDIR = 'e:\server\data\mssql\dba_scripts\disrec\dts\'

INSERT INTO #SQL

SELECT distinct

'exec master.dbo.xp_cmdshell ' + '''DTSRUN.EXE /S '

+ CONVERT(varchar(200), SERVERPROPERTY('servername'))

+ ' /E '

+ ' /N '

+ '"' + name + '"'

+ ' /F '

+ '"' + @TARGETDIR + name + '.dts"'

+ ' /!X' + ''''

FROM msdb.dbo.sysdtspackages P

select * from #SQL

-----------------------------------------------------------------------------------

-- Initialize

-----------------------------------------------------------------------------------

DECLARE Command CURSOR FOR

SELECT SQLStatement

FROM #SQL

OPEN Command

FETCH NEXT FROM Command INTO @SQL

-----------------------------------------------------------------------------------

-- Cursor Loop Start

-----------------------------------------------------------------------------------

WHILE @@FETCH_STATUS = 0

BEGIN

--print @SQL

execute (@SQL)

FETCH NEXT FROM Command INTO @SQL

END

-----------------------------------------------------------------------------------

-- Cursor Loop End

-----------------------------------------------------------------------------------

close Command

deallocate Command

-----------------------------------------------------------------------------------

-- Finalize

-----------------------------------------------------------------------------------

drop table #SQL

grant all on dbo.usp_backupDTS to public

GO

---------------------------------------------------------------------