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 DBs to new machine with same name

I need to move all my SQL DB's from an old machine to a new machine. For
ease, I'm going to keep the new server the same name and IP address.
My plan is as follows
1) Ensure all users out of all DBs
2) Disconnect server from network
3) Performance full backups of ALL databases
4) Shutdown SQL Server service
5) Re-connect server to network and transfer DB backups to new machine
6) Shut down old server
7) Rename new server to old servers name and change to old servers IP,
reboot
Now here is where I need advice. To restore all my DBs, I need existing
DBs with matching names, correct? So the master DB and the other default
DBs I can restore over, but all of my additional DBs I cannot. Do I simply
create a new DB with a matching name to my additional DBs and then restore
over them?
Should I restore the master and default DBs first, then create the intial
DBs, then restore. Or do I create teh DBs, restore them all, then do the
master? What's the correct order?First advice, if you don't have the right feeling about this, don't do it.
Which SQL Servers are you using ? Both the same version ?
Backup/restore is not the only option. Attaching and detaching is also an
option.
Server renaming will stop SQL Server 7 from running, you'll need the setup
cd to revive sql server. (not a problem with SQL2000)
You don't need new databases, they will be created during restore.
You can not restore the master DB.
Search support.microsoft.com for 'move database' on SQL Server, you'll find
a lot of info (scripts for moving users, ...)
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Evan Mann" <ask@.for.it> wrote in message
news:2e3%b.137969$jH.860266@.twister.tampabay.rr.com...
> I need to move all my SQL DB's from an old machine to a new machine. For
> ease, I'm going to keep the new server the same name and IP address.
> My plan is as follows
> 1) Ensure all users out of all DBs
> 2) Disconnect server from network
> 3) Performance full backups of ALL databases
> 4) Shutdown SQL Server service
> 5) Re-connect server to network and transfer DB backups to new machine
> 6) Shut down old server
> 7) Rename new server to old servers name and change to old servers IP,
> reboot
> Now here is where I need advice. To restore all my DBs, I need existing
> DBs with matching names, correct? So the master DB and the other default
> DBs I can restore over, but all of my additional DBs I cannot. Do I
simply
> create a new DB with a matching name to my additional DBs and then restore
> over them?
> Should I restore the master and default DBs first, then create the intial
> DBs, then restore. Or do I create teh DBs, restore them all, then do the
> master? What's the correct order?
>
>|||These are 2 separate machines, but which have the same exact version of SQL
Server 2000 SP3a+hotfixes. I know what is involved with renaming a server.
I won't be renaming the server with any of these DBs I am moving attached.
The backup/resotre procedure seems to be the easiest. Does this not get the
entire database, including all the users, stored procedures, etc. Why do I
need to move users if I am changing machines? Is this not all kept in the
DB itself?
"Nico De Greef" <ndg@.denco.be> wrote in message
news:%23muWU%237%23DHA.2516@.TK2MSFTNGP11.phx.gbl...
> First advice, if you don't have the right feeling about this, don't do it.
> Which SQL Servers are you using ? Both the same version ?
> Backup/restore is not the only option. Attaching and detaching is also an
> option.
> Server renaming will stop SQL Server 7 from running, you'll need the setup
> cd to revive sql server. (not a problem with SQL2000)
> You don't need new databases, they will be created during restore.
> You can not restore the master DB.
> Search support.microsoft.com for 'move database' on SQL Server, you'll
find
> a lot of info (scripts for moving users, ...)
> --
> Nico De Greef
> Belgium
> Freelance Software Architect
> MCP, MCSD, .NET certified
>
> "Evan Mann" <ask@.for.it> wrote in message
> news:2e3%b.137969$jH.860266@.twister.tampabay.rr.com...
For
existing
default
> simply
restore
intial
the
>
>|||Evan
Look at this very useful article.Vyas is explained detailed step by step how
to move all data to the new one.
http://vyaskn.tripod.com/moving_sql_server.htm
"Evan Mann" <ask@.for.it> wrote in message
news:HG9%b.138556$jH.890194@.twister.tampabay.rr.com...
> These are 2 separate machines, but which have the same exact version of
SQL
> Server 2000 SP3a+hotfixes. I know what is involved with renaming a
server.
> I won't be renaming the server with any of these DBs I am moving
attached.
> The backup/resotre procedure seems to be the easiest. Does this not get
the
> entire database, including all the users, stored procedures, etc. Why do
I
> need to move users if I am changing machines? Is this not all kept in the
> DB itself?
>
> "Nico De Greef" <ndg@.denco.be> wrote in message
> news:%23muWU%237%23DHA.2516@.TK2MSFTNGP11.phx.gbl...
it.
an
setup
> find
> For
> existing
> default
> restore
> intial
> the
>

Moving all DBs to new machine with same name

I need to move all my SQL DB's from an old machine to a new machine. For
ease, I'm going to keep the new server the same name and IP address.
My plan is as follows
1) Ensure all users out of all DBs
2) Disconnect server from network
3) Performance full backups of ALL databases
4) Shutdown SQL Server service
5) Re-connect server to network and transfer DB backups to new machine
6) Shut down old server
7) Rename new server to old servers name and change to old servers IP,
reboot
Now here is where I need advice. To restore all my DBs, I need existing
DBs with matching names, correct? So the master DB and the other default
DBs I can restore over, but all of my additional DBs I cannot. Do I simply
create a new DB with a matching name to my additional DBs and then restore
over them?
Should I restore the master and default DBs first, then create the intial
DBs, then restore. Or do I create teh DBs, restore them all, then do the
master? What's the correct order?First advice, if you don't have the right feeling about this, don't do it.
Which SQL Servers are you using ? Both the same version ?
Backup/restore is not the only option. Attaching and detaching is also an
option.
Server renaming will stop SQL Server 7 from running, you'll need the setup
cd to revive sql server. (not a problem with SQL2000)
You don't need new databases, they will be created during restore.
You can not restore the master DB.
Search support.microsoft.com for 'move database' on SQL Server, you'll find
a lot of info (scripts for moving users, ...)
--
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Evan Mann" <ask@.for.it> wrote in message
news:2e3%b.137969$jH.860266@.twister.tampabay.rr.com...
> I need to move all my SQL DB's from an old machine to a new machine. For
> ease, I'm going to keep the new server the same name and IP address.
> My plan is as follows
> 1) Ensure all users out of all DBs
> 2) Disconnect server from network
> 3) Performance full backups of ALL databases
> 4) Shutdown SQL Server service
> 5) Re-connect server to network and transfer DB backups to new machine
> 6) Shut down old server
> 7) Rename new server to old servers name and change to old servers IP,
> reboot
> Now here is where I need advice. To restore all my DBs, I need existing
> DBs with matching names, correct? So the master DB and the other default
> DBs I can restore over, but all of my additional DBs I cannot. Do I
simply
> create a new DB with a matching name to my additional DBs and then restore
> over them?
> Should I restore the master and default DBs first, then create the intial
> DBs, then restore. Or do I create teh DBs, restore them all, then do the
> master? What's the correct order?
>
>|||These are 2 separate machines, but which have the same exact version of SQL
Server 2000 SP3a+hotfixes. I know what is involved with renaming a server.
I won't be renaming the server with any of these DBs I am moving attached.
The backup/resotre procedure seems to be the easiest. Does this not get the
entire database, including all the users, stored procedures, etc. Why do I
need to move users if I am changing machines? Is this not all kept in the
DB itself?
"Nico De Greef" <ndg@.denco.be> wrote in message
news:%23muWU%237%23DHA.2516@.TK2MSFTNGP11.phx.gbl...
> First advice, if you don't have the right feeling about this, don't do it.
> Which SQL Servers are you using ? Both the same version ?
> Backup/restore is not the only option. Attaching and detaching is also an
> option.
> Server renaming will stop SQL Server 7 from running, you'll need the setup
> cd to revive sql server. (not a problem with SQL2000)
> You don't need new databases, they will be created during restore.
> You can not restore the master DB.
> Search support.microsoft.com for 'move database' on SQL Server, you'll
find
> a lot of info (scripts for moving users, ...)
> --
> Nico De Greef
> Belgium
> Freelance Software Architect
> MCP, MCSD, .NET certified
>
> "Evan Mann" <ask@.for.it> wrote in message
> news:2e3%b.137969$jH.860266@.twister.tampabay.rr.com...
> > I need to move all my SQL DB's from an old machine to a new machine.
For
> > ease, I'm going to keep the new server the same name and IP address.
> >
> > My plan is as follows
> >
> > 1) Ensure all users out of all DBs
> > 2) Disconnect server from network
> > 3) Performance full backups of ALL databases
> > 4) Shutdown SQL Server service
> > 5) Re-connect server to network and transfer DB backups to new machine
> > 6) Shut down old server
> > 7) Rename new server to old servers name and change to old servers IP,
> > reboot
> >
> > Now here is where I need advice. To restore all my DBs, I need
existing
> > DBs with matching names, correct? So the master DB and the other
default
> > DBs I can restore over, but all of my additional DBs I cannot. Do I
> simply
> > create a new DB with a matching name to my additional DBs and then
restore
> > over them?
> >
> > Should I restore the master and default DBs first, then create the
intial
> > DBs, then restore. Or do I create teh DBs, restore them all, then do
the
> > master? What's the correct order?
> >
> >
> >
>
>|||Evan
Look at this very useful article.Vyas is explained detailed step by step how
to move all data to the new one.
http://vyaskn.tripod.com/moving_sql_server.htm
"Evan Mann" <ask@.for.it> wrote in message
news:HG9%b.138556$jH.890194@.twister.tampabay.rr.com...
> These are 2 separate machines, but which have the same exact version of
SQL
> Server 2000 SP3a+hotfixes. I know what is involved with renaming a
server.
> I won't be renaming the server with any of these DBs I am moving
attached.
> The backup/resotre procedure seems to be the easiest. Does this not get
the
> entire database, including all the users, stored procedures, etc. Why do
I
> need to move users if I am changing machines? Is this not all kept in the
> DB itself?
>
> "Nico De Greef" <ndg@.denco.be> wrote in message
> news:%23muWU%237%23DHA.2516@.TK2MSFTNGP11.phx.gbl...
> > First advice, if you don't have the right feeling about this, don't do
it.
> >
> > Which SQL Servers are you using ? Both the same version ?
> >
> > Backup/restore is not the only option. Attaching and detaching is also
an
> > option.
> > Server renaming will stop SQL Server 7 from running, you'll need the
setup
> > cd to revive sql server. (not a problem with SQL2000)
> > You don't need new databases, they will be created during restore.
> > You can not restore the master DB.
> >
> > Search support.microsoft.com for 'move database' on SQL Server, you'll
> find
> > a lot of info (scripts for moving users, ...)
> >
> > --
> > Nico De Greef
> > Belgium
> > Freelance Software Architect
> > MCP, MCSD, .NET certified
> >
> >
> >
> > "Evan Mann" <ask@.for.it> wrote in message
> > news:2e3%b.137969$jH.860266@.twister.tampabay.rr.com...
> > > I need to move all my SQL DB's from an old machine to a new machine.
> For
> > > ease, I'm going to keep the new server the same name and IP address.
> > >
> > > My plan is as follows
> > >
> > > 1) Ensure all users out of all DBs
> > > 2) Disconnect server from network
> > > 3) Performance full backups of ALL databases
> > > 4) Shutdown SQL Server service
> > > 5) Re-connect server to network and transfer DB backups to new machine
> > > 6) Shut down old server
> > > 7) Rename new server to old servers name and change to old servers IP,
> > > reboot
> > >
> > > Now here is where I need advice. To restore all my DBs, I need
> existing
> > > DBs with matching names, correct? So the master DB and the other
> default
> > > DBs I can restore over, but all of my additional DBs I cannot. Do I
> > simply
> > > create a new DB with a matching name to my additional DBs and then
> restore
> > > over them?
> > >
> > > Should I restore the master and default DBs first, then create the
> intial
> > > DBs, then restore. Or do I create teh DBs, restore them all, then do
> the
> > > master? What's the correct order?
> > >
> > >
> > >
> >
> >
> >
>sql

Moving a Windows 2003 Cluster to a New Forest

I am looking to decomission an old AD forest and move all it's resources to a
new AD forest. Both forests exist today. I have a 2 node Windows 2003
cluster with 3 resource groups -- the cluster resource group and two (2) SQL
2000 server resource groups.
My questions are...
1) Is this sort of migration possible/support?
2) If so, how do I go about doing it (or finding out how to do it).
Thanks.
Bill
How to change domains for a SQL Server 2000 or SQL Server 2005 failover
cluster
http://support.microsoft.com/kb/319016/en-us
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Bill J." <Bill J.@.discussions.microsoft.com> wrote in message
news:77BA4242-BDBB-4A83-9A46-52F57A055F2F@.microsoft.com...
>I am looking to decomission an old AD forest and move all it's resources to
>a
> new AD forest. Both forests exist today. I have a 2 node Windows 2003
> cluster with 3 resource groups -- the cluster resource group and two (2)
> SQL
> 2000 server resource groups.
> My questions are...
> 1) Is this sort of migration possible/support?
> 2) If so, how do I go about doing it (or finding out how to do it).
> Thanks.
> Bill
sql

moving a database from old to new server?

Hi all. I know that since you know nothing about my database it will
be a stretch to answer this - but does anyone have a boiler plate
checklist for moving a database, dts packages, agent jobs, etc...from
one server to another? Maybe something that just says I should at
least do x,y,z.

Also if anyone out there has any hard won experience/advice about
gotchas when doing this I would love to hear it. We have outgrown a
server and are installing a new one this week that we need to move
over to.

Thanks!If you are migrating complete Server and If you can keep drive letters same with same directory structure. Detach all user databases . Move Ldf and Mdf files to new server in same location, Backup master/msdb on old server

and restore them to new. Make sure you have Windows accounts moved to new Server too. I don't think you will have any issues at all

Moving a database application to SQL Server 2005 Express

I have been programming an application with VC++ 2005 and SQL Server 2005. I have converted an old 16-bit database to 32-bit managed code and SQL server and the application seems to be good. Now I want to deploy the application to another server for testing.

I have installed XP SP2, Windows Installer 3.1, Net framework 2.0 and SQL Server 2005 express to the test server. I have transferred the application with WI 3.1 and the program works well in the test server till the first SQL command. I have made a back up of the database and restored it in the test server. In the test server I can log in the database with Server Management studio and I can read the data there correctly. I have enabled both named pipes and TCP/IP for the database in the test server. With Surface Area Configuration I have enabled Local and Remote Connections Using both TCP/IP and named pipes. I only need Windows authentication at this time.

After all this when I come to the first SQL command in the application on the test server I receive the error message:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server).

My connection string to the database is:
'connection->ConnectionString = "Persist Security Info=False; Integrated Security =SSPI;"
"Data Source=TESTSERVER; Initial Catalog=TESTDATABASE;";'

When I use "Data Source=DevelopmentServer", the application works well on the development server.

Can't understand what is still wrong. Can you possibly have an answer for me?

hi,

are you connecting locally or from remote clients?

check with the SQL Server Configuration Manager the appropriate network protocols are enabled as well..

regards

|||

Thank you for your answer!

Actually I am connecting to the TestServer only locally. I enabled TCP/IP and named pipes only because of the error message (40) and I enabled those features using SQL Server Configuration Manager as you suggest. This is done but I still receive the error.

|||

Finally, the solution found!

When connecting to the TestServer the connection is not actually to the server but the instance. The connection string must have format TestServer\SQLExpress. I had this and the compiler could compile and link the code without errors, but there was a warning of wrong character sequence. When I have to repeat the connection command repeatedly, I had defined the string with

#define COMMANDSTRING "Data Source=TestServer\SQLExpress"

The correct format is

#define COMMANDSTRING "Data Source=TestServer\\SQLExpress"

although the backslash is within quotation marks.

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

Move sql database to diverend harddisk with same drive letter

I have a small d: drive and like to change this with a bigger one, how can i
move the database from old drive to new drive and use te same drive letter
after the database move? i installed the new harddrive with letter e: Can i
stop the sql server and copy the whole drive d: to drive e: and than change
driveletter to d:?
ThanksYou can move the data file. you would need to deatach the database, move the
file phisicaly to another drive (mdf or ndf and ldf) then atach the database
and it will ask you for a location of the new data files (both mdf/ndf and
ldf). point to the newly created drive and new location of your files and
that is it.
Do me a favor, it has been 2 years since I did it; google it "move data
files to another drive" and you will get tons of hits. This is non system DB
?
This is one of your own databases?
"Luq" wrote:

> I have a small d: drive and like to change this with a bigger one, how can
i
> move the database from old drive to new drive and use te same drive letter
> after the database move? i installed the new harddrive with letter e: Can
i
> stop the sql server and copy the whole drive d: to drive e: and than chang
e
> driveletter to d:?
> Thanks|||Her you go Luq
http://support.microsoft.com/kb/224071
"Luq" wrote:

> I have a small d: drive and like to change this with a bigger one, how can
i
> move the database from old drive to new drive and use te same drive letter
> after the database move? i installed the new harddrive with letter e: Can
i
> stop the sql server and copy the whole drive d: to drive e: and than chang
e
> driveletter to d:?
> Thanks

Move sql database to diverend harddisk with same drive letter

I have a small d: drive and like to change this with a bigger one, how can i
move the database from old drive to new drive and use te same drive letter
after the database move? i installed the new harddrive with letter e: Can i
stop the sql server and copy the whole drive d: to drive e: and than change
driveletter to d:?
ThanksYou can move the data file. you would need to deatach the database, move the
file phisicaly to another drive (mdf or ndf and ldf) then atach the database
and it will ask you for a location of the new data files (both mdf/ndf and
ldf). point to the newly created drive and new location of your files and
that is it.
Do me a favor, it has been 2 years since I did it; google it "move data
files to another drive" and you will get tons of hits. This is non system DB?
This is one of your own databases?
"Luq" wrote:
> I have a small d: drive and like to change this with a bigger one, how can i
> move the database from old drive to new drive and use te same drive letter
> after the database move? i installed the new harddrive with letter e: Can i
> stop the sql server and copy the whole drive d: to drive e: and than change
> driveletter to d:?
> Thanks|||Her you go Luq
http://support.microsoft.com/kb/224071
"Luq" wrote:
> I have a small d: drive and like to change this with a bigger one, how can i
> move the database from old drive to new drive and use te same drive letter
> after the database move? i installed the new harddrive with letter e: Can i
> stop the sql server and copy the whole drive d: to drive e: and than change
> driveletter to d:?
> Thanks

Move RS from old server to new server

I need some assistance in moving RS databases from an old server that has SQL
Server 2000 to a new machine. I was able to move the ReportServer and
ReportServerTempDB to the new machine and seems to see all the reports, but
I'm getting an error when trying to run them. "An error has occurred during
report processing. (rsProcessingAborted). Bad Data".
I had tried following http://support.microsoft.com/default.aspx?scid=842425
but no luck.
Here's the steps that I had done.
1) Detach Both databases from old server that host Reporting Services.
(ReportServer and ReportServerTempDB)
2) Attached them to new server.
3) Did "Delete from Keys where client > -1" on the new server
4) Did rsactivate to initialize.
5) Did RSConfig (Not sure if this needs to be done on source or
destination) I did this on the new server.
Only thing i left out is RSKeyMgmt.exe since that says that it will extract
the key from my source. Not sure if this will mess up the source server
since that is still be use until I can move everything to the new server.
So can someone that has move Reporting Services to a new server please help
me out?
P.S. The reporting services server does not host any of our ERP databases.
It is on a different server and the reports will grab those using datasources.
Thanks in advance.To answer your question about extracting the Key, the reason it asks you if
you want to extract the key is because this is a security issue if you leave
the file out in an un-secure location. You RS installation will continue
just fine after extracting the key; think of this as more of a BACKUP then as
an EXTRACTION.
"chang" wrote:
> I need some assistance in moving RS databases from an old server that has SQL
> Server 2000 to a new machine. I was able to move the ReportServer and
> ReportServerTempDB to the new machine and seems to see all the reports, but
> I'm getting an error when trying to run them. "An error has occurred during
> report processing. (rsProcessingAborted). Bad Data".
> I had tried following http://support.microsoft.com/default.aspx?scid=842425
> but no luck.
> Here's the steps that I had done.
> 1) Detach Both databases from old server that host Reporting Services.
> (ReportServer and ReportServerTempDB)
> 2) Attached them to new server.
> 3) Did "Delete from Keys where client > -1" on the new server
> 4) Did rsactivate to initialize.
> 5) Did RSConfig (Not sure if this needs to be done on source or
> destination) I did this on the new server.
> Only thing i left out is RSKeyMgmt.exe since that says that it will extract
> the key from my source. Not sure if this will mess up the source server
> since that is still be use until I can move everything to the new server.
> So can someone that has move Reporting Services to a new server please help
> me out?
> P.S. The reporting services server does not host any of our ERP databases.
> It is on a different server and the reports will grab those using datasources.
> Thanks in advance.

Move replicated database to a new server

I have the intention to move SQLserver (used with dynamic merge replication,
also distributor) from an old server to a new one. Can I copy all the files
to the new server without reconfiguration?
If the server has the same netbios name then restoring master, msdb,
distribution and the published database is a possibility. Have a look in BOL
for the topic "Strategies for Backing Up and Restoring Merge Replication".
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Move DB to new server

We just bought a new server for our 23 DB. We are running SQL 2000.
What is the best way to transfer our DB form our old server to our new one.
DTS?
Thanks for any documentation that will help me acheive my goal.
Dan
Hi,
2 approaches:-
I.
1. SP_DETACH_DB DBNAME
2. Move the .mdf and .ldf files to new server
3. SP_ATTACH_DB dbanme,@.filename1='',@.filename2=''
or else you can move a database using below steps,
II.
1. Do a Backup of database
2. Copy the .BAK file to new server
3. Restore the database
see below link for details:-
http://support.microsoft.com/default...b;en-us;314546
Another undocumented method:-
If you can maintain the same directory structure in the new server, Stop the
MSSQL server service in both servers and copy all the .MDF and .LDF files
to the identical folder in new server and then start the MSSQL service.
Once the service come up , you can use the below system stored procedure to
change the server name.
sp_dropserver 'oldservername'
go
sp_addserver 'newservername','local'
After this restart the SQL server service.
Thanks
Hari
MCDBA
"Dan M" <Dan.Morrow@.nodoby.com> wrote in message
news:ecAlcZOZEHA.3476@.tk2msftngp13.phx.gbl...
> We just bought a new server for our 23 DB. We are running SQL 2000.
> What is the best way to transfer our DB form our old server to our new
one.
> DTS?
> Thanks for any documentation that will help me acheive my goal.
> Dan
>
|||If you want to transfer users, jobs, as well:
Use the simple Copy Database Wizard to copy all databases, logins. Ths is
less admin hassle and imo this is a great tool. You may need to reset the
users default databases once completed.
The below method does not migrate the users from the master database or the
Jobs from the MSDB etc..
Regards
James
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uFKSHgOZEHA.3988@.tk2msftngp13.phx.gbl...
> Hi,
> 2 approaches:-
> I.
> 1. SP_DETACH_DB DBNAME
> 2. Move the .mdf and .ldf files to new server
> 3. SP_ATTACH_DB dbanme,@.filename1='',@.filename2=''
>
> or else you can move a database using below steps,
> II.
> 1. Do a Backup of database
> 2. Copy the .BAK file to new server
> 3. Restore the database
> see below link for details:-
> http://support.microsoft.com/default...b;en-us;314546
> Another undocumented method:-
> --
> If you can maintain the same directory structure in the new server, Stop
the
> MSSQL server service in both servers and copy all the .MDF and .LDF files
> to the identical folder in new server and then start the MSSQL service.
> Once the service come up , you can use the below system stored procedure
to
> change the server name.
> sp_dropserver 'oldservername'
> go
> sp_addserver 'newservername','local'
> After this restart the SQL server service.
>
> --
> Thanks
> Hari
> MCDBA
> "Dan M" <Dan.Morrow@.nodoby.com> wrote in message
> news:ecAlcZOZEHA.3476@.tk2msftngp13.phx.gbl...
> one.
>

Move DB to new server

We just bought a new server for our 23 DB. We are running SQL 2000.
What is the best way to transfer our DB form our old server to our new one.
DTS?
Thanks for any documentation that will help me acheive my goal.
DanHi,
2 approaches:-
I.
1. SP_DETACH_DB DBNAME
2. Move the .mdf and .ldf files to new server
3. SP_ATTACH_DB dbanme,@.filename1='',@.filename2=''
or else you can move a database using below steps,
II.
1. Do a Backup of database
2. Copy the .BAK file to new server
3. Restore the database
see below link for details:-
http://support.microsoft.com/defaul...kb;en-us;314546
Another undocumented method:-
--
If you can maintain the same directory structure in the new server, Stop the
MSSQL server service in both servers and copy all the .MDF and .LDF files
to the identical folder in new server and then start the MSSQL service.
Once the service come up , you can use the below system stored procedure to
change the server name.
sp_dropserver 'oldservername'
go
sp_addserver 'newservername','local'
After this restart the SQL server service.
Thanks
Hari
MCDBA
"Dan M" <Dan.Morrow@.nodoby.com> wrote in message
news:ecAlcZOZEHA.3476@.tk2msftngp13.phx.gbl...
> We just bought a new server for our 23 DB. We are running SQL 2000.
> What is the best way to transfer our DB form our old server to our new
one.
> DTS?
> Thanks for any documentation that will help me acheive my goal.
> Dan
>|||If you want to transfer users, jobs, as well:
Use the simple Copy Database Wizard to copy all databases, logins. Ths is
less admin hassle and imo this is a great tool. You may need to reset the
users default databases once completed.
The below method does not migrate the users from the master database or the
Jobs from the MSDB etc..
Regards
James
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uFKSHgOZEHA.3988@.tk2msftngp13.phx.gbl...
> Hi,
> 2 approaches:-
> I.
> 1. SP_DETACH_DB DBNAME
> 2. Move the .mdf and .ldf files to new server
> 3. SP_ATTACH_DB dbanme,@.filename1='',@.filename2=''
>
> or else you can move a database using below steps,
> II.
> 1. Do a Backup of database
> 2. Copy the .BAK file to new server
> 3. Restore the database
> see below link for details:-
> http://support.microsoft.com/defaul...kb;en-us;314546
> Another undocumented method:-
> --
> If you can maintain the same directory structure in the new server, Stop
the
> MSSQL server service in both servers and copy all the .MDF and .LDF files
> to the identical folder in new server and then start the MSSQL service.
> Once the service come up , you can use the below system stored procedure
to
> change the server name.
> sp_dropserver 'oldservername'
> go
> sp_addserver 'newservername','local'
> After this restart the SQL server service.
>
> --
> Thanks
> Hari
> MCDBA
> "Dan M" <Dan.Morrow@.nodoby.com> wrote in message
> news:ecAlcZOZEHA.3476@.tk2msftngp13.phx.gbl...
> one.
>

Move DB to new server

We just bought a new server for our 23 DB. We are running SQL 2000.
What is the best way to transfer our DB form our old server to our new one.
DTS?
Thanks for any documentation that will help me acheive my goal.
DanHi,
2 approaches:-
I.
1. SP_DETACH_DB DBNAME
2. Move the .mdf and .ldf files to new server
3. SP_ATTACH_DB dbanme,@.filename1='',@.filename2=''
or else you can move a database using below steps,
II.
1. Do a Backup of database
2. Copy the .BAK file to new server
3. Restore the database
see below link for details:-
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
Another undocumented method:-
--
If you can maintain the same directory structure in the new server, Stop the
MSSQL server service in both servers and copy all the .MDF and .LDF files
to the identical folder in new server and then start the MSSQL service.
Once the service come up , you can use the below system stored procedure to
change the server name.
sp_dropserver 'oldservername'
go
sp_addserver 'newservername','local'
After this restart the SQL server service.
Thanks
Hari
MCDBA
"Dan M" <Dan.Morrow@.nodoby.com> wrote in message
news:ecAlcZOZEHA.3476@.tk2msftngp13.phx.gbl...
> We just bought a new server for our 23 DB. We are running SQL 2000.
> What is the best way to transfer our DB form our old server to our new
one.
> DTS?
> Thanks for any documentation that will help me acheive my goal.
> Dan
>|||If you want to transfer users, jobs, as well:
Use the simple Copy Database Wizard to copy all databases, logins. Ths is
less admin hassle and imo this is a great tool. You may need to reset the
users default databases once completed.
The below method does not migrate the users from the master database or the
Jobs from the MSDB etc..
Regards
James
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:uFKSHgOZEHA.3988@.tk2msftngp13.phx.gbl...
> Hi,
> 2 approaches:-
> I.
> 1. SP_DETACH_DB DBNAME
> 2. Move the .mdf and .ldf files to new server
> 3. SP_ATTACH_DB dbanme,@.filename1='',@.filename2=''
>
> or else you can move a database using below steps,
> II.
> 1. Do a Backup of database
> 2. Copy the .BAK file to new server
> 3. Restore the database
> see below link for details:-
> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
> Another undocumented method:-
> --
> If you can maintain the same directory structure in the new server, Stop
the
> MSSQL server service in both servers and copy all the .MDF and .LDF files
> to the identical folder in new server and then start the MSSQL service.
> Once the service come up , you can use the below system stored procedure
to
> change the server name.
> sp_dropserver 'oldservername'
> go
> sp_addserver 'newservername','local'
> After this restart the SQL server service.
>
> --
> Thanks
> Hari
> MCDBA
> "Dan M" <Dan.Morrow@.nodoby.com> wrote in message
> news:ecAlcZOZEHA.3476@.tk2msftngp13.phx.gbl...
> > We just bought a new server for our 23 DB. We are running SQL 2000.
> >
> > What is the best way to transfer our DB form our old server to our new
> one.
> > DTS?
> >
> > Thanks for any documentation that will help me acheive my goal.
> >
> > Dan
> >
> >
>

Move DB by backup/restore or SP_Detach?

Question: Which method should I use to do the simultaneous change from SQL
2000 to 2005, and the change from an old PC (server) to a new one?
Current: SQL 2000 + Old Hardware
New (Future): SQL 2005 + New Hardware
What's the best way to get a single live database from the current to the
new? I expect to add users manually and point the client applications to
the new server. I've done the Upgrade Advisor and resolved a couple minor
things. For testing, I've taken a BAK file from the old server (sql 2000)
and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
2005 in the process, and a few test queries execute properly. So I'm
inclined to just do a backup and restore at that magic moment when we do the
upgrade. And then I'd add logins and jobs manually. Is there some
reason why SP_Detach & Attach is better? At least with a backup/restore
method, I can easily switch back to the old database if something doesn't
work. Plus, I can test this method many times before the big day, but I
can't easily test a detach of this database in advance. Thoughts?
Hi HK
You can use both forms obviously, the detach and attach would be quicker and
you can just take a copy of your data and log files where a backup and
restore will take a bit longer, which ever method you choose i would suggest
you rebuild all your indexes, stored procs, functions and views and then last
run update stats.
Regards
Charl
http://www.sqlserver.co.za
"HK" wrote:

> Question: Which method should I use to do the simultaneous change from SQL
> 2000 to 2005, and the change from an old PC (server) to a new one?
> Current: SQL 2000 + Old Hardware
> New (Future): SQL 2005 + New Hardware
> What's the best way to get a single live database from the current to the
> new? I expect to add users manually and point the client applications to
> the new server. I've done the Upgrade Advisor and resolved a couple minor
> things. For testing, I've taken a BAK file from the old server (sql 2000)
> and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
> and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> 2005 in the process, and a few test queries execute properly. So I'm
> inclined to just do a backup and restore at that magic moment when we do the
> upgrade. And then I'd add logins and jobs manually. Is there some
> reason why SP_Detach & Attach is better? At least with a backup/restore
> method, I can easily switch back to the old database if something doesn't
> work. Plus, I can test this method many times before the big day, but I
> can't easily test a detach of this database in advance. Thoughts?
>
>
|||How to move databases between computers that are running SQL Server
http://support.microsoft.com/default...b;en-us;314546
AMB
"HK" wrote:

> Question: Which method should I use to do the simultaneous change from SQL
> 2000 to 2005, and the change from an old PC (server) to a new one?
> Current: SQL 2000 + Old Hardware
> New (Future): SQL 2005 + New Hardware
> What's the best way to get a single live database from the current to the
> new? I expect to add users manually and point the client applications to
> the new server. I've done the Upgrade Advisor and resolved a couple minor
> things. For testing, I've taken a BAK file from the old server (sql 2000)
> and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
> and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> 2005 in the process, and a few test queries execute properly. So I'm
> inclined to just do a backup and restore at that magic moment when we do the
> upgrade. And then I'd add logins and jobs manually. Is there some
> reason why SP_Detach & Attach is better? At least with a backup/restore
> method, I can easily switch back to the old database if something doesn't
> work. Plus, I can test this method many times before the big day, but I
> can't easily test a detach of this database in advance. Thoughts?
>
>
|||Why would I have to do anything with stored procs?
Re-indexing doesn't seem required, but I can easily do that, and it is a
good idea anyway while I have the servers down. I don't have any functions
or views.
Can you elaborate on "update stats"?
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:FE5CFB19-0B91-48E4-A9C5-97BC0D419D03@.microsoft.com...
> Hi HK
> You can use both forms obviously, the detach and attach would be quicker
and
> you can just take a copy of your data and log files where a backup and
> restore will take a bit longer, which ever method you choose i would
suggest
> you rebuild all your indexes, stored procs, functions and views and then
last[vbcol=seagreen]
> run update stats.
> Regards
> Charl
> --
> http://www.sqlserver.co.za
>
> "HK" wrote:
SQL[vbcol=seagreen]
the[vbcol=seagreen]
to[vbcol=seagreen]
minor[vbcol=seagreen]
2000)[vbcol=seagreen]
FILELISTONLY[vbcol=seagreen]
the[vbcol=seagreen]
backup/restore[vbcol=seagreen]
doesn't[vbcol=seagreen]
|||Hi HK
The reasoning behind this is based on personal experience when we moved a
SQL 2000 DB to SQL 2005, we ran into some performance issues when migrating,
we rebuilt all the objects and ran sp_updatestats to update the table
statistics again and it resolved our problems. MS have made changes to the
query optimizer and to ensure that your procs use the best possible execution
plan i would suggest it. SQL should be intelligent enough to do it but we did
it as a precausionary and it helped our implementation.
Regards
charl
http://www.sqlserver.co.za
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> How to move databases between computers that are running SQL Server
> http://support.microsoft.com/default...b;en-us;314546
>
> AMB
> "HK" wrote:
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:427477
You wrote that you rebuild all the objects. Which types of objects were
rebuilt besides indexes? Thanks.
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:7736D8DD-EEFC-4470-8EF8-D2F08BE8D96A@.microsoft.com...
> Hi HK
> The reasoning behind this is based on personal experience when we moved a
> SQL 2000 DB to SQL 2005, we ran into some performance issues when
migrating,
> we rebuilt all the objects and ran sp_updatestats to update the table
> statistics again and it resolved our problems. MS have made changes to the
> query optimizer and to ensure that your procs use the best possible
execution
> plan i would suggest it. SQL should be intelligent enough to do it but we
did[vbcol=seagreen]
> it as a precausionary and it helped our implementation.
> Regards
> charl
>
> --
> http://www.sqlserver.co.za
>
> "Alejandro Mesa" wrote:
from SQL[vbcol=seagreen]
the[vbcol=seagreen]
applications to[vbcol=seagreen]
minor[vbcol=seagreen]
2000)[vbcol=seagreen]
FILELISTONLY[vbcol=seagreen]
to[vbcol=seagreen]
I'm[vbcol=seagreen]
do the[vbcol=seagreen]
backup/restore[vbcol=seagreen]
doesn't[vbcol=seagreen]
I[vbcol=seagreen]

Move DB by backup/restore or SP_Detach?

Question: Which method should I use to do the simultaneous change from SQL
2000 to 2005, and the change from an old PC (server) to a new one?
Current: SQL 2000 + Old Hardware
New (Future): SQL 2005 + New Hardware
What's the best way to get a single live database from the current to the
new? I expect to add users manually and point the client applications to
the new server. I've done the Upgrade Advisor and resolved a couple minor
things. For testing, I've taken a BAK file from the old server (sql 2000)
and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
2005 in the process, and a few test queries execute properly. So I'm
inclined to just do a backup and restore at that magic moment when we do the
upgrade. And then I'd add logins and jobs manually. Is there some
reason why SP_Detach & Attach is better? At least with a backup/restore
method, I can easily switch back to the old database if something doesn't
work. Plus, I can test this method many times before the big day, but I
can't easily test a detach of this database in advance. Thoughts?Hi HK
You can use both forms obviously, the detach and attach would be quicker and
you can just take a copy of your data and log files where a backup and
restore will take a bit longer, which ever method you choose i would sugges
t
you rebuild all your indexes, stored procs, functions and views and then las
t
run update stats.
Regards
Charl
http://www.sqlserver.co.za
"HK" wrote:

> Question: Which method should I use to do the simultaneous change from SQ
L
> 2000 to 2005, and the change from an old PC (server) to a new one?
> Current: SQL 2000 + Old Hardware
> New (Future): SQL 2005 + New Hardware
> What's the best way to get a single live database from the current to the
> new? I expect to add users manually and point the client applications to
> the new server. I've done the Upgrade Advisor and resolved a couple mino
r
> things. For testing, I've taken a BAK file from the old server (sql 2000)
> and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
> and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> 2005 in the process, and a few test queries execute properly. So I'm
> inclined to just do a backup and restore at that magic moment when we do t
he
> upgrade. And then I'd add logins and jobs manually. Is there some
> reason why SP_Detach & Attach is better? At least with a backup/restore
> method, I can easily switch back to the old database if something doesn't
> work. Plus, I can test this method many times before the big day, but I
> can't easily test a detach of this database in advance. Thoughts?
>
>|||How to move databases between computers that are running SQL Server
http://support.microsoft.com/defaul...kb;en-us;314546
AMB
"HK" wrote:

> Question: Which method should I use to do the simultaneous change from SQ
L
> 2000 to 2005, and the change from an old PC (server) to a new one?
> Current: SQL 2000 + Old Hardware
> New (Future): SQL 2005 + New Hardware
> What's the best way to get a single live database from the current to the
> new? I expect to add users manually and point the client applications to
> the new server. I've done the Upgrade Advisor and resolved a couple mino
r
> things. For testing, I've taken a BAK file from the old server (sql 2000)
> and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
> and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> 2005 in the process, and a few test queries execute properly. So I'm
> inclined to just do a backup and restore at that magic moment when we do t
he
> upgrade. And then I'd add logins and jobs manually. Is there some
> reason why SP_Detach & Attach is better? At least with a backup/restore
> method, I can easily switch back to the old database if something doesn't
> work. Plus, I can test this method many times before the big day, but I
> can't easily test a detach of this database in advance. Thoughts?
>
>|||Why would I have to do anything with stored procs?
Re-indexing doesn't seem required, but I can easily do that, and it is a
good idea anyway while I have the servers down. I don't have any functions
or views.
Can you elaborate on "update stats"?
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:FE5CFB19-0B91-48E4-A9C5-97BC0D419D03@.microsoft.com...
> Hi HK
> You can use both forms obviously, the detach and attach would be quicker
and
> you can just take a copy of your data and log files where a backup and
> restore will take a bit longer, which ever method you choose i would
suggest
> you rebuild all your indexes, stored procs, functions and views and then
last[vbcol=seagreen]
> run update stats.
> Regards
> Charl
> --
> http://www.sqlserver.co.za
>
> "HK" wrote:
>
SQL[vbcol=seagreen]
the[vbcol=seagreen]
to[vbcol=seagreen]
minor[vbcol=seagreen]
2000)[vbcol=seagreen]
FILELISTONLY[vbcol=seagreen]
the[vbcol=seagreen]
backup/restore[vbcol=seagreen]
doesn't[vbcol=seagreen]|||Hi HK
The reasoning behind this is based on personal experience when we moved a
SQL 2000 DB to SQL 2005, we ran into some performance issues when migrating,
we rebuilt all the objects and ran sp_updatestats to update the table
statistics again and it resolved our problems. MS have made changes to the
query optimizer and to ensure that your procs use the best possible executio
n
plan i would suggest it. SQL should be intelligent enough to do it but we di
d
it as a precausionary and it helped our implementation.
Regards
charl
http://www.sqlserver.co.za
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> How to move databases between computers that are running SQL Server
> http://support.microsoft.com/defaul...kb;en-us;314546
>
> AMB
> "HK" wrote:
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:427477
You wrote that you rebuild all the objects. Which types of objects were
rebuilt besides indexes? Thanks.
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:7736D8DD-EEFC-4470-8EF8-D2F08BE8D96A@.microsoft.com...
> Hi HK
> The reasoning behind this is based on personal experience when we moved a
> SQL 2000 DB to SQL 2005, we ran into some performance issues when
migrating,
> we rebuilt all the objects and ran sp_updatestats to update the table
> statistics again and it resolved our problems. MS have made changes to the
> query optimizer and to ensure that your procs use the best possible
execution
> plan i would suggest it. SQL should be intelligent enough to do it but we
did[vbcol=seagreen]
> it as a precausionary and it helped our implementation.
> Regards
> charl
>
> --
> http://www.sqlserver.co.za
>
> "Alejandro Mesa" wrote:
>
from SQL[vbcol=seagreen]
the[vbcol=seagreen]
applications to[vbcol=seagreen]
minor[vbcol=seagreen]
2000)[vbcol=seagreen]
FILELISTONLY[vbcol=seagreen]
to[vbcol=seagreen]
I'm[vbcol=seagreen]
do the[vbcol=seagreen]
backup/restore[vbcol=seagreen]
doesn't[vbcol=seagreen]
I[vbcol=seagreen]

Move DB by backup/restore or SP_Detach?

Question: Which method should I use to do the simultaneous change from SQL
2000 to 2005, and the change from an old PC (server) to a new one?
Current: SQL 2000 + Old Hardware
New (Future): SQL 2005 + New Hardware
What's the best way to get a single live database from the current to the
new? I expect to add users manually and point the client applications to
the new server. I've done the Upgrade Advisor and resolved a couple minor
things. For testing, I've taken a BAK file from the old server (sql 2000)
and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
2005 in the process, and a few test queries execute properly. So I'm
inclined to just do a backup and restore at that magic moment when we do the
upgrade. And then I'd add logins and jobs manually. Is there some
reason why SP_Detach & Attach is better? At least with a backup/restore
method, I can easily switch back to the old database if something doesn't
work. Plus, I can test this method many times before the big day, but I
can't easily test a detach of this database in advance. Thoughts?Hi HK
You can use both forms obviously, the detach and attach would be quicker and
you can just take a copy of your data and log files where a backup and
restore will take a bit longer, which ever method you choose i would suggest
you rebuild all your indexes, stored procs, functions and views and then last
run update stats.
Regards
Charl
--
http://www.sqlserver.co.za
"HK" wrote:
> Question: Which method should I use to do the simultaneous change from SQL
> 2000 to 2005, and the change from an old PC (server) to a new one?
> Current: SQL 2000 + Old Hardware
> New (Future): SQL 2005 + New Hardware
> What's the best way to get a single live database from the current to the
> new? I expect to add users manually and point the client applications to
> the new server. I've done the Upgrade Advisor and resolved a couple minor
> things. For testing, I've taken a BAK file from the old server (sql 2000)
> and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
> and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> 2005 in the process, and a few test queries execute properly. So I'm
> inclined to just do a backup and restore at that magic moment when we do the
> upgrade. And then I'd add logins and jobs manually. Is there some
> reason why SP_Detach & Attach is better? At least with a backup/restore
> method, I can easily switch back to the old database if something doesn't
> work. Plus, I can test this method many times before the big day, but I
> can't easily test a detach of this database in advance. Thoughts?
>
>|||How to move databases between computers that are running SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
AMB
"HK" wrote:
> Question: Which method should I use to do the simultaneous change from SQL
> 2000 to 2005, and the change from an old PC (server) to a new one?
> Current: SQL 2000 + Old Hardware
> New (Future): SQL 2005 + New Hardware
> What's the best way to get a single live database from the current to the
> new? I expect to add users manually and point the client applications to
> the new server. I've done the Upgrade Advisor and resolved a couple minor
> things. For testing, I've taken a BAK file from the old server (sql 2000)
> and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
> and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> 2005 in the process, and a few test queries execute properly. So I'm
> inclined to just do a backup and restore at that magic moment when we do the
> upgrade. And then I'd add logins and jobs manually. Is there some
> reason why SP_Detach & Attach is better? At least with a backup/restore
> method, I can easily switch back to the old database if something doesn't
> work. Plus, I can test this method many times before the big day, but I
> can't easily test a detach of this database in advance. Thoughts?
>
>|||Why would I have to do anything with stored procs?
Re-indexing doesn't seem required, but I can easily do that, and it is a
good idea anyway while I have the servers down. I don't have any functions
or views.
Can you elaborate on "update stats"?
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:FE5CFB19-0B91-48E4-A9C5-97BC0D419D03@.microsoft.com...
> Hi HK
> You can use both forms obviously, the detach and attach would be quicker
and
> you can just take a copy of your data and log files where a backup and
> restore will take a bit longer, which ever method you choose i would
suggest
> you rebuild all your indexes, stored procs, functions and views and then
last
> run update stats.
> Regards
> Charl
> --
> http://www.sqlserver.co.za
>
> "HK" wrote:
> > Question: Which method should I use to do the simultaneous change from
SQL
> > 2000 to 2005, and the change from an old PC (server) to a new one?
> >
> > Current: SQL 2000 + Old Hardware
> > New (Future): SQL 2005 + New Hardware
> >
> > What's the best way to get a single live database from the current to
the
> > new? I expect to add users manually and point the client applications
to
> > the new server. I've done the Upgrade Advisor and resolved a couple
minor
> > things. For testing, I've taken a BAK file from the old server (sql
2000)
> > and imported it onto the new server (sql 2005) using RESTORE
FILELISTONLY
> > and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> > 2005 in the process, and a few test queries execute properly. So I'm
> > inclined to just do a backup and restore at that magic moment when we do
the
> > upgrade. And then I'd add logins and jobs manually. Is there some
> > reason why SP_Detach & Attach is better? At least with a
backup/restore
> > method, I can easily switch back to the old database if something
doesn't
> > work. Plus, I can test this method many times before the big day, but I
> > can't easily test a detach of this database in advance. Thoughts?
> >
> >
> >|||Hi HK
The reasoning behind this is based on personal experience when we moved a
SQL 2000 DB to SQL 2005, we ran into some performance issues when migrating,
we rebuilt all the objects and ran sp_updatestats to update the table
statistics again and it resolved our problems. MS have made changes to the
query optimizer and to ensure that your procs use the best possible execution
plan i would suggest it. SQL should be intelligent enough to do it but we did
it as a precausionary and it helped our implementation.
Regards
charl
http://www.sqlserver.co.za
"Alejandro Mesa" wrote:
> How to move databases between computers that are running SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
>
> AMB
> "HK" wrote:
> > Question: Which method should I use to do the simultaneous change from SQL
> > 2000 to 2005, and the change from an old PC (server) to a new one?
> >
> > Current: SQL 2000 + Old Hardware
> > New (Future): SQL 2005 + New Hardware
> >
> > What's the best way to get a single live database from the current to the
> > new? I expect to add users manually and point the client applications to
> > the new server. I've done the Upgrade Advisor and resolved a couple minor
> > things. For testing, I've taken a BAK file from the old server (sql 2000)
> > and imported it onto the new server (sql 2005) using RESTORE FILELISTONLY
> > and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading to
> > 2005 in the process, and a few test queries execute properly. So I'm
> > inclined to just do a backup and restore at that magic moment when we do the
> > upgrade. And then I'd add logins and jobs manually. Is there some
> > reason why SP_Detach & Attach is better? At least with a backup/restore
> > method, I can easily switch back to the old database if something doesn't
> > work. Plus, I can test this method many times before the big day, but I
> > can't easily test a detach of this database in advance. Thoughts?
> >
> >
> >|||You wrote that you rebuild all the objects. Which types of objects were
rebuilt besides indexes? Thanks.
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:7736D8DD-EEFC-4470-8EF8-D2F08BE8D96A@.microsoft.com...
> Hi HK
> The reasoning behind this is based on personal experience when we moved a
> SQL 2000 DB to SQL 2005, we ran into some performance issues when
migrating,
> we rebuilt all the objects and ran sp_updatestats to update the table
> statistics again and it resolved our problems. MS have made changes to the
> query optimizer and to ensure that your procs use the best possible
execution
> plan i would suggest it. SQL should be intelligent enough to do it but we
did
> it as a precausionary and it helped our implementation.
> Regards
> charl
>
> --
> http://www.sqlserver.co.za
>
> "Alejandro Mesa" wrote:
> > How to move databases between computers that are running SQL Server
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
> >
> >
> > AMB
> >
> > "HK" wrote:
> >
> > > Question: Which method should I use to do the simultaneous change
from SQL
> > > 2000 to 2005, and the change from an old PC (server) to a new one?
> > >
> > > Current: SQL 2000 + Old Hardware
> > > New (Future): SQL 2005 + New Hardware
> > >
> > > What's the best way to get a single live database from the current to
the
> > > new? I expect to add users manually and point the client
applications to
> > > the new server. I've done the Upgrade Advisor and resolved a couple
minor
> > > things. For testing, I've taken a BAK file from the old server (sql
2000)
> > > and imported it onto the new server (sql 2005) using RESTORE
FILELISTONLY
> > > and then RESTORE DATABASE -- FROM DISK. It imported fine, upgrading
to
> > > 2005 in the process, and a few test queries execute properly. So
I'm
> > > inclined to just do a backup and restore at that magic moment when we
do the
> > > upgrade. And then I'd add logins and jobs manually. Is there some
> > > reason why SP_Detach & Attach is better? At least with a
backup/restore
> > > method, I can easily switch back to the old database if something
doesn't
> > > work. Plus, I can test this method many times before the big day, but
I
> > > can't easily test a detach of this database in advance. Thoughts?
> > >
> > >
> > >

Move Database and Relocate Transaction Logs

Hello,
We have just built a Windows 2000 Advance Server with SQL Server 2000
Enterprise Edition. We would like to move some databases off of an old SQL
2000 Server onto this new server.
Questions:
What is the proper procedure for moving these databases over?
Once the databases are transferred, how can we relocate all of the
transaction logs (master, model, tempdb and databases we have created) to
another drive?
Thanks,
JBaileyCheckout the Copy Database Wizard, you can specify where
the files get copied to. It doesn't copy system
databases, but should grab most relevant material from
them.
>--Original Message--
>Hello,
>We have just built a Windows 2000 Advance Server with SQL
Server 2000
>Enterprise Edition. We would like to move some databases
off of an old SQL
>2000 Server onto this new server.
>Questions:
>What is the proper procedure for moving these databases
over?
>Once the databases are transferred, how can we relocate
all of the
>transaction logs (master, model, tempdb and databases we
have created) to
>another drive?
>Thanks,
>JBailey
>
>.
>|||Best place to start is reading 224071. Also, CDW as recommended by .. is an option.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"JBailey" <abc@.123.com> wrote in message news:ucrE451kDHA.2424@.TK2MSFTNGP10.phx.gbl...
> Hello,
> We have just built a Windows 2000 Advance Server with SQL Server 2000
> Enterprise Edition. We would like to move some databases off of an old SQL
> 2000 Server onto this new server.
> Questions:
> What is the proper procedure for moving these databases over?
> Once the databases are transferred, how can we relocate all of the
> transaction logs (master, model, tempdb and databases we have created) to
> another drive?
> Thanks,
> JBailey
>|||Tibor,
224071 ? I cant seem to find that, have a link?
Thanks,
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:ehGA9E7kDHA.2012@.TK2MSFTNGP12.phx.gbl...
> Best place to start is reading 224071. Also, CDW as recommended by .. is
an option.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "JBailey" <abc@.123.com> wrote in message
news:ucrE451kDHA.2424@.TK2MSFTNGP10.phx.gbl...
> > Hello,
> >
> > We have just built a Windows 2000 Advance Server with SQL Server 2000
> > Enterprise Edition. We would like to move some databases off of an old
SQL
> > 2000 Server onto this new server.
> >
> > Questions:
> >
> > What is the proper procedure for moving these databases over?
> > Once the databases are transferred, how can we relocate all of the
> > transaction logs (master, model, tempdb and databases we have created)
to
> > another drive?
> >
> > Thanks,
> >
> > JBailey
> >
> >
>|||Found 224071.
Questions:
Is it necessary to move the master, model, and tempdb over? Or can I just
move the databases we created? Is there dependencies user created databases
have on the default SQL databases that I should be aware of?
Thanks,
JBailey
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:ehGA9E7kDHA.2012@.TK2MSFTNGP12.phx.gbl...
> Best place to start is reading 224071. Also, CDW as recommended by .. is
an option.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "JBailey" <abc@.123.com> wrote in message
news:ucrE451kDHA.2424@.TK2MSFTNGP10.phx.gbl...
> > Hello,
> >
> > We have just built a Windows 2000 Advance Server with SQL Server 2000
> > Enterprise Edition. We would like to move some databases off of an old
SQL
> > 2000 Server onto this new server.
> >
> > Questions:
> >
> > What is the proper procedure for moving these databases over?
> > Once the databases are transferred, how can we relocate all of the
> > transaction logs (master, model, tempdb and databases we have created)
to
> > another drive?
> >
> > Thanks,
> >
> > JBailey
> >
> >
>|||You can to consider what is stored in the system databases. In master, you have logins, linked
servers, sysmessages, sp_configure. In msdb, you have jobs, operators, alerts.
Only you can say whether above is a too huge job to do manually compared to try to get backup etc
over to the new server.
As for logins (which generally is the main issue), read in books online about sp_change_users_login
and you might want to use the GUI tool at www.dbmaint.com (free utilities).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"JBailey" <abc@.123.com> wrote in message news:OXwiR3%23kDHA.2364@.TK2MSFTNGP11.phx.gbl...
> Found 224071.
> Questions:
> Is it necessary to move the master, model, and tempdb over? Or can I just
> move the databases we created? Is there dependencies user created databases
> have on the default SQL databases that I should be aware of?
> Thanks,
> JBailey
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:ehGA9E7kDHA.2012@.TK2MSFTNGP12.phx.gbl...
> > Best place to start is reading 224071. Also, CDW as recommended by .. is
> an option.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at: http://groups.google.com/groups?oi=djq&as
> ugroup=microsoft.public.sqlserver
> >
> >
> > "JBailey" <abc@.123.com> wrote in message
> news:ucrE451kDHA.2424@.TK2MSFTNGP10.phx.gbl...
> > > Hello,
> > >
> > > We have just built a Windows 2000 Advance Server with SQL Server 2000
> > > Enterprise Edition. We would like to move some databases off of an old
> SQL
> > > 2000 Server onto this new server.
> > >
> > > Questions:
> > >
> > > What is the proper procedure for moving these databases over?
> > > Once the databases are transferred, how can we relocate all of the
> > > transaction logs (master, model, tempdb and databases we have created)
> to
> > > another drive?
> > >
> > > Thanks,
> > >
> > > JBailey
> > >
> > >
> >
> >
>