Restore a SQL Server 2000 .bak to SQL Server 2005 using SQL Server Management Studio Express

Question

Restore a SQL Server 2000 .bak to SQL Server 2005 using SQL Server Management Studio Express

lleemon13

Ten Centuries

Points: 1063
More actions
May 11, 2009 at 7:05 am

#200373

We are moving from one ISP to Network Solutions. The current hosting was using SQL Server 2000 and was able to get a .bak file of the database. I only have access to SQL Server Management Studio Express and can connect to both (old) SQL Server 2000 and (new) SQL Server 2005 databases using SQL Server Management Studio Express.
Guess my first question is if this is even possible to restore a .bak file and if so, how do I restore it using SQL Server Management Studio Express?
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

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

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

Please do not restore SS2K database on SS2K5 server; you have to upgrade it.

Also remember Logins, Jobs, etc. are not part of your "database" so you have to script them on source environment then re-create them on target environment -plan for it.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

lleemon13 (5/11/2009)
Guess my first question is if this is even possible to restore a .bak file and if so, how do I restore it using SQL Server Management Studio Express?

Sure it's possible.

In management studio, expand out object explorer. Right click on the databases folder and select restore database. find the .bak file, check on the options tab that the files are where they should be and start the restore. SQL will upgrade the DB from 2000 to 2005 as it restores. Note, it's a one-way process. Once the DB's on SQL 2005, you cannot get it back onto SQL 2000 again.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 3

yes you can.

Paul is right..if you take your backup .bak file, you can restore it on any SQL 2005 instance, but behind the scenes it will be upgraded to the 2005 format...it's a one way process, once it's restored, it is a SQL 2005 database.

you cannot take a backup from a 2005 and resore it on a lesser version of SQL 2000, for example.

as an FYI, the compatibility version of the database(70,80,90,100) has nothing to do with how the data is stored/backed up...it only decides what syntax TSQL commands will be permitted...that's why you cna change compatibility level instantly...it just filters syntax commands.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

lleemon13 Ten Centuries Points: 1063 More actions · Answer 4

In Restore Files and Filegroups, I can select the 'To database:' but in the Source for restore I selected 'From device' but can't paste the location into the box. If I click the .. button I can't add any file there either.

Kind of wonder if Express doesn't allow you to do this.

Is there a easy way to create 'INSERT' statements quickly using TSQL?

Lowell SSC Guru Points: 323518 More actions · Answer 5

lleemon13 (5/11/2009)
In Restore Files and Filegroups, I can select the 'To database:' but in the Source for restore I selected 'From device' but can't paste the location into the box. If I click the .. button I can't add any file there either.
Kind of wonder if Express doesn't allow you to do this.
Is there a easy way to create 'INSERT' statements quickly using TSQL?

the source must be a local harddrive that exists on the server you want to restore...you can't browse to any \\UNC paths, or USB/Removable drives either, I think.

you have to use the "..." button inb the from device dialog to browse to the .BAK file...if it doesn't end in .bak or .bck, it might be hidden by the file filter and you can't see it.

maybe the .bak file is on your local drive and not on the server?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

lleemon13 Ten Centuries Points: 1063 More actions · Answer 6

Yes, I guess I was looking on my local machine.

Forget the .bak file, does anyone know a good way to get 'INSERT' statements without scripting it all out? Is there something already in place that will do this quickly?

Lowell SSC Guru Points: 323518 More actions · Answer 7

get the script "sp_generate_inserts" from Narayana Vyas Kondreddi at http://vyaskn.tripod.com

he's got a 200 and 2005 versions, and they generate INSERT INTO [YOURTABLE]....statements for you by simply passing it the tablename.

be careful: if you point that at a table with a million billion rows...well...you'll be waiting a looong time.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!