MOVing To SQL 2005 on Crystaltech.com

I have spent the last 2 months or so designing and modeling my new application backend in SQL Server 2005 Express edition.

I am ready to move it to a production copy of SQL Server 2005.

How do I go about this?

What kind of words can I put into google to bring some sort of results.

I have been looking in many places but no luck on how to take all the tables I have made (some with data, some not) and copy them to a live SQL 2005 Server.

Thanks in Advance for any help.

When I right-click on the Database in the Manager, I choose Tasks..

But Import / Export options are NOT THERE.

What do I do?

Why is this option not in my instance of SQL Express.

Thank you.

|||Depending on how you want the database hosted you could use detach/attach, backup/restore or scripting of schema and data.|||

How can I produce a schema? I'm assuming that is what I need to do. I am moving the database from my local machine (SQL Server Express) to a hosted SQL Server 2005 machine.

Thank you for your help,

Jon

|||You would need to write a few lines of code that call SMO.|||Jon,
I am in the same boat with my Express db. I have signed up with CrystalTech for hosting. Did you find a good solution to your problem?

Greg Lamb

Moving the Data directory in Desktop Edition

Is it possible to change the location of the databases on the desktop
edition? ThanksHave a look here: http://www.support.microsoft.com/?id=224071
--
Andrew J. Kelly SQL MVP
"boe" <boe_d@.hotmail.nospam.com> wrote in message
news:uI1dKyy3EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Is it possible to change the location of the databases on the desktop
> edition? Thanks
>|||Whoops - sorry it looks like I may asking the wrong question. If I have no
SQL databases (but SQL DE is installed) and want to change where it creates
a new one is this possible? I won't need to migrate the data just change
the default location.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uCgCX943EHA.3616@.TK2MSFTNGP11.phx.gbl...
> Have a look here: http://www.support.microsoft.com/?id=224071
> --
> Andrew J. Kelly SQL MVP
>
> "boe" <boe_d@.hotmail.nospam.com> wrote in message
> news:uI1dKyy3EHA.2180@.TK2MSFTNGP10.phx.gbl...
>> Is it possible to change the location of the databases on the desktop
>> edition? Thanks
>|||Right click on the server node in Enterprise Manager and choose Properties.
Under the database settings tab you can specify the default database
directories. You can also specify the location in the Create Database
command.
Andrew J. Kelly SQL MVP
"boe" <boe_d@.hotmail.nospam.com> wrote in message
news:uYWNZF93EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Whoops - sorry it looks like I may asking the wrong question. If I have
> no SQL databases (but SQL DE is installed) and want to change where it
> creates a new one is this possible? I won't need to migrate the data just
> change the default location.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uCgCX943EHA.3616@.TK2MSFTNGP11.phx.gbl...
>> Have a look here: http://www.support.microsoft.com/?id=224071
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "boe" <boe_d@.hotmail.nospam.com> wrote in message
>> news:uI1dKyy3EHA.2180@.TK2MSFTNGP10.phx.gbl...
>> Is it possible to change the location of the databases on the desktop
>> edition? Thanks
>>
>

Moving the Data directory in Desktop Edition

Is it possible to change the location of the databases on the desktop
edition? Thanks
Have a look here: http://www.support.microsoft.com/?id=224071
Andrew J. Kelly SQL MVP
"boe" <boe_d@.hotmail.nospam.com> wrote in message
news:uI1dKyy3EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Is it possible to change the location of the databases on the desktop
> edition? Thanks
>
|||Whoops - sorry it looks like I may asking the wrong question. If I have no
SQL databases (but SQL DE is installed) and want to change where it creates
a new one is this possible? I won't need to migrate the data just change
the default location.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uCgCX943EHA.3616@.TK2MSFTNGP11.phx.gbl...
> Have a look here: http://www.support.microsoft.com/?id=224071
> --
> Andrew J. Kelly SQL MVP
>
> "boe" <boe_d@.hotmail.nospam.com> wrote in message
> news:uI1dKyy3EHA.2180@.TK2MSFTNGP10.phx.gbl...
>
|||Right click on the server node in Enterprise Manager and choose Properties.
Under the database settings tab you can specify the default database
directories. You can also specify the location in the Create Database
command.
Andrew J. Kelly SQL MVP
"boe" <boe_d@.hotmail.nospam.com> wrote in message
news:uYWNZF93EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Whoops - sorry it looks like I may asking the wrong question. If I have
> no SQL databases (but SQL DE is installed) and want to change where it
> creates a new one is this possible? I won't need to migrate the data just
> change the default location.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uCgCX943EHA.3616@.TK2MSFTNGP11.phx.gbl...
>

moving tables

Hi,
ms-sqlserver 2000 sp4 - entrerprise edition
Can you help me on this issue. I would like to move some user
tables(about 100 MB) to new file group. Can you tell me what I need to
do?
Thanks in advance,If you have clustered index on all the tables then just recreate the
clustered and non-clustered indexes and specify the new filegroup. Else
you can bcp out the data and then create the table in new file group and bcp
in the data.
"he_msql" wrote:
> Hi,
> ms-sqlserver 2000 sp4 - entrerprise edition
> Can you help me on this issue. I would like to move some user
> tables(about 100 MB) to new file group. Can you tell me what I need to
> do?
> Thanks in advance,
>|||Hi,
Due to the details of moving tables (i.e. moving constraints, etc), it is
considered best practice to allow Enterprise Manager do the work for you.
Enterprise Manager knows what to move. I haven't found a stored procedure to
do the trick, so you will have deal with the GUI of Enterprise Manager. If
you happen to know one, please post.
This subject is located in the Books Online under 'filegroups:switching
filegroup for table'. Here is the procedure...
How to place an existing table on a different filegroup (Enterprise Manager)
To place an existing table on a different filegroup
1. Expand a server group, and then expand a server.
2. Expand Databases, expand the database in which the table belongs, and
then click Tables.
3. In the details pane, right-click the table, and then click Design Table.
4. Right-click any column, and then click Properties.
5. On the Tables tab, in the Table Filegroup list, select the filegroup on
which to place the table.
6. Optionally, in the Text Filegroup list, select a filegroup on which to
place any text, image, and ntext columns.
HTH,
Adam
"he_msql" wrote:
> Hi,
> ms-sqlserver 2000 sp4 - entrerprise edition
> Can you help me on this issue. I would like to move some user
> tables(about 100 MB) to new file group. Can you tell me what I need to
> do?
> Thanks in advance,
>|||If the tables are defined relations (i.e. Foreign Key constraints), then
it is probably easiest to use Enterprise Manager to do the work and/or
create the script to do the work.
If there are no tables referencing the table you want to move, and if
this table has a clustered index, then the most efficient way to move
it, is to recreate the clustered index with the original name and
definition with "CREATE INDEX ... ON ...(...) WITH DROP_EXISTING ON
[your target filegroup]"
The same method can be used for nonclustered indexes.
If the table is a heap, then create a clustered index ON [your target
filegroup] and drop the clustered index afterwards.
HTH,
Gert-Jan
he_msql wrote:
> Hi,
> ms-sqlserver 2000 sp4 - entrerprise edition
> Can you help me on this issue. I would like to move some user
> tables(about 100 MB) to new file group. Can you tell me what I need to
> do?
> Thanks in advance,

moving system databases

Hi all.
Long story, I have a clustered SQL Server 2005 Standard Edition that is
using a SAN. We have just added more drives to our SAN and I need to move
the system database off the current drive to another drive. I have read some
others that have too have asked the same question. And Article ID 224071
keeps coming up. Well I just tried following this article this morning on my
beta server (SQL 2005 Dev Edition) to ensure things would go smoothly on
production and after I tried changing the startup parameters and tried
starting SQL server instance backup I received the following error.
FCB::Open: Operating system error 2(The system cannot find the file
specified.) occurred while creating or opening file 'F:\Program
Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf -c -m -T3608'.
Diagnose and correct the operating system error, and retry the operation.
And after a Google search I ran across an article out on MSDN, saying that
you need to use a ';' in between the parameters.
http://msdn2.microsoft.com/en-us/library/ms190699.aspx
I had to use SQL Server Configuration Manager to change the startup
parameters because there was no 'startup parameters' under the general tab
under Enterprise Manager (I had 2 other people check the steps out too and
they couldn't find it either).
Did I do something wrong? Should there be ';' between the parameters or not?
Thanks in advance!
Kris
If it helps, my SQL 2005 startup parameters from teh Config manager:
-dC:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
Or check in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.1\MSSQLServer\Parameters
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"Kris McCarty" <Kris McCarty@.discussions.microsoft.com> wrote in message
news:AE19C53B-7F0E-4A33-AF14-85748AAF7559@.microsoft.com...
> Hi all.
> Long story, I have a clustered SQL Server 2005 Standard Edition that is
> using a SAN. We have just added more drives to our SAN and I need to move
> the system database off the current drive to another drive. I have read
> some
> others that have too have asked the same question. And Article ID 224071
> keeps coming up. Well I just tried following this article this morning on
> my
> beta server (SQL 2005 Dev Edition) to ensure things would go smoothly on
> production and after I tried changing the startup parameters and tried
> starting SQL server instance backup I received the following error.
> FCB::Open: Operating system error 2(The system cannot find the file
> specified.) occurred while creating or opening file 'F:\Program
> Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf -c -m -T3608'.
> Diagnose and correct the operating system error, and retry the operation.
> And after a Google search I ran across an article out on MSDN, saying that
> you need to use a ';' in between the parameters.
> http://msdn2.microsoft.com/en-us/library/ms190699.aspx
> I had to use SQL Server Configuration Manager to change the startup
> parameters because there was no 'startup parameters' under the general tab
> under Enterprise Manager (I had 2 other people check the steps out too and
> they couldn't find it either).
> Did I do something wrong? Should there be ';' between the parameters or
> not?
> Thanks in advance!
> Kris
>
|||OK by adding in the ';' between the parameters via Config Manager I am now
getting the following message when I try to connect to the server after I
restart the instance backup.
Cannot connect to 'servername'
Login failed for user 'sa'. Reason: Server is in single user mode. Only
one administrator can connect at this time. (Microsoft SQL Server, Error:
18461)
I even went to the length to unplugg the network cable and restart with
server off the network and I still received the same error. Only database I
was moving at the time was 'Model' using the procedures of Article ID 224071.
Doing another search I found a tech net artilce telling of another want to
move all system databases other then master and resources
http://technet.microsoft.com/en-us/library/ms345408.aspx
, which is different then 224071. Any ideas and help is great!
Thanks again!
Kris
"Tibor Karaszi" wrote:

> It depend son what tool you are using. In Config Manager: Yes.
> In RegEdit: No, since each parameter has it's own key.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kris McCarty" <Kris McCarty@.discussions.microsoft.com> wrote in message
> news:AE19C53B-7F0E-4A33-AF14-85748AAF7559@.microsoft.com...
>

moving system databases

Hi all.
Long story, I have a clustered SQL Server 2005 Standard Edition that is
using a SAN. We have just added more drives to our SAN and I need to move
the system database off the current drive to another drive. I have read som
e
others that have too have asked the same question. And Article ID 224071
keeps coming up. Well I just tried following this article this morning on m
y
beta server (SQL 2005 Dev Edition) to ensure things would go smoothly on
production and after I tried changing the startup parameters and tried
starting SQL server instance backup I received the following error.
FCB::Open: Operating system error 2(The system cannot find the file
specified.) occurred while creating or opening file 'F:\Program
Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf -c -m -T3608'.
Diagnose and correct the operating system error, and retry the operation.
And after a Google search I ran across an article out on MSDN, saying that
you need to use a ';' in between the parameters.
http://msdn2.microsoft.com/en-us/library/ms190699.aspx
I had to use SQL Server Configuration Manager to change the startup
parameters because there was no 'startup parameters' under the general tab
under Enterprise Manager (I had 2 other people check the steps out too and
they couldn't find it either).
Did I do something wrong? Should there be ';' between the parameters or not
?
Thanks in advance!
KrisIf it helps, my SQL 2005 startup parameters from teh Config manager:
-dC:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
Or check in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL
Server\MSSQL.1\MSSQLServer\Parameters
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"Kris McCarty" <Kris McCarty@.discussions.microsoft.com> wrote in message
news:AE19C53B-7F0E-4A33-AF14-85748AAF7559@.microsoft.com...
> Hi all.
> Long story, I have a clustered SQL Server 2005 Standard Edition that is
> using a SAN. We have just added more drives to our SAN and I need to move
> the system database off the current drive to another drive. I have read
> some
> others that have too have asked the same question. And Article ID 224071
> keeps coming up. Well I just tried following this article this morning on
> my
> beta server (SQL 2005 Dev Edition) to ensure things would go smoothly on
> production and after I tried changing the startup parameters and tried
> starting SQL server instance backup I received the following error.
> FCB::Open: Operating system error 2(The system cannot find the file
> specified.) occurred while creating or opening file 'F:\Program
> Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf -c -m -T3608'.
> Diagnose and correct the operating system error, and retry the operation.
> And after a Google search I ran across an article out on MSDN, saying that
> you need to use a ';' in between the parameters.
> http://msdn2.microsoft.com/en-us/library/ms190699.aspx
> I had to use SQL Server Configuration Manager to change the startup
> parameters because there was no 'startup parameters' under the general tab
> under Enterprise Manager (I had 2 other people check the steps out too and
> they couldn't find it either).
> Did I do something wrong? Should there be ';' between the parameters or
> not?
> Thanks in advance!
> Kris
>|||> Should there be ';' between the parameters or not?
It depend son what tool you are using. In Config Manager: Yes.
In RegEdit: No, since each parameter has it's own key.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kris McCarty" <Kris McCarty@.discussions.microsoft.com> wrote in message
news:AE19C53B-7F0E-4A33-AF14-85748AAF7559@.microsoft.com...
> Hi all.
> Long story, I have a clustered SQL Server 2005 Standard Edition that is
> using a SAN. We have just added more drives to our SAN and I need to move
> the system database off the current drive to another drive. I have read s
ome
> others that have too have asked the same question. And Article ID 224071
> keeps coming up. Well I just tried following this article this morning on
my
> beta server (SQL 2005 Dev Edition) to ensure things would go smoothly on
> production and after I tried changing the startup parameters and tried
> starting SQL server instance backup I received the following error.
> FCB::Open: Operating system error 2(The system cannot find the file
> specified.) occurred while creating or opening file 'F:\Program
> Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf -c -m -T3608'.
> Diagnose and correct the operating system error, and retry the operation.
> And after a Google search I ran across an article out on MSDN, saying that
> you need to use a ';' in between the parameters.
> http://msdn2.microsoft.com/en-us/library/ms190699.aspx
> I had to use SQL Server Configuration Manager to change the startup
> parameters because there was no 'startup parameters' under the general tab
> under Enterprise Manager (I had 2 other people check the steps out too and
> they couldn't find it either).
> Did I do something wrong? Should there be ';' between the parameters or n
ot?
> Thanks in advance!
> Kris
>|||OK by adding in the ';' between the parameters via Config Manager I am now
getting the following message when I try to connect to the server after I
restart the instance backup.
Cannot connect to 'servername'
Login failed for user 'sa'. Reason: Server is in single user mode. Only
one administrator can connect at this time. (Microsoft SQL Server, Error:
18461)
I even went to the length to unplugg the network cable and restart with
server off the network and I still received the same error. Only database I
was moving at the time was 'Model' using the procedures of Article ID 224071
.
Doing another search I found a tech net artilce telling of another want to
move all system databases other then master and resources
http://technet.microsoft.com/en-us/...y/ms345408.aspx
, which is different then 224071. Any ideas and help is great!
Thanks again!
Kris
"Tibor Karaszi" wrote:

> It depend son what tool you are using. In Config Manager: Yes.
> In RegEdit: No, since each parameter has it's own key.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kris McCarty" <Kris McCarty@.discussions.microsoft.com> wrote in message
> news:AE19C53B-7F0E-4A33-AF14-85748AAF7559@.microsoft.com...
>

moving system databases

Hi all.
Long story, I have a clustered SQL Server 2005 Standard Edition that is
using a SAN. We have just added more drives to our SAN and I need to move
the system database off the current drive to another drive. I have read some
others that have too have asked the same question. And Article ID 224071
keeps coming up. Well I just tried following this article this morning on my
beta server (SQL 2005 Dev Edition) to ensure things would go smoothly on
production and after I tried changing the startup parameters and tried
starting SQL server instance backup I received the following error.
FCB::Open: Operating system error 2(The system cannot find the file
specified.) occurred while creating or opening file 'F:\Program
Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf -c -m -T3608'.
Diagnose and correct the operating system error, and retry the operation.
And after a Google search I ran across an article out on MSDN, saying that
you need to use a ';' in between the parameters.
http://msdn2.microsoft.com/en-us/library/ms190699.aspx
I had to use SQL Server Configuration Manager to change the startup
parameters because there was no 'startup parameters' under the general tab
under Enterprise Manager (I had 2 other people check the steps out too and
they couldn't find it either).
Did I do something wrong? Should there be ';' between the parameters or not?
Thanks in advance!
KrisIf it helps, my SQL 2005 startup parameters from teh Config manager:
-dC:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
Or check in the registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\MSSQL.1\MSSQLServer\Parameters
--
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"Kris McCarty" <Kris McCarty@.discussions.microsoft.com> wrote in message
news:AE19C53B-7F0E-4A33-AF14-85748AAF7559@.microsoft.com...
> Hi all.
> Long story, I have a clustered SQL Server 2005 Standard Edition that is
> using a SAN. We have just added more drives to our SAN and I need to move
> the system database off the current drive to another drive. I have read
> some
> others that have too have asked the same question. And Article ID 224071
> keeps coming up. Well I just tried following this article this morning on
> my
> beta server (SQL 2005 Dev Edition) to ensure things would go smoothly on
> production and after I tried changing the startup parameters and tried
> starting SQL server instance backup I received the following error.
> FCB::Open: Operating system error 2(The system cannot find the file
> specified.) occurred while creating or opening file 'F:\Program
> Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf -c -m -T3608'.
> Diagnose and correct the operating system error, and retry the operation.
> And after a Google search I ran across an article out on MSDN, saying that
> you need to use a ';' in between the parameters.
> http://msdn2.microsoft.com/en-us/library/ms190699.aspx
> I had to use SQL Server Configuration Manager to change the startup
> parameters because there was no 'startup parameters' under the general tab
> under Enterprise Manager (I had 2 other people check the steps out too and
> they couldn't find it either).
> Did I do something wrong? Should there be ';' between the parameters or
> not?
> Thanks in advance!
> Kris
>|||> Should there be ';' between the parameters or not?
It depend son what tool you are using. In Config Manager: Yes.
In RegEdit: No, since each parameter has it's own key.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kris McCarty" <Kris McCarty@.discussions.microsoft.com> wrote in message
news:AE19C53B-7F0E-4A33-AF14-85748AAF7559@.microsoft.com...
> Hi all.
> Long story, I have a clustered SQL Server 2005 Standard Edition that is
> using a SAN. We have just added more drives to our SAN and I need to move
> the system database off the current drive to another drive. I have read some
> others that have too have asked the same question. And Article ID 224071
> keeps coming up. Well I just tried following this article this morning on my
> beta server (SQL 2005 Dev Edition) to ensure things would go smoothly on
> production and after I tried changing the startup parameters and tried
> starting SQL server instance backup I received the following error.
> FCB::Open: Operating system error 2(The system cannot find the file
> specified.) occurred while creating or opening file 'F:\Program
> Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf -c -m -T3608'.
> Diagnose and correct the operating system error, and retry the operation.
> And after a Google search I ran across an article out on MSDN, saying that
> you need to use a ';' in between the parameters.
> http://msdn2.microsoft.com/en-us/library/ms190699.aspx
> I had to use SQL Server Configuration Manager to change the startup
> parameters because there was no 'startup parameters' under the general tab
> under Enterprise Manager (I had 2 other people check the steps out too and
> they couldn't find it either).
> Did I do something wrong? Should there be ';' between the parameters or not?
> Thanks in advance!
> Kris
>|||OK by adding in the ';' between the parameters via Config Manager I am now
getting the following message when I try to connect to the server after I
restart the instance backup.
Cannot connect to 'servername'
Login failed for user 'sa'. Reason: Server is in single user mode. Only
one administrator can connect at this time. (Microsoft SQL Server, Error:
18461)
I even went to the length to unplugg the network cable and restart with
server off the network and I still received the same error. Only database I
was moving at the time was 'Model' using the procedures of Article ID 224071.
Doing another search I found a tech net artilce telling of another want to
move all system databases other then master and resources
http://technet.microsoft.com/en-us/library/ms345408.aspx
, which is different then 224071. Any ideas and help is great!
Thanks again!
Kris
"Tibor Karaszi" wrote:
> > Should there be ';' between the parameters or not?
> It depend son what tool you are using. In Config Manager: Yes.
> In RegEdit: No, since each parameter has it's own key.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kris McCarty" <Kris McCarty@.discussions.microsoft.com> wrote in message
> news:AE19C53B-7F0E-4A33-AF14-85748AAF7559@.microsoft.com...
> > Hi all.
> >
> > Long story, I have a clustered SQL Server 2005 Standard Edition that is
> > using a SAN. We have just added more drives to our SAN and I need to move
> > the system database off the current drive to another drive. I have read some
> > others that have too have asked the same question. And Article ID 224071
> > keeps coming up. Well I just tried following this article this morning on my
> > beta server (SQL 2005 Dev Edition) to ensure things would go smoothly on
> > production and after I tried changing the startup parameters and tried
> > starting SQL server instance backup I received the following error.
> >
> > FCB::Open: Operating system error 2(The system cannot find the file
> > specified.) occurred while creating or opening file 'F:\Program
> > Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf -c -m -T3608'.
> > Diagnose and correct the operating system error, and retry the operation.
> >
> > And after a Google search I ran across an article out on MSDN, saying that
> > you need to use a ';' in between the parameters.
> > http://msdn2.microsoft.com/en-us/library/ms190699.aspx
> >
> > I had to use SQL Server Configuration Manager to change the startup
> > parameters because there was no 'startup parameters' under the general tab
> > under Enterprise Manager (I had 2 other people check the steps out too and
> > they couldn't find it either).
> >
> > Did I do something wrong? Should there be ';' between the parameters or not?
> >
> > Thanks in advance!
> > Kris
> >
>

Moving sql 2000 DTS packages to sql 2005 standard edition

I have been tasked with upgrading around 150 SQL Server 2000 DTS packages to SSIS in SQL Server 2005 standard edition. I made a backup of the 2000 database upon which the DTS packages operate and restored it to the SQL 2005 server. So far, so good. I have the database in place. Now I need to get the DTS packages themselves into the SLQ 2005 server. I think I need to check my install and make sure that I have the SQL Server 2000 DTS services installed on the SQL 2005 server. I can do that.

However, I wonder what would be the most effective way to physically get the packages from the SQL 2000 server to the SQL 2005 server. Should I use structured storage files? If so, how do I go about opening them in SQL 2005 in order to save them to SQL server 2005?

I should mention that these packages make heavy use of ActiveX scripting so I am looking at rewriting them from scratch to be SSIS packages. I just need the packages on the SQL Server 2005 box so I can make sure I am creating exactly the same functionality in 2005 as existed in SQL server 2000. Each DTS 2000 individual package tends to be fairly simple and I think I can greatly improve the process by consolidating them.

Thanks in advance for any advice.

S. Wells

You'll need the DTS2000 editor by teh sounds of it. Downloadable from here: http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

-Jamie

|||

Thanks Jamie,

It does appear that this will give me what I need. I had read another, earlier, post on the forum that indicated the legacy components did not have to be separately installed now that 2005 is in production release if one had chosen to install integration services. I did not realize that the designer was not installed as part of the legacy DTS components install. I looked all over the BI interface and could not see where I could open a DTS package and then save it to SQL 2005.

Again, thanks.

S. Wells

|||

Just a quick note to say that there is a new version (an update to the previous link):

Instead of:

Feature Pack for Microsoft SQL Server 2005 - November 2005
http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

Download from:

Feature Pack for Microsoft SQL Server 2005 - April 2006
http://www.microsoft.com/downloads/details.aspx?FamilyID=df0ba5aa-b4bd-4705-aa0a-b477ba72a9cb&DisplayLang=en

6th item down:

Microsoft SQL Server 2000 DTS Designer Components

The Microsoft SQL Server 2000 Data Transformation Services (DTS) package designer is a design tool used by developers and administrators of SQL Server 2005 servers to edit and maintain existing DTS packages until they can be upgraded or recreated in the SQL Server 2005 Integration Services package format. After installing this download, SQL Server 2005 users can continue to edit and maintain existing DTS packages from the Object Explorer in SQL Server 2005 Management Studio and from the Execute DTS 2000 Package Task Editor in Business Intelligence Development Studio, without needing to reinstall the SQL Server 2000 tools. The DTS package designer in this download was formerly accessed from the Data Transformation Services node in SQL Server 2000 Enterprise Manager.
Audience(s): Customer, Developer
X86 Package (SQLServer2005_DTS.msi) - 5088 KB

Also look at

SQL Server 2005 Books Online

SQL Server 2005 Integration Services Backward Compatibility
Updated: 17 July 2006
http://msdn2.microsoft.com/en-us/library/ms143706.aspx

General Compatibility between DTS and SSIS
Because Integration Services is an entirely new product, and not a version upgrade, you will not encounter software conflicts between DTS and SSIS.

You can install both DTS and SSIS on the same server.
You can run both DTS and SSIS packages on the same server when both products are installed. You can run DTS packages, even when the SQL Server 2000 tools are not present, by using an updated version of the DTS runtime that is installed with Integration Services.
You can save DTS packages in the MSDB database of a SQL Server 2000 or SQL Server 2005 instance. You can save SSIS packages only on a SQL Server 2005 instance.
You can edit DTS packages, even when the SQL Server 2000 tools are not present, by using an updated version of the DTS Designer that is available for download. You cannot edit DTS packages in Business Intelligence Development Studio. You can edit SSIS packages only in BI Development Studio.

Moving msdb and model database to a standby machine

Hi
We are planning to do a hardware upgrade for our SQL 2000 STD edition.
I have to activate my standby database.
I am only restoring the standby for my Production database (tg)
But now I need to copy model,msdb DB also to get the jobs.
Can I drop and create the DB from Production using restore methods.
Please let me know.
Tks
Mangesh
"But now I need to copy model..." WHY? model is empty database, template for
new database...
you can restore msdb if you use same version (and sp) and collation on
standby server
1. stop sql agent
2. restore msdb
3. start agent and check agent error log...
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Mangesh Deshpande" wrote:

> Hi
> We are planning to do a hardware upgrade for our SQL 2000 STD edition.
> I have to activate my standby database.
> I am only restoring the standby for my Production database (tg)
> But now I need to copy model,msdb DB also to get the jobs.
> Can I drop and create the DB from Production using restore methods.
> Please let me know.
> Tks
> Mangesh

Moving msdb and model database to a standby machine

Hi
We are planning to do a hardware upgrade for our SQL 2000 STD edition.
I have to activate my standby database.
I am only restoring the standby for my Production database (tg)
But now I need to copy model,msdb DB also to get the jobs.
Can I drop and create the DB from Production using restore methods.
Please let me know.
Tks
Mangesh"But now I need to copy model..." WHY? model is empty database, template for
new database...
you can restore msdb if you use same version (and sp) and collation on
standby server
1. stop sql agent
2. restore msdb
3. start agent and check agent error log...
--
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Mangesh Deshpande" wrote:
> Hi
> We are planning to do a hardware upgrade for our SQL 2000 STD edition.
> I have to activate my standby database.
> I am only restoring the standby for my Production database (tg)
> But now I need to copy model,msdb DB also to get the jobs.
> Can I drop and create the DB from Production using restore methods.
> Please let me know.
> Tks
> Mangesh

Moving msdb and model database to a standby machine

Hi
We are planning to do a hardware upgrade for our SQL 2000 STD edition.
I have to activate my standby database.
I am only restoring the standby for my Production database (tg)
But now I need to copy model,msdb DB also to get the jobs.
Can I drop and create the DB from Production using restore methods.
Please let me know.
Tks
Mangesh"But now I need to copy model..." WHY? model is empty database, template fo
r
new database...
you can restore msdb if you use same version (and sp) and collation on
standby server
1. stop sql agent
2. restore msdb
3. start agent and check agent error log...
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Mangesh Deshpande" wrote:

> Hi
> We are planning to do a hardware upgrade for our SQL 2000 STD edition.
> I have to activate my standby database.
> I am only restoring the standby for my Production database (tg)
> But now I need to copy model,msdb DB also to get the jobs.
> Can I drop and create the DB from Production using restore methods.
> Please let me know.
> Tks
> Mangesh