April 3, 2008 at 2:02 pm
Can someone help? I need to script DTS packages and jobs for disaster recovery and I would like to use SQL 2005, if possible. It seems like it would work with Visual Source safe. Does anyone know of a reference that can help with how to complete this task? Most of my DBs are 80 (SQL 2000) but I can connect to SQL 2005 management studio.
April 3, 2008 at 2:32 pm
you can save the DTS packages as a .vbs file.
for the jobs, i had to create a small vb.net console app to do it.
available at http://shivaramchalla.googlepages.com/ProjConsoleJobScripter.htm
ConsoleJobScripter.exe is a Job Scritping application that scripts out all the jobs on a server and saves all the scripts of every single job into the descination folder.
usage:
ConsoleJobScripter.exe {servername} {destination path}
I have coded it for integrated authentication only. see if thats any useful.
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
April 3, 2008 at 2:49 pm
Ed vassies product finebuild contains scripts that will script out DTS packages and jobs for you nice and simply via SQL
http://www.codeplex.com/SQLServerFineBuild
---------------------------------------------------------------------
April 3, 2008 at 3:43 pm
I've used this for migrating DTS packages saved in the MSDB to file... You need a copy of textcopy.exe and a valid Standard auth. account on the SQL server, it will not use Windows Auth.
/******************************************************************************
**
**Name: usp_Export_DTS_Packages_via_Textcopy.sql
**
**Description: Textcopy DTS packages from MSDB to Structured Storage File (*.dts).
**This preserves the formatting of the dts packages, unlike DTSBackup2000.
**
**Author: G. Rayburn
**
**Date: 09/19/2007
**
**Depends on: textcopy.exe in a valid server path.
** Standard login and password for use in the textcopy call.
** SQLAgent access to target fileshare.
**
**TODO: Errorhandling
**Add search/replace for single quotes, fwd/back
**slashes in output filename.
**
*******************************************************************************
**Modification History
*******************************************************************************
**
**Initial Creation: 09/19/2007 G. Rayburn
** 10/03/2007 G. Rayburn -- Added MAX(createdate) subquery.
**
*******************************************************************************
**
******************************************************************************/
SET NOCOUNT ON
DECLARE @DynTextCopy varchar(4000)
, @PkgName sysname
, @FileName varchar(4000)
, @FilePath varchar(256)
, @UserName sysname
, @UserPassword sysname
SET @FilePath = '\\FooServer\BarShare' -- Dest. file path.
SET @UserName = 'user' --- Standard login.
SET @UserPassword = 'password' -- Password for login.
DECLARE curTextCopy CURSOR
READ_ONLY
FOR
SELECT [name]
FROM MSDB.dbo.sysdtspackages(NOLOCK)
GROUP BY [name]
ORDER BY [name]
OPEN curTextCopy
FETCH NEXT FROM curTextCopy INTO @PkgName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @DynTextCopy = 'textcopy /S ' + @@SERVERNAME + ' /U ' + @UserName + ' /P ' + @UserPassword + ' /D MSDB /T sysdtspackages /C packagedata /W "where name = ''' + @PkgName + ''' AND createdate = (SELECT MAX(createdate) FROM msdb.dbo.sysdtspackages (NOLOCK) WHERE [name] = ''' + @PkgName + ''')"' + ' /F "' + @FilePath + '\' + @PkgName + '.dts" /O'
PRINT @DynTextCopy
PRINT ''
EXEC master..xp_cmdshell @DynTextCopy
WAITFOR DELAY '00:00:05.000'
END
FETCH NEXT FROM curTextCopy INTO @PkgName
END
CLOSE curTextCopy
DEALLOCATE curTextCopy
GO
Your friendly High-Tech Janitor... 🙂
April 3, 2008 at 3:49 pm
April 3, 2008 at 3:53 pm
The bummer about DTSBackup is that it doesn't retain the text-formatting that is in the packages...hence the reason I put together my proc.
Your friendly High-Tech Janitor... 🙂
April 4, 2008 at 11:55 am
Thanks all! The code using textcopy and xp_cmdshell to create a DTS backup is working nicely. 😀 I am storing them is Visual Source Safe and I hope to be able to connect to VSS in SQL 2005 to retrieve the files when DBs are migrated.
What about automating scripting jobs, any ideas? Sorry if I am too needy.;)
April 4, 2008 at 12:06 pm
You're quite welcome.
The script can be compiled as a procedure easy enough, just add the appropriate CREATE statement. Once you have it as a procedure in a database you can schedule a job to execute the procedure.
Your friendly High-Tech Janitor... 🙂
April 18, 2008 at 3:07 pm
Shivaram,
I downloaded the 3 executables for automating job scripting and received an error on each install that states:
Unable to install or run the application. the application requires that assembly Microsoft.SqlServer.ConnectionInfo Version 9.0.242.0 be installed in the Globbal Assembly Cashe (GAC) first. More information on this system update can be found by visiting the site here.
The it hyperlinks back to you site and I could not find information. Can you please help?
April 18, 2008 at 3:46 pm
Hi barb.wendling
Do you have SQL Server Management studio installed on your machine?
I am using SMO in this app. SMO relies on .NET Framework 2.0 and SQL Native Client.
You can find these at this URL
Look for these downloadable files based on your processor:
Microsoft SQL Server Native Client
Microsoft SQL Server 2005 Management Objects Collection
The installation wizard is trying to locate the dll file which is usually contained in this folder
C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll
Easiest way would be to just install the SSMS and that will bring along all the required files.
Let me know if that doesn't work for you.
Thanks
Shiva
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
April 21, 2008 at 9:12 am
Thanks. I am able to use the new SQL Job Scripter. Nice utility! It is quick and easy to use and saved me development time.:)
April 21, 2008 at 9:21 am
Glad the tool was useful.
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply