How to Move SQL Server 2000 DB.BAK File to SQL Sever 2005

Good Day:

I have a SQL Server 2000 database full backup file called C:\Old Data\PwtDB\TestPWT_db_201108260200.BAK

I would like to move my SQL Server 2000 database file to my new SQL Server 2005 database.

Any Suggestions?

Thank you,
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
Just restore that database using the T-SQL command RESTORE.
Shaun KlineLead Software EngineerCommented:
If it is a full backup, you can restore the database as you would on SS2K.
Move the backup file to a location your 2005 server can access. In SQL Server Management Studio (SSMS), right click on the Databases node in the Server Explorer pane and select Restore Database in the Tasks sub-menu. Provide a new database name and in the bottom section, select File and browse to the backup file. If you need to restore the data and log files to a specific drive/folder, select options in the left portion of the window and modify the file paths.

Once the database is restored, if you want to upgrade the database to 2005, right click the database in Server Explorer, and choose properties. Select options and then modify the Compatibility level to v9.0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
>>Once the database is restored, if you want to upgrade the database to 2005<<
To be strictly correct, the minute tou restore a 2000 database to 2005 you have already upgraded the database to the SQL Server 2005 format as evidenced when you RESTORE using T-SQL.  The only thing you achieve by changing the Compatibility Level to 90 is to enable functions that were not supported in SQL Server 2000.
DeniseGoodheartAuthor Commented:

Thanks for the suggestions.  I could not use the wizard because it was reading the physical file directory and giving me errors, so I had to use the restore and move command.

I successfully restored/moved my 2000 DB to 2005 by doing the following:
--Step 1
--Get the Logical and Physical Names from .BAK file (Complete Backup)
restore filelistonly
from disk = 'C:\Old Data\PwtDB\TestPWT_db_201108260200.BAK'

--Step 2
restore database TestPWT
from disk = 'C:\Old Data\PwtDB\TestPWT_db_201108260200.BAK'
---LogicalName=PWTDB_Data and Physical Name=TestPWT.MDF
with move 'PWTDB_Data' to 'C:\Old Data\PwtDB\TestPWT.MDF',
---Logical Name=PWTDB_log and Physical Name=TestPWT_log.ldf
move 'PWTDB_log' to 'C:\Old Data\PwtDB\TestPWT_log.ldf'
Anthony PerkinsCommented:
>>I could not use the wizard because it was reading the physical file directory and giving me errors<<
Even the wizard supports changing the file names.  I believe it is under Options.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.