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 online. Also, get the
sp_help_revlogin procedure from KB and use that to move over your login (this 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 application
> and service access database? 2) anyone can tell detail procedure on how to
> 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 online. Also, get the
> sp_help_revlogin procedure from KB and use that to move over your login (this 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 application
> > and service access database? 2) anyone can tell detail procedure on how to
> > move?
> >
> > Thanks in advance for your time,
> > Martin
>|||KB = Microsoft KnowledgeBase:
http://support.microsoft.com/default.aspx?scid=fh;EN-US;kbhowto&sd=GN&=EN-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...
> 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 online. Also, get the
>> sp_help_revlogin procedure from KB and use that to move over your login (this 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 application
>> > and service access database? 2) anyone can tell detail procedure on how to
>> > move?
>> >
>> > Thanks in advance for your time,
>> > Martin
>>|||Thanks again
"Tibor Karaszi" wrote:
> KB = Microsoft KnowledgeBase:
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;kbhowto&sd=GN&=EN-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...
> > 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 online. Also, get the
> >> sp_help_revlogin procedure from KB and use that to move over your login (this 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 application
> >> > and service access database? 2) anyone can tell detail procedure on how to
> >> > move?
> >> >
> >> > Thanks in advance for your time,
> >> > Martin
> >>
> >>
>|||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/default.aspx?scid=fh;EN-US;kbhowto&sd=GN&=EN-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...
> > 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 online. Also, get the
> >> sp_help_revlogin procedure from KB and use that to move over your login (this 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 application
> >> > and service access database? 2) anyone can tell detail procedure on how to
> >> > move?
> >> >
> >> > Thanks in advance for your time,
> >> > Martin
> >>
> >>
>|||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/attach) 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 transferred. 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 using) 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...
> 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/default.aspx?scid=fh;EN-US;kbhowto&sd=GN&=EN-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...
>> > 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 online. Also, get the
>> >> sp_help_revlogin procedure from KB and use that to move over your login (this 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 application
>> >> > and service access database? 2) anyone can tell detail procedure on how to
>> >> > move?
>> >> >
>> >> > Thanks in advance for your time,
>> >> > Martin
>> >>
>> >>
>>|||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...
> 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/default.aspx?scid=fh;EN-US;kbhowto&sd=GN&=EN-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...
>> > 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
>> >> online. Also, get the
>> >> sp_help_revlogin procedure from KB and use that to move over your
>> >> login (this 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
>> >> > application
>> >> > and service access database? 2) anyone can tell detail procedure on
>> >> > how to
>> >> > move?
>> >> >
>> >> > Thanks in advance for your time,
>> >> > Martin
>> >>
>> >>
>>|||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/attach) 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 transferred. 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 using) 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...
> > 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/default.aspx?scid=fh;EN-US;kbhowto&sd=GN&=EN-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...
> >> > 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 online. Also, get the
> >> >> sp_help_revlogin procedure from KB and use that to move over your login (this 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 application
> >> >> > and service access database? 2) anyone can tell detail procedure on how to
> >> >> > move?
> >> >> >
> >> >> > Thanks in advance for your time,
> >> >> > Martin
> >> >>
> >> >>
> >>
> >>
>|||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...
> 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/attach) 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
>> transferred. 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
>> using) 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...
>> > 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/default.aspx?scid=fh;EN-US;kbhowto&sd=GN&=EN-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...
>> >> > 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 online. Also, get the
>> >> >> sp_help_revlogin procedure from KB and use that to move over your
>> >> >> login (this 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
>> >> >> > application
>> >> >> > and service access database? 2) anyone can tell detail procedure
>> >> >> > on how to
>> >> >> > move?
>> >> >> >
>> >> >> > Thanks in advance for your time,
>> >> >> > Martin
>> >> >>
>> >> >>
>> >>
>> >>
>>