Migrating From Development to Production Environment in MSAS 2005

Hi,

I have built a project in Ananlysis Services 2005 and would like to migrate the contents of the Project ( Dimensions, Measures, Cubes Etc) to a New server having analysis Services 2005 , what is the best approach. Does microsoft provide any best practices document. I searched the forum and the topics on migration seem to be from Analysis Services 2000 to Analysis Services 2005, can some one guide me on migration between Analysis Services 2005 and 2005.

Thansks

A backup and restore (like in AS2000) is certainly possible. This is the only method if you need to get the data cube moved in a processed state (with data). Except you could use the new synchronization feature, but that would probably be using this feature for something it was not quite intended.

The recommended method would be using the new AS Deployment Wizard. Make a "build" in BI Development Studio of your database and fire up the AS Deployment Wizard. This will allow you to specify different settings for your cube database when it is deployed to the production server. For more info on how to use the Deployment Wizard, see http://msdn2.microsoft.com/en-US/library/ms176121.aspx

Migrating DTS packages on instances

I have problem and don't know how to resolve it.

I'm running multiple instances(developpement,acceptation, production) on one server with the same db's on each one of them.

Now I'm trying to find out a way to transfer DTS packages from one instance to another (I know it is straight-forward if you don't have to change anything) but in my case I would want to change the connection properties within the dts package e.g.

on my server\developpement instance I've created a DTS package with connection properties server name server\developpement

Now I want to transfer it to my server\acceptation instance and there I want the connection properties server name to be server\acceptation .

I've tried doing it with a dynamic properties task but I can't seem to find the instance name where the package is saved. I can only find the server name(environment variable).

This of course is no problem if you have but a few packages, but if you have over 50 to transfer

Any help would be more than welcome.

Rosko

PS: I know it is possible between two different servers using (local) as the servername but that's not an option for my boss.I have a very similar situation. I currently have global variables. When I migrate, I am changing this from Package Properties.

If I do not find a solution, I plan to have a flat file and read the information from that file using ActiveScript.

Please let me know if you have a better solution.|||See

"LoadFromSQLServer' & 'SaveToSQLServer'
in SQLServer Books online or on the web.
this can be called in VB and will repopulate on the other server.

For more advanced changes the attached structe can be populated and manipulated in VB to create new DTS Packages.|||Correct me if I am wrong. I think this still does not address the issue.

The issue is to set the datasource property to the connections within the package(s) while the package is migrated from one instance to another.

LoadFromSQLServer and SaveToSQLServer seem to help in migrating from one instance to another.|||Unfortunately jayaramanc,

The connections held in the connections object must also be updated with the new server name, as does any ADODB connections in ActiveXScript in the Steps object.

MIgrating DTS packages from one machine to another

I am building a new machine (SQL server 2000 SP3a on Windows 2003 R2) for an
exisitng production michine (SQL server 2000 SP3a on Windows 2000) with 27
user databases. I can move databases, logins, scheduled jobs. I have lot of
DTS packages and Maintenance Plans. Is there an easy way to recreate these
DTS packages and Maintenance plans on the new server with out creating each
one of them manually?
--
RKYou can open the package on one server, using DTS Designer, and then using
[Package], [Save As], save it to a different server.
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:F5FA74AD-DFFC-4C10-AC6D-CED5F8DE6203@.microsoft.com...
>I am building a new machine (SQL server 2000 SP3a on Windows 2003 R2) for
>an
> exisitng production michine (SQL server 2000 SP3a on Windows 2000) with 27
> user databases. I can move databases, logins, scheduled jobs. I have lot
> of
> DTS packages and Maintenance Plans. Is there an easy way to recreate these
> DTS packages and Maintenance plans on the new server with out creating
> each
> one of them manually?
> --
> RK|||Arnie Rowland wrote:[vbcol=seagreen]
> You can open the package on one server, using DTS Designer, and then using
> [Package], [Save As], save it to a different server.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:F5FA74AD-DFFC-4C10-AC6D-CED5F8DE6203@.microsoft.com...
http://www.sqldts.com/?204
Regards
Amish Shah

MIgrating DTS packages from one machine to another

I am building a new machine (SQL server 2000 SP3a on Windows 2003 R2) for an
exisitng production michine (SQL server 2000 SP3a on Windows 2000) with 27
user databases. I can move databases, logins, scheduled jobs. I have lot of
DTS packages and Maintenance Plans. Is there an easy way to recreate these
DTS packages and Maintenance plans on the new server with out creating each
one of them manually?
--
RKYou can open the package on one server, using DTS Designer, and then using
[Package], [Save As], save it to a different server.
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"RK73" <RK73@.discussions.microsoft.com> wrote in message
news:F5FA74AD-DFFC-4C10-AC6D-CED5F8DE6203@.microsoft.com...
>I am building a new machine (SQL server 2000 SP3a on Windows 2003 R2) for
>an
> exisitng production michine (SQL server 2000 SP3a on Windows 2000) with 27
> user databases. I can move databases, logins, scheduled jobs. I have lot
> of
> DTS packages and Maintenance Plans. Is there an easy way to recreate these
> DTS packages and Maintenance plans on the new server with out creating
> each
> one of them manually?
> --
> RK|||Arnie Rowland wrote:
> You can open the package on one server, using DTS Designer, and then using
> [Package], [Save As], save it to a different server.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "RK73" <RK73@.discussions.microsoft.com> wrote in message
> news:F5FA74AD-DFFC-4C10-AC6D-CED5F8DE6203@.microsoft.com...
> >I am building a new machine (SQL server 2000 SP3a on Windows 2003 R2) for
> >an
> > exisitng production michine (SQL server 2000 SP3a on Windows 2000) with 27
> > user databases. I can move databases, logins, scheduled jobs. I have lot
> > of
> > DTS packages and Maintenance Plans. Is there an easy way to recreate these
> > DTS packages and Maintenance plans on the new server with out creating
> > each
> > one of them manually?
> > --
> > RK
http://www.sqldts.com/?204
Regards
Amish Shah

Migrating DTS packages and SQL Jobs

Hello All,
We are in the process of migrating our SQLServer2000 production server to a
better hardware and Windows 2003. We have lot of DTS packages and SQL Jobs
that needs to be migrated as well. I can script the Jobs and run the script
on the new server and similarly I can save the DTS package on the new server.
Instead, Can I backup the msdb database and restore it on the new server. I
was wondering which method would be the best.
Thanks,
rgnRestoring MSDB is certainly an option but if you have anything that
references the original server name directly you may have to fix that.
These links may help:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system DB's 2000
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://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
Moving Users
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
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
http://www.sqlservercentral.com/scripts/contributions/1598.asp Script
Roles and Permissions
--
Andrew J. Kelly SQL MVP
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:798481FC-6AD6-461C-A554-AF99EBD5B3E7@.microsoft.com...
> Hello All,
> We are in the process of migrating our SQLServer2000 production server to
> a
> better hardware and Windows 2003. We have lot of DTS packages and SQL Jobs
> that needs to be migrated as well. I can script the Jobs and run the
> script
> on the new server and similarly I can save the DTS package on the new
> server.
> Instead, Can I backup the msdb database and restore it on the new server.
> I
> was wondering which method would be the best.
> Thanks,
> rgn

Migrating DTS packages and SQL Jobs

Hello All,
We are in the process of migrating our SQLServer2000 production server to a
better hardware and Windows 2003. We have lot of DTS packages and SQL Jobs
that needs to be migrated as well. I can script the Jobs and run the script
on the new server and similarly I can save the DTS package on the new server
.
Instead, Can I backup the msdb database and restore it on the new server. I
was wondering which method would be the best.
Thanks,
rgnRestoring MSDB is certainly an option but if you have anything that
references the original server name directly you may have to fix that.
These links may help:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system DB's 2000
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://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/col...se
s.asp
Moving Users
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/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
http://www.sqlservercentral.com/scr...utions/1598.asp Script
Roles and Permissions
Andrew J. Kelly SQL MVP
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:798481FC-6AD6-461C-A554-AF99EBD5B3E7@.microsoft.com...
> Hello All,
> We are in the process of migrating our SQLServer2000 production server to
> a
> better hardware and Windows 2003. We have lot of DTS packages and SQL Jobs
> that needs to be migrated as well. I can script the Jobs and run the
> script
> on the new server and similarly I can save the DTS package on the new
> server.
> Instead, Can I backup the msdb database and restore it on the new server.
> I
> was wondering which method would be the best.
> Thanks,
> rgn

Migrating DTS packages and SQL Jobs

Hello All,
We are in the process of migrating our SQLServer2000 production server to a
better hardware and Windows 2003. We have lot of DTS packages and SQL Jobs
that needs to be migrated as well. I can script the Jobs and run the script
on the new server and similarly I can save the DTS package on the new server.
Instead, Can I backup the msdb database and restore it on the new server. I
was wondering which method would be the best.
Thanks,
rgn
Restoring MSDB is certainly an option but if you have anything that
references the original server name directly you may have to fix that.
These links may help:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system DB's 2000
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://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
Moving Users
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
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
http://www.sqlservercentral.com/scripts/contributions/1598.asp Script
Roles and Permissions
Andrew J. Kelly SQL MVP
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:798481FC-6AD6-461C-A554-AF99EBD5B3E7@.microsoft.com...
> Hello All,
> We are in the process of migrating our SQLServer2000 production server to
> a
> better hardware and Windows 2003. We have lot of DTS packages and SQL Jobs
> that needs to be migrated as well. I can script the Jobs and run the
> script
> on the new server and similarly I can save the DTS package on the new
> server.
> Instead, Can I backup the msdb database and restore it on the new server.
> I
> was wondering which method would be the best.
> Thanks,
> rgn
sql

migrate users

How can I script out existing SQL users from a user database on Production s
erver to the same user databse migrated to Test server ? (Both srv are on w2
k, sql2000)
When I migrate the user database to Test server, I accidentally deleted all
the SQL users for that database on the Test server.
I hve looked at these KB, but not sure if the "logins transfer" will removed
the existing logins or any impact on the Production server,
http://support.microsoft.com/default.aspx?kbid=298897
http://support.microsoft.com/default.aspx?kbid=246133
http://support.microsoft.com/default.aspx?kbid=240872
pls helpHi,
You can script the users and associated roles for that user using the SQL
Server ENterprise manager "Generate SQL script"
1. Open Enterprise manager and connect to the SQL server where your actual
database resides.
2. Expand the datbases and select the database.
3. Right click above the database and choose All tasks and select "Generate
SQL Scripts"
4. Go to Options tab in the new window displayed
5. Check the option "Script database users and database roles"
6. Go back to "General Tab"
7. CLick Preview.. This will display you the database users and roles
assigned
8. copy the script and paste in to a notepad
9. Login to TEST SQL servers Query analyzer using 'SA'
10. Go to the database you have dropped the users
11. copy the notepad contents and paste it in Query analyzer
12. Execute the script... Ensure that you are in correct database.
This will create all the users back with correct previlages.
FYI, I have never tested this steps before.
Thanks
Hari
MCDBA
"pk" <pk@.discussions.microsoft.com> wrote in message
news:AB82B609-55E7-4D10-9344-4ACC87721FA6@.microsoft.com...
> How can I script out existing SQL users from a user database on Production
server to the same user databse migrated to Test server ? (Both srv are on
w2k, sql2000)
> When I migrate the user database to Test server, I accidentally deleted
all the SQL users for that database on the Test server.
> I hve looked at these KB, but not sure if the "logins transfer" will
removed the existing logins or any impact on the Production server,
> http://support.microsoft.com/default.aspx?kbid=298897
> http://support.microsoft.com/default.aspx?kbid=246133
> http://support.microsoft.com/default.aspx?kbid=240872
>
> pls help

migrate users

How can I script out existing SQL users from a user database on Production server to the same user databse migrated to Test server ? (Both srv are on w2k, sql2000)
When I migrate the user database to Test server, I accidentally deleted all the SQL users for that database on the Test server.
I hve looked at these KB, but not sure if the "logins transfer" will removed the existing logins or any impact on the Production server,
http://support.microsoft.com/default.aspx?kbid=298897
http://support.microsoft.com/default.aspx?kbid=246133
http://support.microsoft.com/default.aspx?kbid=240872
pls help
Hi,
You can script the users and associated roles for that user using the SQL
Server ENterprise manager "Generate SQL script"
1. Open Enterprise manager and connect to the SQL server where your actual
database resides.
2. Expand the datbases and select the database.
3. Right click above the database and choose All tasks and select "Generate
SQL Scripts"
4. Go to Options tab in the new window displayed
5. Check the option "Script database users and database roles"
6. Go back to "General Tab"
7. CLick Preview.. This will display you the database users and roles
assigned
8. copy the script and paste in to a notepad
9. Login to TEST SQL servers Query analyzer using 'SA'
10. Go to the database you have dropped the users
11. copy the notepad contents and paste it in Query analyzer
12. Execute the script... Ensure that you are in correct database.
This will create all the users back with correct previlages.
FYI, I have never tested this steps before.
Thanks
Hari
MCDBA
"pk" <pk@.discussions.microsoft.com> wrote in message
news:AB82B609-55E7-4D10-9344-4ACC87721FA6@.microsoft.com...
> How can I script out existing SQL users from a user database on Production
server to the same user databse migrated to Test server ? (Both srv are on
w2k, sql2000)
> When I migrate the user database to Test server, I accidentally deleted
all the SQL users for that database on the Test server.
> I hve looked at these KB, but not sure if the "logins transfer" will
removed the existing logins or any impact on the Production server,
> http://support.microsoft.com/default.aspx?kbid=298897
> http://support.microsoft.com/default.aspx?kbid=246133
> http://support.microsoft.com/default.aspx?kbid=240872
>
> pls help
sql

migrate users

How can I script out existing SQL users from a user database on Production server to the same user databse migrated to Test server ? (Both srv are on w2k, sql2000)
When I migrate the user database to Test server, I accidentally deleted all the SQL users for that database on the Test server.
I hve looked at these KB, but not sure if the "logins transfer" will removed the existing logins or any impact on the Production server,
http://support.microsoft.com/default.aspx?kbid=298897
http://support.microsoft.com/default.aspx?kbid=246133
http://support.microsoft.com/default.aspx?kbid=240872
pls helpHi,
You can script the users and associated roles for that user using the SQL
Server ENterprise manager "Generate SQL script"
1. Open Enterprise manager and connect to the SQL server where your actual
database resides.
2. Expand the datbases and select the database.
3. Right click above the database and choose All tasks and select "Generate
SQL Scripts"
4. Go to Options tab in the new window displayed
5. Check the option "Script database users and database roles"
6. Go back to "General Tab"
7. CLick Preview.. This will display you the database users and roles
assigned
8. copy the script and paste in to a notepad
9. Login to TEST SQL servers Query analyzer using 'SA'
10. Go to the database you have dropped the users
11. copy the notepad contents and paste it in Query analyzer
12. Execute the script... Ensure that you are in correct database.
This will create all the users back with correct previlages.
FYI, I have never tested this steps before.
--
Thanks
Hari
MCDBA
"pk" <pk@.discussions.microsoft.com> wrote in message
news:AB82B609-55E7-4D10-9344-4ACC87721FA6@.microsoft.com...
> How can I script out existing SQL users from a user database on Production
server to the same user databse migrated to Test server ? (Both srv are on
w2k, sql2000)
> When I migrate the user database to Test server, I accidentally deleted
all the SQL users for that database on the Test server.
> I hve looked at these KB, but not sure if the "logins transfer" will
removed the existing logins or any impact on the Production server,
> http://support.microsoft.com/default.aspx?kbid=298897
> http://support.microsoft.com/default.aspx?kbid=246133
> http://support.microsoft.com/default.aspx?kbid=240872
>
> pls help

Migrate subset of data from 2000 to 2005

First a little background.

Production server is SQL Server 2000. Various clients will be installing a desktop version of our web application. The desktop app will be running SQL Server 2005 Express (MSDE replacement, correct?). When the client downloads the package, the installer will import their data from the SQL Server 2000 production database into the SQL Server 2005 database. The production database contains numerous clients, so it doesn't make sense to migrate ALL of the data, only the data that belongs to the client.

My question is what method(s) would allow this to be done? The databases will NOT be connected to one another so using DTS packages is not an option.

After doing some research I came to the conclusion that the only way I could see this working, would be to output each tables data (just the data that belongs to the particular client) into its own text file and then import them individually on the SQL Server 2005 side.

Does anyone have any other ideas?

Kevin - I have the same dilemma currently and wondered if you had found a solution to this?

Migrate SQL Server from 64Bit platform to 32Bit

Hi, All:

We have to migrate several 64Bit SQL Servers to 32Bit platform. Due to the 64Bit source systems are all production system, the downtime is too limited. So we can only get the DB files via Detach way. Among the source systems, both SQL2000 and SQL2005 exist.

My question are:

Is it possible to migrate the DB from 64Bit to 32Bit via Detach/Attach?

If ok, how to do that?

Any info will be highly appreciated! Thanks!

Best Regards,

Kevin

See the response to the same question here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1066278&SiteID=1

Migrate SQL Server from 64Bit platform to 32Bit

Hi, All:

We have to migrate several 64Bit SQL Servers to 32Bit platform. Due to the 64Bit source systems are all production system, the downtime is too limited. So we can only get the DB files via Detach way. Among the source systems, both SQL2000 and SQL2005 exist.

My question are:

Is it possible to migrate the DB from 64Bit to 32Bit via Detach/Attach?

If ok, how to do that?

Any info will be highly appreciated! Thanks!

Best Regards,

Kevin

Hi Kevin.

The on-disk layout of database files is the same for 64bit and 32bit databases, there's no difference...you can freely attach/detach, backup/restore, etc. to/from 32bit/64bit systems and vice-versa at will with no issues. Of course, this assumes you are moving the between supported migration/compatibility instances...i.e. you couldn't move a Sql 2005 64bit database to a Sql 2000 32bit system...

HTH

|||

Hi, Chad:

Greate thanks for your reply!

Best Regards,

Kevin

Migrate jobs from production to backup

The backup server is using the agent to run log shipping. If I were to
migrate jobs prior to a disaster, the agent would run them. Assuming I can
turn them off so that having them run is not an issue, migrating them one at
a time is tedious.
Is there a way to mass migrate the jobs from one server to the other?
(packages are already migrated)
If such a thing as a mass import of the jobs is available, will the job
migration automatically schedule the packages or will it be necessary to
reschedule them (assuming a disaster event occurs and log-shipping is the
backup solution).
Regards,
Jamie
Hi
"thejamie" wrote:

> The backup server is using the agent to run log shipping. If I were to
> migrate jobs prior to a disaster, the agent would run them. Assuming I can
> turn them off so that having them run is not an issue, migrating them one at
> a time is tedious.
> Is there a way to mass migrate the jobs from one server to the other?
> (packages are already migrated)
>
As described in http://support.microsoft.com/kb/314546 you can script all
jobs.

> If such a thing as a mass import of the jobs is available, will the job
> migration automatically schedule the packages or will it be necessary to
> reschedule them (assuming a disaster event occurs and log-shipping is the
> backup solution).
>
Scripting will re-create the same schedules. The jobs should be enabled when
you are scripting them if you don't want to change them on the destination
server.

> --
> Regards,
> Jamie
John
|||I hadn't noticed before, but there appears to be a wizard in SQL 2005 for
this. We have our production running on 2000 and with disk space at a
premium and backup time stretched to the limit, we needed to start
compressing backups and decided to buy a package. It works great. I am
impressed to see that the 2005 version already does this. Elated actually.
Regards,
Jamie
"John Bell" wrote:

> Hi
> "thejamie" wrote:
> As described in http://support.microsoft.com/kb/314546 you can script all
> jobs.
>
> Scripting will re-create the same schedules. The jobs should be enabled when
> you are scripting them if you don't want to change them on the destination
> server.
>
> John

Migrate jobs from production to backup

The backup server is using the agent to run log shipping. If I were to
migrate jobs prior to a disaster, the agent would run them. Assuming I can
turn them off so that having them run is not an issue, migrating them one at
a time is tedious.
Is there a way to mass migrate the jobs from one server to the other?
(packages are already migrated)
If such a thing as a mass import of the jobs is available, will the job
migration automatically schedule the packages or will it be necessary to
reschedule them (assuming a disaster event occurs and log-shipping is the
backup solution).
Regards,
JamieHi
"thejamie" wrote:

> The backup server is using the agent to run log shipping. If I were to
> migrate jobs prior to a disaster, the agent would run them. Assuming I c
an
> turn them off so that having them run is not an issue, migrating them one
at
> a time is tedious.
> Is there a way to mass migrate the jobs from one server to the other?
> (packages are already migrated)
>
As described in http://support.microsoft.com/kb/314546 you can script all
jobs.

> If such a thing as a mass import of the jobs is available, will the job
> migration automatically schedule the packages or will it be necessary to
> reschedule them (assuming a disaster event occurs and log-shipping is the
> backup solution).
>
Scripting will re-create the same schedules. The jobs should be enabled when
you are scripting them if you don't want to change them on the destination
server.

> --
> Regards,
> Jamie
John|||I hadn't noticed before, but there appears to be a wizard in SQL 2005 for
this. We have our production running on 2000 and with disk space at a
premium and backup time stretched to the limit, we needed to start
compressing backups and decided to buy a package. It works great. I am
impressed to see that the 2005 version already does this. Elated actually.
--
Regards,
Jamie
"John Bell" wrote:

> Hi
> "thejamie" wrote:
>
> As described in http://support.microsoft.com/kb/314546 you can script all
> jobs.
>
> Scripting will re-create the same schedules. The jobs should be enabled wh
en
> you are scripting them if you don't want to change them on the destination
> server.
>
> John

Migrate form stand alone to clustered server

Here is the scenario:
We have an existing production non clustered SQL 2000 server instance that we need to migrate to a new clustered SQL 2000 server instance. We need to accomplish this without affecting the FQDN that applications use to call this server. I found this article on a solution to rename the server after an xcopy of the entire db structure. Here is the link http://vyaskn.tripod.com/moving_sql_server.htm. The other issue that we are trying to resolve is the time it takes for the snapshots of replication to run (in our case almost a full day). That is why this approach looked like it may be a good solution for us.

Here is the question:
Is it possible to move our existing database to a new clustered environment without having to change the FQDN that other applications use to access this database and without having to reinitialize replication?GrantAsh,

The link you posted could work. But, the problem is in the drive letters and the folders.

The problem is that for a clustered server the data drives need to be shared in the cluster. The concern that I would have is the drive letters that you used for your current SQL server would not match the Clustered SQL server drives.

But, if you can match the drive letters then copying the data is not a problem.

But I can't stress this enough, using the method described on that web page to change the SQL server name will not work for a Clustered SQL Server.

I would suggest reading the following:
http://support.microsoft.com/kb/244980/
http://www.sql-server-performance.com/clustering_2000.asp

bEH

migrate a local SQL user account

whats the simplest way to migrate a user account from a master development SQL server to local development sql servers and also to a production server?

we can recreate the SQL account everywhere, but if we take a backup from one machine and restore to another, we loose access because the accounts arent truly the same account.Very few info can be found in sql books online.
You need to read Microsoft Knowledge Base Article - 246133
check out this link
http://support.microsoft.com/default.aspx?scid=kb;[LN];Q246133|||thanks for the article...i need to run the process that keeps the SID intact.

somehow i new the was no copy/paste solution... :-(

Microsoft Visual Studio is unable to load this document after SQL Server SP2 install.

Hello all,

Over the weekend, I loaded the SQL Server SP2 on to my production database after having it on my dev server for two weeks with out any problems.

After I did this, I went to check all my SSIS packages and found them not able to load.

I get this message “Microsoft Visual Studio is unable to load this document: The package failed to load due to error 0xC0010014 “One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors”. This occurs when CPackage::LoadFromXML fails.

Now when I look at the errors I see:

Error 1 Error loading Master_Full_Weekly.dtsx: Error loading value "<DTS:LogProvider xmlns : DTS="www.microsoft.com/SqlServer/Dts" > <DTS : Property DTS:Name="ConfigString">SQL_TPMTSH_Datamart</DTS: Property><DTS : Property DTS:Name="DelayValidation">0</DTS: Property><DTS: Property DTS:Name="ObjectName">DTS Log Provider for SQL Serve" from node "DTS: LogProvider". D:\ETL\Projects\TPM\TPMTSH\Master_Full_Weekly.dtsx 1 1

Does anyone know what this is about and how I can go about fixing it?Anyone?|||

Could you try to register xml modules:

regsvr32 msxml3.dll

regsvr32 msxml6.dll

HTH.