migrating DTS packages

I need to migrate dts packages on one server to structured storage files and then restore them all to another server in a different network

i got the restore method down fine, but all of the backup methods I used yield an incorrect DTS package. the only way i can get the package to look right is if I manually open and save it

i would like an automated way if possible

i tried tools from sqldts.com, wrote my own, etc... none of them work right

it is sql server 2000

thanksI need to migrate dts packages on one server to structured storage files and then restore them all to another server in a different network

i got the restore method down fine, but all of the backup methods I used yield an incorrect DTS package. the only way i can get the package to look right is if I manually open and save it

i would like an automated way if possible

i tried tools from sqldts.com, wrote my own, etc... none of them work right

it is sql server 2000

thanks

What's the code you are using for your backup method? If you do a quick search on here, you should find a post that I recently wrote on a script that backsup all DTS packages for a selected instance. I have restored from one of these with no problem (other than perhaps needing to reset a few connection properties).

Regards,

hmscott|||I cannot find your recent post regarding backing up DTS packages, could you provide me with the title of the post, or re-post the code

Here is one of the methods I used:

DECLARE @.COMMAND varchar(1000)
DECLARE @.TARGETDIR varchar(500)
SET @.TARGETDIR = 'D:\DTS\'

DECLARE c1 CURSOR FOR

SELECT distinct
'DTSRUN.EXE /S '
+ CONVERT(varchar(200), SERVERPROPERTY('servername'))
+ ' /E '
+ ' /N '
+ '"' + name + '"'
+ ' /F '
+ '"' + @.TARGETDIR + replace(name,' ','_') + '.dts"'
+ ' /!X'
FROM msdb.dbo.sysdtspackages P

OPEN c1

FETCH NEXT FROM c1
INTO @.COMMAND

WHILE @.@.FETCH_STATUS = 0
BEGIN

exec xp_cmdshell @.COMMAND, no_output

FETCH NEXT FROM c1
INTO @.COMMAND
END

CLOSE c1
DEALLOCATE c1|||Search appears to be FUBAR. I found it by scrolling through. Here it is...

http://www.dbforums.com/showthread.php?t=1607904

Regards,

hmscott|||nevermind.....|||didn't work

i made the update to this thread over here:

http://www.dbforums.com/showthread.php?p=6233426#post6233426|||I can not find my script but I swear I have done this by just moving records between msdb.dbo.sysdtspackages tables|||didn't work

i made the update to this thread over here:

http://www.dbforums.com/showthread.php?p=6233426#post6233426

It's bad form, I realize, but I posted an update to your update on the other thread. Let's agree to move the discussion there for now. Perhaps we can convince a mod to merge the threads?

Regards,

hmscott

Migiration of DB from one storage sub system to another vendor

DB is 150GB and I cannot take it offline long enough to apply logs to a back up.
I am moving from one sub storage system to another.
Any mirroring ideas?
ThanksNot sure what you mean by applying the logs? You don't need to take sql
server offline to do backups. Mabye these will help:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"eudamon" <max789@.yahoo.com> wrote in message
news:cf4ef727.0401071937.136fb59d@.posting.google.com...
> DB is 150GB and I cannot take it offline long enough to apply logs to a
back up.
> I am moving from one sub storage system to another.
> Any mirroring ideas?
> Thanks|||Standard backs would work, but then I have a existing transactions on one server with sub storage system and a restored back up to a new sub storage system DB. Plus the time is takes to copy a couple 100gb. I was hoping for some way to mirror the existing DB to a new copy on the seperate storage subsystem. Hope this makes sense|||Are you talking about just a one shot deal or do you want to keep the two up
to sync all the time? If so you can use Log Shipping or replication but in
either case you need to get the whole DB there to start and that usually
involves a copy or some sort.
--
Andrew J. Kelly SQL MVP
"eudamon" <anonymous@.discussions.microsoft.com> wrote in message
news:7E1B5A95-DFC2-4A52-958D-3F411F824569@.microsoft.com...
> Standard backs would work, but then I have a existing transactions on one
server with sub storage system and a restored back up to a new sub storage
system DB. Plus the time is takes to copy a couple 100gb. I was hoping for
some way to mirror the existing DB to a new copy on the seperate storage
subsystem. Hope this makes sense|||If your're trying to move the DB files to a new storage system on the same
server then your best bet is to detach/move/re-attach the DB. If you aren't
able to be down long enough to be able to do this I think your only option
is to mess around with filegroups. Create new file(s) in an existing
filegroup on the storage system and use DBCC SHRINKFILE('file_name',
EMPTYFILE) which will move the data from that file to other files in the
filegroup. Or create a new filegroup with new file(s) on the new storage
system and rebuild clustered indexes specifying the new filegroup, which
will cause the table to be moved. You'll actually have to rebuild all
indexes specifying ON 'filegroup' if you use this method.
I don't believe there are any options for mirroring in SQL Server. Maybe a
third-party like Veritas has something that could mirror/unmirror live
volumes.
Mike Kruchten
"eudamon" <max789@.yahoo.com> wrote in message
news:cf4ef727.0401071937.136fb59d@.posting.google.com...
> DB is 150GB and I cannot take it offline long enough to apply logs to a
back up.
> I am moving from one sub storage system to another.
> Any mirroring ideas?
> Thanks

Microsoft Windows 2000 alocation unit

Hi,
In <<SQL Server 2000 Operations Guide: Capacity and Storage Management>>
(http://www.microsoft.com/technet/pr...in/sqlops6.mspx
)
in section <<Windows NT File System (NTFS) Allocation Unit>> I read the
following:
<<The best practice for SQL Server is to choose 64 KB, because this reduces
the likelihood of I/Os that span distinct NTFS allocations, which then might
result in split I/Os.>>
How many of you have chosen the 64K allocation unit size? Did you get any
noticeable performance gains? How much worse is the default 4KB size than
64KB, when the data disk is, say, RAID10 with 128KB stripe size?
Many thanks,
OskarIn addition to my previous post: does the bigger allocation unit size really
make sense for the disk, on which you place the tempdb database?
"Oskar" wrote:

> Hi,
> In <<SQL Server 2000 Operations Guide: Capacity and Storage Management>>
> ([url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx[/ur
l])
> in section <<Windows NT File System (NTFS) Allocation Unit>> I read the
> following:
> <<The best practice for SQL Server is to choose 64 KB, because this reduce
s
> the likelihood of I/Os that span distinct NTFS allocations, which then mig
ht
> result in split I/Os.>>
> How many of you have chosen the 64K allocation unit size? Did you get any
> noticeable performance gains? How much worse is the default 4KB size than
> 64KB, when the data disk is, say, RAID10 with 128KB stripe size?
> --
> Many thanks,
> Oskar
>

Microsoft Windows 2000 alocation unit

Hi,
In <<SQL Server 2000 Operations Guide: Capacity and Storage Management>>
(http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx)
in section <<Windows NT File System (NTFS) Allocation Unit>> I read the
following:
<<The best practice for SQL Server is to choose 64 KB, because this reduces
the likelihood of I/Os that span distinct NTFS allocations, which then might
result in split I/Os.>>
How many of you have chosen the 64K allocation unit size? Did you get any
noticeable performance gains? How much worse is the default 4KB size than
64KB, when the data disk is, say, RAID10 with 128KB stripe size?
--
Many thanks,
OskarIn addition to my previous post: does the bigger allocation unit size really
make sense for the disk, on which you place the tempdb database?
"Oskar" wrote:
> Hi,
> In <<SQL Server 2000 Operations Guide: Capacity and Storage Management>>
> (http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx)
> in section <<Windows NT File System (NTFS) Allocation Unit>> I read the
> following:
> <<The best practice for SQL Server is to choose 64 KB, because this reduces
> the likelihood of I/Os that span distinct NTFS allocations, which then might
> result in split I/Os.>>
> How many of you have chosen the 64K allocation unit size? Did you get any
> noticeable performance gains? How much worse is the default 4KB size than
> 64KB, when the data disk is, say, RAID10 with 128KB stripe size?
> --
> Many thanks,
> Oskar
>

Microsoft Windows 2000 alocation unit

Hi,
In <<SQL Server 2000 Operations Guide: Capacity and Storage Management>>
(http://www.microsoft.com/technet/pro...n/sqlops6.mspx)
in section <<Windows NT File System (NTFS) Allocation Unit>> I read the
following:
<<The best practice for SQL Server is to choose 64 KB, because this reduces
the likelihood of I/Os that span distinct NTFS allocations, which then might
result in split I/Os.>>
How many of you have chosen the 64K allocation unit size? Did you get any
noticeable performance gains? How much worse is the default 4KB size than
64KB, when the data disk is, say, RAID10 with 128KB stripe size?
Many thanks,
Oskar
In addition to my previous post: does the bigger allocation unit size really
make sense for the disk, on which you place the tempdb database?
"Oskar" wrote:

> Hi,
> In <<SQL Server 2000 Operations Guide: Capacity and Storage Management>>
> (http://www.microsoft.com/technet/pro...n/sqlops6.mspx)
> in section <<Windows NT File System (NTFS) Allocation Unit>> I read the
> following:
> <<The best practice for SQL Server is to choose 64 KB, because this reduces
> the likelihood of I/Os that span distinct NTFS allocations, which then might
> result in split I/Os.>>
> How many of you have chosen the 64K allocation unit size? Did you get any
> noticeable performance gains? How much worse is the default 4KB size than
> 64KB, when the data disk is, say, RAID10 with 128KB stripe size?
> --
> Many thanks,
> Oskar
>