Moving all the data to a new server

What is the best approach to move all the databases from one server
to a new server with minimum downtime and then unplugging the old server ?
Thanks in advance,
MBTry these two pages:
http://support.microsoft.com/default.aspx?kbid=314546#9
http://support.microsoft.com/default.aspx?scid=kb;en-us;246133
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"MB" <mbarroso@.webcentralsolutions.com> wrote in message
news:%234yaacNcDHA.1044@.TK2MSFTNGP10.phx.gbl...
> What is the best approach to move all the databases from one server
> to a new server with minimum downtime and then unplugging the old server ?
> Thanks in advance,
> MB
>

moving all databases to new server including sql server configurat

Hi,
We are getting a new server and need to move all the sql server database to this new server including configuration of the current sql server software. What is the best way to do this? Currently, we do not have any backup of the production.
Thanks,
Jack
Probably the easiest way is to detach the databases and copy to new server and attach. Depending on your security model, you could also use the DTS import login functions to bring users over
"Jack" wrote:

> Hi,
> We are getting a new server and need to move all the sql server database to this new server including configuration of the current sql server software. What is the best way to do this? Currently, we do not have any backup of the production.
> Thanks,
> Jack
|||Have a look at this: http://vyaskn.tripod.com/moving_sql_server.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:B4462402-6091-4D5B-B0DB-1C61BE5F3E45@.microsoft.com...
Hi,
We are getting a new server and need to move all the sql server database to
this new server including configuration of the current sql server software.
What is the best way to do this? Currently, we do not have any backup of the
production.
Thanks,
Jack
|||Hi,
Have a look into the below link for moving all the databases to new server.
Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Thanks
Hari
MCDBA
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:B4462402-6091-4D5B-B0DB-1C61BE5F3E45@.microsoft.com...
> Hi,
> We are getting a new server and need to move all the sql server database
to this new server including configuration of the current sql server
software. What is the best way to do this? Currently, we do not have any
backup of the production.
> Thanks,
> Jack

moving all databases to new server including sql server configurat

Hi,
We are getting a new server and need to move all the sql server database to
this new server including configuration of the current sql server software.
What is the best way to do this? Currently, we do not have any backup of the
production.
Thanks,
JackProbably the easiest way is to detach the databases and copy to new server a
nd attach. Depending on your security model, you could also use the DTS imp
ort login functions to bring users over
"Jack" wrote:

> Hi,
> We are getting a new server and need to move all the sql server database t
o this new server including configuration of the current sql server software
. What is the best way to do this? Currently, we do not have any backup of t
he production.
> Thanks,
> Jack|||Have a look at this: http://vyaskn.tripod.com/moving_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:B4462402-6091-4D5B-B0DB-1C61BE5F3E45@.microsoft.com...
Hi,
We are getting a new server and need to move all the sql server database to
this new server including configuration of the current sql server software.
What is the best way to do this? Currently, we do not have any backup of the
production.
Thanks,
Jack|||Hi,
Have a look into the below link for moving all the databases to new server.
Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Thanks
Hari
MCDBA
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:B4462402-6091-4D5B-B0DB-1C61BE5F3E45@.microsoft.com...
> Hi,
> We are getting a new server and need to move all the sql server database
to this new server including configuration of the current sql server
software. What is the best way to do this? Currently, we do not have any
backup of the production.
> Thanks,
> Jack

Moving a SQL2K publisher to a SQL 2005 instance.

Hi,

I have to move a SQL2K instance (publisher) to a SQL 2005 instance and i prefer not to re-snapshot all the databases being replicated due to the large size of the databases. As i understand all i need to do is

1. Ensure all published databases are in readonly mode.

2. Stop the replication (by either dropping subscriptions, publication or stopping replication agents).

3. Move the databases to the SQL 2005 instance.

4. Configure a SQL 2005 instance for the distributor (for a SQL 2005 publisher, the distribution database must be a SQL 2005 instance).

5. Setup replication to the SQL2K subscriber but not "initialise" each subscription with a snapshot of the publication data and schema.

Are these the only issues i need to consider?
Any comments greatly appreciated.
Thanks,
Priyanga
Is this merge replication or Transactional.
If it is merge, snapshot will need to be generated no matter how you plan to move to SQL Server 2005.
If it is Transactional, I will need to check/verify.|||Sorry, i forgot to mention.

Its for transactional replication.

Cheers,
Priyanga
|||In SQL Server 2005 Books Online, please take a look at topic:

"Initializing a Transactional Subscription Without a Snapshot".

|||Thanks Greg. This is what i have been looking for.

Cheers,
Priyanga
|||

Hi Greg,

I have encountered a problem implementing this solution....

Not initialising the subscription upon its creation at the subscriber enables me to not have to re-snapshot the publication but this does not create the system procedures on the subscribed database which SQL 2005 needs to support replication.

For example, with a SQL 2000 replication topology, it executes [dbo].[sp_MSins_Customers] to do an insert to the Customer table at the subscriber but SQL 2005 executes [dbo].[sp_MSins_dboCustomers] which it cannot find unless you reinitialise from a snapshot. We do not want to do this as we’ll need to snapshot about 100GB of data.

Assistance on this matter is greatly appreciated.

Cheers,
Priyanga

|||

You may need to create these procs at the subscriber manually, if you havent have them on th subscribers.

|||Hi Mahesh,

There could be some issues if i create these procedures manually on the subscriber. For example, since the naming of the procedures differ is it possible that the logic of these procedures also differ.

Why doesn't the process of creating a subscription at the subscriber re-create the new procedures which SQL 2005 needs for replication?

I think this should be flagged as a bug as there is no mention that it can't be done in BOL in 2005.

Cheers,
Priyanga|||Hi Priyanga,

You can specify @.sync_type to 'replication support only' when sp_addsubscription is called. It will create appropriate replication system procedure at subscriber.

BOL has detailed instruction to help you do that. Look at topic "How to: Initialize a Subscription Manually (SQL Server Management Studio)" or "How to: Initialize a Subscription Manually (Replication Transact-SQL Programming)", depending you prefer to use UI or procedure.

Please let me know if it works for you.

Peng|||

Priyanga Karunathilake wrote:

I have encountered a problem implementing this solution....

Oops, looks like you already tried with sync_type as either 'replication support only' or 'initialize with backup'. Replication should create system procs on the subscriber database for both case. Have you run logreader and distribution agent after create subscription? Is it a push or pull subscription?|||

Hi Peng,

The log reader and distribution agents are both running after I setup the subscription. It is a pull subscription.

When I try to set up this scenario where I do a pull subscription without initializing the subscription, the new SQL 2005 distribution stored procedures don’t get created on the subscriber. All I see are sp_MSxxx_tablename type stored procedures which is from the SQL 2000 replication and hense I get the error cannot find sp_MSxxx_dbotablename procedure. Note the dbo in the SQL 2005 procedures.

When I do a push subscription without initializing, it successfully creates the SQL 2005 distribution stored procedures on the subscriber. Also, when I do a pull subscription WITH initialize it creates the SQL 2005 distribution stored procedures on the subscriber.

Cheers,

Priyanga

|||Hi Priyanga,

Pull subscription should also creates the system procs on subscriber DB. The name of the procs depends on parameters @.ins_cmd/@.upd_cmd/@.del_cmd when article is created (i.e. sp_addarticle). The default is sp_MSxxx_dbotablename for SQL 2005. How do you create the replication? Through UI and script? You can query sysarticles on publication DB to get the CALL format you are using.

Another piece of information that might be useful is the @.schema_option of the article.

For the subscriber, is it SQL 2000 or 2005?

-Peng

|||Hi Peng,

I am creating the replication using UI.

The subscriber is a SQL 2000 instance. I scripted out the commands during the pull subscription creation.

What i don't understand is why the distribution procedures don't get created for a pull subscription when the subscription is not initialised. They get created for all other scenarios. I'am beginning to think this is a bug in SQL 2005.

Thanks for you assistance.

Priyanga

--BEGIN: Script to be run at Publisher 'INSPIRON\SQL01'--

use [NorthwindPK]

exec sp_addsubscription @.publication = N'NorthwindPK_SQL2005', @.subscriber = N'INSPIRON\SQL02', @.destination_db = N'NorthwindPK', @.sync_type = N'replication support only', @.subscription_type = N'pull', @.update_mode = N'read only'

GO

--END: Script to be run at Publisher 'INSPIRON\SQL01'--

--BEGIN: Script to be run at Subscriber 'INSPIRON\SQL02'--

use [NorthwindPK]

exec sp_addpullsubscription @.publisher = N'INSPIRON\SQL01', @.publication = N'NorthwindPK_SQL2005', @.publisher_db = N'NorthwindPK', @.independent_agent = N'True', @.subscription_type = N'pull', @.description = N'', @.update_mode = N'read only', @.immediate_sync = 1

exec sp_addpullsubscription_agent @.publisher = N'INSPIRON\SQL01', @.publisher_db = N'NorthwindPK', @.publication = N'NorthwindPK_SQL2005', @.subscriber_security_mode = 1, @.subscriber_login = N'', @.subscriber_password = null, @.distributor = N'INSPIRON\SQL01', @.distributor_security_mode = 1, @.distributor_login = N'', @.distributor_password = null, @.enabled_for_syncmgr = N'False', @.frequency_type = 64, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 20051014, @.active_end_date = 99991231, @.alt_snapshot_folder = N'', @.working_directory = N'', @.use_ftp = N'False', @.offloadagent = N'False', @.offloadserver = N'', @.publication_type = 0

GO

--END: Script to be run at Subscriber 'INSPIRON\SQL02'--

|||

Priyanga,

Since the distribution agent on your pull subscriber is effectively SQL2000 binary from way back, it doesn't really understand the commands posted to MSrepl_commands for creating the custom procedures as provided by the 'replication support only' @.sync_type since this is a SQL2005 feature. I can understand your disappointment, but we can't really "fix" this by changing anything in SQL2005.

-Raymond

|||Hi Raymond.

I guess i have to do a full initialise of the subscription when we move to SQL 2005 for our publisher and distributor.....

Do you know whether significant performance enhancements have been made to the speed of initialising a subscription from snaphot?
Taking into consideration that i will be going from a SQL 2005 Publisher and Distributor on separate servers to SQL 2000 subscriber, and have to initialise 150GB of data, would you have an approximate time it might take? The only environemnt stats i can give you is that we have a gigabit network connection, publisher and distributor are 64bit with 16GB memory, all servers connected to SAN and that it would be performed during a scheduled downtime?

Cheers,
Priyanga

Moving a SQL2K publisher to a SQL 2005 instance.

Hi,

I have to move a SQL2K instance (publisher) to a SQL 2005 instance and i prefer not to re-snapshot all the databases being replicated due to the large size of the databases. As i understand all i need to do is

1. Ensure all published databases are in readonly mode.

2. Stop the replication (by either dropping subscriptions, publication or stopping replication agents).

3. Move the databases to the SQL 2005 instance.

4. Configure a SQL 2005 instance for the distributor (for a SQL 2005 publisher, the distribution database must be a SQL 2005 instance).

5. Setup replication to the SQL2K subscriber but not "initialise" each subscription with a snapshot of the publication data and schema.

Are these the only issues i need to consider?
Any comments greatly appreciated.
Thanks,
Priyanga
Is this merge replication or Transactional.
If it is merge, snapshot will need to be generated no matter how you plan to move to SQL Server 2005.
If it is Transactional, I will need to check/verify.|||Sorry, i forgot to mention.

Its for transactional replication.

Cheers,
Priyanga
|||In SQL Server 2005 Books Online, please take a look at topic:

"Initializing a Transactional Subscription Without a Snapshot".

|||Thanks Greg. This is what i have been looking for.

Cheers,
Priyanga
|||

Hi Greg,

I have encountered a problem implementing this solution....

Not initialising the subscription upon its creation at the subscriber enables me to not have to re-snapshot the publication but this does not create the system procedures on the subscribed database which SQL 2005 needs to support replication.

For example, with a SQL 2000 replication topology, it executes [dbo].[sp_MSins_Customers] to do an insert to the Customer table at the subscriber but SQL 2005 executes [dbo].[sp_MSins_dboCustomers] which it cannot find unless you reinitialise from a snapshot. We do not want to do this as we’ll need to snapshot about 100GB of data.

Assistance on this matter is greatly appreciated.

Cheers,
Priyanga

|||

You may need to create these procs at the subscriber manually, if you havent have them on th subscribers.

|||Hi Mahesh,

There could be some issues if i create these procedures manually on the subscriber. For example, since the naming of the procedures differ is it possible that the logic of these procedures also differ.

Why doesn't the process of creating a subscription at the subscriber re-create the new procedures which SQL 2005 needs for replication?

I think this should be flagged as a bug as there is no mention that it can't be done in BOL in 2005.

Cheers,
Priyanga|||Hi Priyanga,

You can specify @.sync_type to 'replication support only' when sp_addsubscription is called. It will create appropriate replication system procedure at subscriber.

BOL has detailed instruction to help you do that. Look at topic "How to: Initialize a Subscription Manually (SQL Server Management Studio)" or "How to: Initialize a Subscription Manually (Replication Transact-SQL Programming)", depending you prefer to use UI or procedure.

Please let me know if it works for you.

Peng|||

Priyanga Karunathilake wrote:

I have encountered a problem implementing this solution....

Oops, looks like you already tried with sync_type as either 'replication support only' or 'initialize with backup'. Replication should create system procs on the subscriber database for both case. Have you run logreader and distribution agent after create subscription? Is it a push or pull subscription?|||

Hi Peng,

The log reader and distribution agents are both running after I setup the subscription. It is a pull subscription.

When I try to set up this scenario where I do a pull subscription without initializing the subscription, the new SQL 2005 distribution stored procedures don’t get created on the subscriber. All I see are sp_MSxxx_tablename type stored procedures which is from the SQL 2000 replication and hense I get the error cannot find sp_MSxxx_dbotablename procedure. Note the dbo in the SQL 2005 procedures.

When I do a push subscription without initializing, it successfully creates the SQL 2005 distribution stored procedures on the subscriber. Also, when I do a pull subscription WITH initialize it creates the SQL 2005 distribution stored procedures on the subscriber.

Cheers,

Priyanga

|||Hi Priyanga,

Pull subscription should also creates the system procs on subscriber DB. The name of the procs depends on parameters @.ins_cmd/@.upd_cmd/@.del_cmd when article is created (i.e. sp_addarticle). The default is sp_MSxxx_dbotablename for SQL 2005. How do you create the replication? Through UI and script? You can query sysarticles on publication DB to get the CALL format you are using.

Another piece of information that might be useful is the @.schema_option of the article.

For the subscriber, is it SQL 2000 or 2005?

-Peng

|||Hi Peng,

I am creating the replication using UI.

The subscriber is a SQL 2000 instance. I scripted out the commands during the pull subscription creation.

What i don't understand is why the distribution procedures don't get created for a pull subscription when the subscription is not initialised. They get created for all other scenarios. I'am beginning to think this is a bug in SQL 2005.

Thanks for you assistance.

Priyanga

--BEGIN: Script to be run at Publisher 'INSPIRON\SQL01'--

use [NorthwindPK]

exec sp_addsubscription @.publication = N'NorthwindPK_SQL2005', @.subscriber = N'INSPIRON\SQL02', @.destination_db = N'NorthwindPK', @.sync_type = N'replication support only', @.subscription_type = N'pull', @.update_mode = N'read only'

GO

--END: Script to be run at Publisher 'INSPIRON\SQL01'--

--BEGIN: Script to be run at Subscriber 'INSPIRON\SQL02'--

use [NorthwindPK]

exec sp_addpullsubscription @.publisher = N'INSPIRON\SQL01', @.publication = N'NorthwindPK_SQL2005', @.publisher_db = N'NorthwindPK', @.independent_agent = N'True', @.subscription_type = N'pull', @.description = N'', @.update_mode = N'read only', @.immediate_sync = 1

exec sp_addpullsubscription_agent @.publisher = N'INSPIRON\SQL01', @.publisher_db = N'NorthwindPK', @.publication = N'NorthwindPK_SQL2005', @.subscriber_security_mode = 1, @.subscriber_login = N'', @.subscriber_password = null, @.distributor = N'INSPIRON\SQL01', @.distributor_security_mode = 1, @.distributor_login = N'', @.distributor_password = null, @.enabled_for_syncmgr = N'False', @.frequency_type = 64, @.frequency_interval = 0, @.frequency_relative_interval = 0, @.frequency_recurrence_factor = 0, @.frequency_subday = 0, @.frequency_subday_interval = 0, @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959, @.active_start_date = 20051014, @.active_end_date = 99991231, @.alt_snapshot_folder = N'', @.working_directory = N'', @.use_ftp = N'False', @.offloadagent = N'False', @.offloadserver = N'', @.publication_type = 0

GO

--END: Script to be run at Subscriber 'INSPIRON\SQL02'--

|||

Priyanga,

Since the distribution agent on your pull subscriber is effectively SQL2000 binary from way back, it doesn't really understand the commands posted to MSrepl_commands for creating the custom procedures as provided by the 'replication support only' @.sync_type since this is a SQL2005 feature. I can understand your disappointment, but we can't really "fix" this by changing anything in SQL2005.

-Raymond

|||Hi Raymond.

I guess i have to do a full initialise of the subscription when we move to SQL 2005 for our publisher and distributor.....

Do you know whether significant performance enhancements have been made to the speed of initialising a subscription from snaphot?
Taking into consideration that i will be going from a SQL 2005 Publisher and Distributor on separate servers to SQL 2000 subscriber, and have to initialise 150GB of data, would you have an approximate time it might take? The only environemnt stats i can give you is that we have a gigabit network connection, publisher and distributor are 64bit with 16GB memory, all servers connected to SAN and that it would be performed during a scheduled downtime?

Cheers,
Priyanga

moving a database to another physical disk

I want to move 2 databases to another physical disk. They are both single file databases. It looks like a fairly straigtforward attach and detach procedure, but I have a couple of questions:

The log files (.ldf) currently reside on a separate physical disk from the data files, if I attach and reattach the data files will the logs remain where they are by default or do I have to re specify there location?

Is there any advantage to running the update statistics portion of the sp_detach_db?with the re-attach procedure you can specify the location of the log file as well. See the article in BOL and this is probably the quickest method. However the same thing can be done with backup and restore using the with move and replace arguments.

Moving a Database

I have a few databases that is clogging up my C drive and I would like to
move them to my D: drive.
What is the easiest and safest way to do that?Hi,
just
-detach them from drive c:
-copy over to drive d:
-attach on drive d:
You can either use thhe GUi for attach /detach or use the sp_detach /
sp_attach statement / procedure for that, more details can be found in
the BOL.
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
--|||Cool, thank you.
But where is the GUI located for that?
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1145564475.673288.28790@.g10g2000cwb.googlegroups.com...
> Hi,
> just
> -detach them from drive c:
> -copy over to drive d:
> -attach on drive d:
> You can either use thhe GUi for attach /detach or use the sp_detach /
> sp_attach statement / procedure for that, more details can be found in
> the BOL.
> HTH, jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||Hi,
depends on which version of SQL Server you are using. If you use the
client tools for SQL 2000 you lopen the enterprise manager, navigate to
the database you want to detach, right click, select detach, copy the
files, right click on the Server, select attach, browse to the
destination and you are done.
It is nearly the same for SQl Server Management Studio and SQl Server
2005.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||How or where do I find teh GUI?
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1145564475.673288.28790@.g10g2000cwb.googlegroups.com...
> Hi,
> just
> -detach them from drive c:
> -copy over to drive d:
> -attach on drive d:
> You can either use thhe GUi for attach /detach or use the sp_detach /
> sp_attach statement / procedure for that, more details can be found in
> the BOL.
> HTH, jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||You didn=B4t say yet which version of SQL Server you are using.sql

Moving a Database

I have a few databases that is clogging up my C drive and I would like to
move them to my D: drive.
What is the easiest and safest way to do that?Hi,
just
-detach them from drive c:
-copy over to drive d:
-attach on drive d:
You can either use thhe GUi for attach /detach or use the sp_detach /
sp_attach statement / procedure for that, more details can be found in
the BOL.
HTH, jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Cool, thank you.
But where is the GUI located for that?
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1145564475.673288.28790@.g10g2000cwb.googlegroups.com...
> Hi,
> just
> -detach them from drive c:
> -copy over to drive d:
> -attach on drive d:
> You can either use thhe GUi for attach /detach or use the sp_detach /
> sp_attach statement / procedure for that, more details can be found in
> the BOL.
> HTH, jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||Hi,
depends on which version of SQL Server you are using. If you use the
client tools for SQL 2000 you lopen the enterprise manager, navigate to
the database you want to detach, right click, select detach, copy the
files, right click on the Server, select attach, browse to the
destination and you are done.
It is nearly the same for SQl Server Management Studio and SQl Server
2005.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||How or where do I find teh GUI?
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1145564475.673288.28790@.g10g2000cwb.googlegroups.com...
> Hi,
> just
> -detach them from drive c:
> -copy over to drive d:
> -attach on drive d:
> You can either use thhe GUi for attach /detach or use the sp_detach /
> sp_attach statement / procedure for that, more details can be found in
> the BOL.
> HTH, jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||You didn=B4t say yet which version of SQL Server you are using.

moving a database

If we move our databases from one server to another and rename the new server to what the old one was and give it the same IP and then take the old one offline, will this have any impact on the applications that the databases connect with? Both Servers are running Windows 2003 Enterprise and SQL Sever 2005. Will the applications the databases connect with see it as the old server?

Also, will this move be an issue for reporting services? Will the report server see the reporting services databases as the same ones that were on the old server?

As long as DNS resolves the name to the IP correctly (which it should), that should work. One issue you will run into is the "orphaned user" problem (if you are using SQL Server "mixed authentication"). The SQL Server login on the new server won't be able to talk to the existing SQL Server database user in the database(s) that you move to the new server unless you fix the problem by running this command:

sp_change_users_login @.Action='update_one', @.UserNamePattern='usernameindatabase', @.LoginName='loginnameonsql';

You will have to do it for each user in each database, so hopefully you are using a single "application" user (to take advantage of connection pooling).

http://glennberrysqlperformance.spaces.live.com/

|||

Thanks for the tip. My understanding is that the copy database wizard will move the windows logins without an issue. All of our logins except for one are windows integrated logins, not sql logins.

Also, would you use the copy database wizard when transferring the databases between servers?

|||I would not need to worry about using a single connection (user) if all our logins our windows? correct?|||

it again depends upon whether u have Active Directory or not. If users are domain user you will not have any problem...

Madhu

Moving 5 databases to new server

In a few hours I am going to move 5 user databases from the existing product
ion server to a new one. I have a plan in place, but I would like to verify
that its the correct approach.
Here's my plan:
1. Stop SQL Server on production using Services Manager
2. Start SQL Server on production in Single User Mode
-using Services Manager, type "-m" in startup parameters
3. Perform a manual Full Backup of each user database to be migrated
4. Stop the SQL Server on production
5. Restore each database on the new server from the backup files
-check "force restore over existing database"
6. Link all orphaned logins on each database
7. Point the application to the new server
My main question is: Do I need to restore transaction logs also?
No users will be connected at the time of the full backup. I am currectly u
sing the Full recovery model on each database. I have already recreated all
the DTS jobs and Database Maintenance Plans on the new server. The new ser
ver is not yet in use, so I
was able to test the process by restoring each database from the production
backup files to the new server.
The production SQL Server is 2000 version 8.00.194(RTM). The new server is
2000 version 8.00.760(SP3).
Am I overlooking anything?
Thanks,
ScottI am placing the production database in single user mode to ensure there are
no other users.
I have actually performed the restores and they worked OK. I am not restori
ng the system databases only user databases.
The link you provided did not work.
"DeeJay Puar" wrote:

> Hi,
> Here are some comments:
> 1. Production server does have to in 'Single User Mode',
> since no one is going to be connected.
> 2. If you do a fullbackup and restore without further
> activity on the database, you do not have to restore any
> transaction log backups.
> 3. I do not think, restores will work with different
> service packs (production is different level than new
> server).
> 4. Here is an nice article to review:
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;314546&Product=sql
> hth
> DeeJay
> the existing production server to a new one. I have a
> plan in place, but I would like to verify that its the
> correct approach.
> parameters
> be migrated
> backup files
> logs also?
> backup. I am currectly using the Full recovery model on
> each database. I have already recreated all the DTS jobs
> and Database Maintenance Plans on the new server. The new
> server is not yet in use, so I was able to test the
> process by restoring each database from the production
> backup files to the new server.
> The new server is 2000 version 8.00.760(SP3).
>|||Some of below might help:
Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Server
s
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"scott" <scott@.discussions.microsoft.com> wrote in message
news:D4DB1784-FA39-4FD7-A9DC-F557759A333A@.microsoft.com...
> In a few hours I am going to move 5 user databases from the existing production se
rver to a new
one. I have a plan in place, but I would like to verify that its the correct approach.[vbco
l=seagreen]
> Here's my plan:
> 1. Stop SQL Server on production using Services Manager
> 2. Start SQL Server on production in Single User Mode
> -using Services Manager, type "-m" in startup parameters
> 3. Perform a manual Full Backup of each user database to be migrated
> 4. Stop the SQL Server on production
> 5. Restore each database on the new server from the backup files
> -check "force restore over existing database"
> 6. Link all orphaned logins on each database
> 7. Point the application to the new server
> My main question is: Do I need to restore transaction logs also?
> No users will be connected at the time of the full backup. I am currectly using t
he Full recovery[/vbcol]
model on each database. I have already recreated all the DTS jobs and Datab
ase Maintenance Plans on
the new server. The new server is not yet in use, so I was able to test the
process by restoring
each database from the production backup files to the new server.
> The production SQL Server is 2000 version 8.00.194(RTM). The new server is 2000 v
ersion
8.00.760(SP3).
> Am I overlooking anything?
> Thanks,
> Scott|||also,...
Restore or Detach\Reattach will not move\migrate SQL Agent Jobs, System
Messages, DTS Packages, etc.
Greg Jackson
PDX, Oregon

Moving 5 databases to new server

In a few hours I am going to move 5 user databases from the existing production server to a new one. I have a plan in place, but I would like to verify that its the correct approach.
Here's my plan:
1. Stop SQL Server on production using Services Manager
2. Start SQL Server on production in Single User Mode
-using Services Manager, type "-m" in startup parameters
3. Perform a manual Full Backup of each user database to be migrated
4. Stop the SQL Server on production
5. Restore each database on the new server from the backup files
-check "force restore over existing database"
6. Link all orphaned logins on each database
7. Point the application to the new server
My main question is: Do I need to restore transaction logs also?
No users will be connected at the time of the full backup. I am currectly using the Full recovery model on each database. I have already recreated all the DTS jobs and Database Maintenance Plans on the new server. The new server is not yet in use, so I
was able to test the process by restoring each database from the production backup files to the new server.
The production SQL Server is 2000 version 8.00.194(RTM). The new server is 2000 version 8.00.760(SP3).
Am I overlooking anything?
Thanks,
Scott
I am placing the production database in single user mode to ensure there are no other users.
I have actually performed the restores and they worked OK. I am not restoring the system databases only user databases.
The link you provided did not work.
"DeeJay Puar" wrote:

> Hi,
> Here are some comments:
> 1. Production server does have to in 'Single User Mode',
> since no one is going to be connected.
> 2. If you do a fullbackup and restore without further
> activity on the database, you do not have to restore any
> transaction log backups.
> 3. I do not think, restores will work with different
> service packs (production is different level than new
> server).
> 4. Here is an nice article to review:
> http://support.microsoft.com/default.aspx?scid=kb;en-
> us;314546&Product=sql
> hth
> DeeJay
> the existing production server to a new one. I have a
> plan in place, but I would like to verify that its the
> correct approach.
> parameters
> be migrated
> backup files
> logs also?
> backup. I am currectly using the Full recovery model on
> each database. I have already recreated all the DTS jobs
> and Database Maintenance Plans on the new server. The new
> server is not yet in use, so I was able to test the
> process by restoring each database from the production
> backup files to the new server.
> The new server is 2000 version 8.00.760(SP3).
>
|||Some of below might help:
Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL Servers
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"scott" <scott@.discussions.microsoft.com> wrote in message
news:D4DB1784-FA39-4FD7-A9DC-F557759A333A@.microsoft.com...
> In a few hours I am going to move 5 user databases from the existing production server to a new
one. I have a plan in place, but I would like to verify that its the correct approach.
> Here's my plan:
> 1. Stop SQL Server on production using Services Manager
> 2. Start SQL Server on production in Single User Mode
> -using Services Manager, type "-m" in startup parameters
> 3. Perform a manual Full Backup of each user database to be migrated
> 4. Stop the SQL Server on production
> 5. Restore each database on the new server from the backup files
> -check "force restore over existing database"
> 6. Link all orphaned logins on each database
> 7. Point the application to the new server
> My main question is: Do I need to restore transaction logs also?
> No users will be connected at the time of the full backup. I am currectly using the Full recovery
model on each database. I have already recreated all the DTS jobs and Database Maintenance Plans on
the new server. The new server is not yet in use, so I was able to test the process by restoring
each database from the production backup files to the new server.
> The production SQL Server is 2000 version 8.00.194(RTM). The new server is 2000 version
8.00.760(SP3).
> Am I overlooking anything?
> Thanks,
> Scott
|||also,...
Restore or Detach\Reattach will not move\migrate SQL Agent Jobs, System
Messages, DTS Packages, etc.
Greg Jackson
PDX, Oregon

Moving 2005 Resource Database

We have a sql 2005 installation that has service pack 2 applied. All
the system databases apart from the resource database have been moved
from the default data directory to our own data directory on a
seperate drive and this went ok. We've done this basically to make the
back up process more efficient in our environment. The issue is that
we cant find a way to move the resource database to the new location
and this I beleive needs to be kept with the master database. We've
followed the BOL instructions for moving system databases but the
Alter script for the resource database fails as the resource database
doesnt exist in sysdatabases. I've tried simply moving the resource
files to the new location but then sql wont start as it cant find the
files.
Anybody have any ideas how to resolve this issue?
Thanks
MichaelThe instructions in Books Online for moving the master and resource database
files should work. However, we made some corrections to the procedure in
April 2006. If you don't have the latest version of Books Online, you might
be running into a problem.
I suggest that you download the latest version fo BOL (see the link in my
signature) and then starting with step 7 (Start the instance of SQL Server
in master-only recovery mode...), see if you can get the resource database
moved. If you still run into trouble, please report the exact error
message you're getting.
Regards,
Gail
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Michael" <michael@.hampel.fsnet.co.uk> wrote in message
news:1183985960.779841.257510@.k79g2000hse.googlegroups.com...
> We have a sql 2005 installation that has service pack 2 applied. All
> the system databases apart from the resource database have been moved
> from the default data directory to our own data directory on a
> seperate drive and this went ok. We've done this basically to make the
> back up process more efficient in our environment. The issue is that
> we cant find a way to move the resource database to the new location
> and this I beleive needs to be kept with the master database. We've
> followed the BOL instructions for moving system databases but the
> Alter script for the resource database fails as the resource database
> doesnt exist in sysdatabases. I've tried simply moving the resource
> files to the new location but then sql wont start as it cant find the
> files.
> Anybody have any ideas how to resolve this issue?
> Thanks
> Michael
>

Moving 2005 Resource Database

We have a sql 2005 installation that has service pack 2 applied. All
the system databases apart from the resource database have been moved
from the default data directory to our own data directory on a
seperate drive and this went ok. We've done this basically to make the
back up process more efficient in our environment. The issue is that
we cant find a way to move the resource database to the new location
and this I beleive needs to be kept with the master database. We've
followed the BOL instructions for moving system databases but the
Alter script for the resource database fails as the resource database
doesnt exist in sysdatabases. I've tried simply moving the resource
files to the new location but then sql wont start as it cant find the
files.
Anybody have any ideas how to resolve this issue?
Thanks
MichaelThe instructions in Books Online for moving the master and resource database
files should work. However, we made some corrections to the procedure in
April 2006. If you don't have the latest version of Books Online, you might
be running into a problem.
I suggest that you download the latest version fo BOL (see the link in my
signature) and then starting with step 7 (Start the instance of SQL Server
in master-only recovery mode...), see if you can get the resource database
moved. If you still run into trouble, please report the exact error
message you're getting.
Regards,
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Michael" <michael@.hampel.fsnet.co.uk> wrote in message
news:1183985960.779841.257510@.k79g2000hse.googlegroups.com...
> We have a sql 2005 installation that has service pack 2 applied. All
> the system databases apart from the resource database have been moved
> from the default data directory to our own data directory on a
> seperate drive and this went ok. We've done this basically to make the
> back up process more efficient in our environment. The issue is that
> we cant find a way to move the resource database to the new location
> and this I beleive needs to be kept with the master database. We've
> followed the BOL instructions for moving system databases but the
> Alter script for the resource database fails as the resource database
> doesnt exist in sysdatabases. I've tried simply moving the resource
> files to the new location but then sql wont start as it cant find the
> files.
> Anybody have any ideas how to resolve this issue?
> Thanks
> Michael
>sql

Move/Upgrade SQL 6.5 Databases to SQL 2K

I am running into problems when attempting to move
databases from SQL 6.5 to SQL 2K. There is a known bug in
SQL2K, SP3a when using the migration wizard. The work
around involved installing 6.5 on a Windows 2000 Server
but we ran into problems with that as well. Microsoft
actually removed their White Paper on this subject from
their site. Does anyone know of a good information source
for instructions on how to do this?Try to detach the databases from source 6.5 server, copy
the your database mdf and ldf file to your destination
server server and attach the database. Also look into
moving your logins account to that server
>--Original Message--
>I am running into problems when attempting to move
>databases from SQL 6.5 to SQL 2K. There is a known bug
in
>SQL2K, SP3a when using the migration wizard. The work
>around involved installing 6.5 on a Windows 2000 Server
>but we ran into problems with that as well. Microsoft
>actually removed their White Paper on this subject from
>their site. Does anyone know of a good information
source
>for instructions on how to do this?
>.
>

move/upgrade MSDE to SQL 2000

Need some help with moving/upgrading 3 MSDE databases (stored locally on 3 different servers) to a single SQL 2000 server (new instance). Can this be done? If so, how??

Thanks for any responsesStop the MSSQL service. Find the MDF and LDF files (C:\Program Files\Microsoft SQL Server\MSSQL\Data\ ) and copy them to the SQL Server. Then attach the databases on the destination. Watch not to overlay any MDF/LDF files on the destination server.sql

Move/Copy Table(s) Between Databases

I need to learn how to move or copy a couple of tables from one database to another. The tables are defined but contain no data.Quick and easy an INSERT INTO statement but avoid SELECT INTO. Run a search for both in the BOL(books online). Hope this helps.|||Thanks Caddre but I've learned I can use the 'Export Data' DTS Wizard which is fast and easy.

move users

hi all
how can i move all users from one sql instanz to another ?
with all corresponding privilegs?
i have to move 10 databases from one sql server to a second and then
delete the databases on the first one.
thankx for your help
mike schwarz
www.ctek.ch
These should get you going:
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/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Mike Schwarz" <ctek@.ctek.ch> wrote in message
news:%23cLwTDMrEHA.2776@.TK2MSFTNGP14.phx.gbl...
> hi all
> how can i move all users from one sql instanz to another ?
> with all corresponding privilegs?
> i have to move 10 databases from one sql server to a second and then
> delete the databases on the first one.
> thankx for your help
> mike schwarz
> www.ctek.ch
>
|||thank you for the reply... seems to be a very hard task, lots of options
and possibilities. it seems that the SID is the most common problem. i
will try this with the utility you gave me the link
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

move users

hi all
how can i move all users from one sql instanz to another ?
with all corresponding privilegs?
i have to move 10 databases from one sql server to a second and then
delete the databases on the first one.
thankx for your help
mike schwarz
www.ctek.chThese should get you going:
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
"Mike Schwarz" <ctek@.ctek.ch> wrote in message
news:%23cLwTDMrEHA.2776@.TK2MSFTNGP14.phx.gbl...
> hi all
> how can i move all users from one sql instanz to another ?
> with all corresponding privilegs?
> i have to move 10 databases from one sql server to a second and then
> delete the databases on the first one.
> thankx for your help
> mike schwarz
> www.ctek.ch
>sql

Move Transaction Log

I want to move the transaction log from its current
location (same as the databases) to a different drive for
performance reasons. How do I do that?
Check out sp_detach_db and sp_attach_db in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Keith" <dkcamc@.yahoo.com> wrote in message
news:1d4301c49b5e$504e5b00$3a01280a@.phx.gbl...
I want to move the transaction log from its current
location (same as the databases) to a different drive for
performance reasons. How do I do that?
|||http://www.support.microsoft.com/?id=224071
Andrew J. Kelly SQL MVP
"Keith" <dkcamc@.yahoo.com> wrote in message
news:1d4301c49b5e$504e5b00$3a01280a@.phx.gbl...
> I want to move the transaction log from its current
> location (same as the databases) to a different drive for
> performance reasons. How do I do that?
|||Sorry if I'm dragging an old thread up, but I need to do this also. I've
tried using the SP_Detatch command, but it refuses to move the log, due to
having replications attached. Is there anyway I can move the TNS log without
deleting the replications and starting again?
Cheers
Steve
"Keith" wrote:

> I want to move the transaction log from its current
> location (same as the databases) to a different drive for
> performance reasons. How do I do that?
>

Move Transaction Log

I want to move the transaction log from its current
location (same as the databases) to a different drive for
performance reasons. How do I do that?Check out sp_detach_db and sp_attach_db in the BOL.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Keith" <dkcamc@.yahoo.com> wrote in message
news:1d4301c49b5e$504e5b00$3a01280a@.phx.gbl...
I want to move the transaction log from its current
location (same as the databases) to a different drive for
performance reasons. How do I do that?|||http://www.support.microsoft.com/?id=224071
Andrew J. Kelly SQL MVP
"Keith" <dkcamc@.yahoo.com> wrote in message
news:1d4301c49b5e$504e5b00$3a01280a@.phx.gbl...
> I want to move the transaction log from its current
> location (same as the databases) to a different drive for
> performance reasons. How do I do that?|||Sorry if I'm dragging an old thread up, but I need to do this also. I've
tried using the SP_Detatch command, but it refuses to move the log, due to
having replications attached. Is there anyway I can move the TNS log without
deleting the replications and starting again?
Cheers
Steve
"Keith" wrote:
> I want to move the transaction log from its current
> location (same as the databases) to a different drive for
> performance reasons. How do I do that?
>