Friday, March 30, 2012

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

No comments:

Post a Comment