move sql 2000 db to sql 2005

Hi, All,
I want to move sql 2000 db to sql 2005, including data and user. The sql
2005 is on the remote server rather than local one. I knew using
import/export to move data, but don't know on how to move user info. Also
using copy database can copy 2000 to 2005, but before copy, need no
application and service to access 2000 db, so 1) how to make no application
and service access database? 2) anyone can tell detail procedure on how to
move?
Thanks in advance for your time,
MartinI suggest you use BACKUP and RESTORE to move the database. It is fully onlin
e. Also, get the
sp_help_revlogin procedure from KB and use that to move over your login (thi
s way the users' SID
will match the login's SID).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"martin1" <martin1@.discussions.microsoft.com> wrote in message
news:E297ABCE-4F35-46CC-8BAC-98F803BA17B9@.microsoft.com...
> Hi, All,
> I want to move sql 2000 db to sql 2005, including data and user. The sql
> 2005 is on the remote server rather than local one. I knew using
> import/export to move data, but don't know on how to move user info. Also
> using copy database can copy 2000 to 2005, but before copy, need no
> application and service to access 2000 db, so 1) how to make no applicati
on
> and service access database? 2) anyone can tell detail procedure on how t
o
> move?
> Thanks in advance for your time,
> Martin|||Thank you.
as for sp_help_revlogin procedure from KB, what is KB?
"Tibor Karaszi" wrote:

> I suggest you use BACKUP and RESTORE to move the database. It is fully onl
ine. Also, get the
> sp_help_revlogin procedure from KB and use that to move over your login (t
his way the users' SID
> will match the login's SID).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "martin1" <martin1@.discussions.microsoft.com> wrote in message
> news:E297ABCE-4F35-46CC-8BAC-98F803BA17B9@.microsoft.com...
>|||KB = Microsoft KnowledgeBase:
http://support.microsoft.com/defaul...ver/default.asp
http://www.solidqualitylearning.com/
"martin1" <martin1@.discussions.microsoft.com> wrote in message
news:4CDECE99-FCEC-45AA-9090-B5CA8385C70C@.microsoft.com...[vbcol=seagreen]
> Thank you.
> as for sp_help_revlogin procedure from KB, what is KB?
> "Tibor Karaszi" wrote:
>|||Thanks again
"Tibor Karaszi" wrote:

> KB = Microsoft KnowledgeBase:
> http://support.microsoft.com/defaul...US&FR=
0
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "martin1" <martin1@.discussions.microsoft.com> wrote in message
> news:4CDECE99-FCEC-45AA-9090-B5CA8385C70C@.microsoft.com...
>|||Hi, All,
Import/Export, Copy Database Wizard, and Backup/Restore can copy sql 2000
data to sql 2005, can anyone know which is better way to upgrade sql 2000 to
2005?
Thanks,
Martin
"Tibor Karaszi" wrote:

> KB = Microsoft KnowledgeBase:
> http://support.microsoft.com/defaul...US&FR=
0
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "martin1" <martin1@.discussions.microsoft.com> wrote in message
> news:4CDECE99-FCEC-45AA-9090-B5CA8385C70C@.microsoft.com...
>|||I believe that there has been some bugs in Copy Database Wizard. I've only r
ead about them, didn't
memorize them, but it was enough for me to avoid it. I'm sure MS has been wo
rking on for sp2,
though.
I generally prefer working on the binary level (backup/restore or detach/att
ach) instead of
Export/Import. When you use the later, the database is scripted (a bunch of
CREATE statements), the
script files are executed against the destination, and then the data is tran
sferred. If you aren't
prepared to handle any possible errors in the script handling (if it isn't "
your" data model"), then
I recommend against Export/Import.
That leaves us with detach/attach (which is what Copy Database Wizard is usi
ng) and BACKUP/RESTORE.
I prefer Backup/Restore as that don't affect the source database, and the am
ount of data to transfer
is less (empty space isn't included in backup), I only get one file etc.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"martin1" <martin1@.discussions.microsoft.com> wrote in message
news:F6C87F0D-2BBD-40F8-90C4-9F9A40CFE0D4@.microsoft.com...[vbcol=seagreen]
> Hi, All,
> Import/Export, Copy Database Wizard, and Backup/Restore can copy sql 2000
> data to sql 2005, can anyone know which is better way to upgrade sql 2000
to
> 2005?
> Thanks,
> Martin
> "Tibor Karaszi" wrote:
>|||Backup / restore is best, as it copies everything and gives you the ability
to save the intermediate state. The Copy Database Wizard is the easiest to
use, it also copies everything. Import/Export discards a lot of imformation
and is therefore not an option for copying whole databases.
If you are replacing SQL2000 with SQL2005, you can also just detach and
attach the files.
"martin1" <martin1@.discussions.microsoft.com> wrote in message
news:F6C87F0D-2BBD-40F8-90C4-9F9A40CFE0D4@.microsoft.com...[vbcol=seagreen]
> Hi, All,
> Import/Export, Copy Database Wizard, and Backup/Restore can copy sql 2000
> data to sql 2005, can anyone know which is better way to upgrade sql 2000
> to
> 2005?
> Thanks,
> Martin
> "Tibor Karaszi" wrote:
>|||Thank you all for this help!
Martin
"Tibor Karaszi" wrote:

> I believe that there has been some bugs in Copy Database Wizard. I've only
read about them, didn't
> memorize them, but it was enough for me to avoid it. I'm sure MS has been
working on for sp2,
> though.
> I generally prefer working on the binary level (backup/restore or detach/a
ttach) instead of
> Export/Import. When you use the later, the database is scripted (a bunch o
f CREATE statements), the
> script files are executed against the destination, and then the data is tr
ansferred. If you aren't
> prepared to handle any possible errors in the script handling (if it isn't
"your" data model"), then
> I recommend against Export/Import.
> That leaves us with detach/attach (which is what Copy Database Wizard is u
sing) and BACKUP/RESTORE.
> I prefer Backup/Restore as that don't affect the source database, and the
amount of data to transfer
> is less (empty space isn't included in backup), I only get one file etc.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "martin1" <martin1@.discussions.microsoft.com> wrote in message
> news:F6C87F0D-2BBD-40F8-90C4-9F9A40CFE0D4@.microsoft.com...
>|||The best thing would be detach and create for attach. I did the same for a
1.5TB database having 4 datafiles on multiple filegroups. It all went well
on SQL2005.
Note: You might have to run dbcc checkdb, dbcc updateusage after the
mgiration.
thks,
Manikanth.S
MCDBA.
"martin1" <martin1@.discussions.microsoft.com> wrote in message
news:37F7AE5F-69C0-4C67-9CF7-23320A3911F1@.microsoft.com...[vbcol=seagreen]
> Thank you all for this help!
> Martin
> "Tibor Karaszi" wrote:
>