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?
> > >
> > >
> > >