Post reply

Transfer/copy 2000 database to 2005 with 2005 Express

  • April 11, 2008 at 8:10 am

    #185316

    How do you transfer/copy a 2000 database into 2005 using 2005 Express? The Help system describes an approach where you right click on the database, choose Tasks and then choose Copy. Copy is not an option under Tasks on my system. Is it possible that Express 2005 does not contain this feature? If not, how can a 2000 database be converted to 2005? If so, how can it be done?

    Thanks,

    Al

  • April 11, 2008 at 12:26 pm

    #801670

    Hi

    I would ensure the following:

    1) Do you have Enterprise Manager or SQL Query Analyser for SQL 2000?

    2) Do you have the Advanced Tools for SQL 2005 Express (if now download)

    3) Do you have access to make backups from SQL 2000 or have the .BAK file?

    Then create a backup from SQL 2000 using EM or via script of your desired Database

    Then inside SQL Server Management Studio Express Edition run a new query to restore the database

    If you want to change the compatibility mode to 9.0 or SQL 2005 - do this afterwards in SSMSE

    If you are not sure about SQL scripts try using the Restore DB from SSMSE (if allowed - I think it does)

    Let us know

    Thanks

    Kevin

  • April 11, 2008 at 12:54 pm

    #801685

    Thanks, Kevin.

    I ended up detaching the 2000 database, making a copy and then attaching it via SS Management Studio Express. Then via the database->properties->options the Compatibility Level was changed to SQL Server 2005 (90).

    I'm not clear on the Compatibility Level. I expected some processing after that choice as I expected some underlying structural elements, ... would need to be changed. However, nothing appeared to happen. So I now have a copy of the database open in SSMSE along with databases created directly with SSMSE; however, I don't know if the copied database is a true 2005 database or a 2000 database with some compatibility setting. How can I tell? If I'm strictly in a compatibility mode, how can I make it true 2005?

    Thanks,

    Al

  • April 11, 2008 at 1:02 pm

    #801688

    Hi

    As far as I am aware - by changing the Compatibility Level in Properties --> Options --> Compatibility level - to say SQL 2005 (90)

    This initiates the "conversion" of the database - you can also script the change that takes place between the versions:

    USE [master]

    GO

    EXEC dbo.sp_dbcmptlevel @dbname=N'DatabaseName', @new_cmptlevel=90

    GO

    as far as I know this does make certain structure changes due to new data types and other reasons.

    Hope this helps

    Thanks

    Kevin

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply