Restoring SQL 2000 db in SQL 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Restoring SQL 2000 db in SQL 2005

Author

Topic

Joozh
Posting Yak Master

145 Posts

Posted - 2010-11-19 : 08:10:42

I guess i have tried all possibilities but still have not been able to restore a sql 2000 database backup in SQL 2005. Just a background: The backup was taken about 3 or 4 years back (Though I'm not sure if it makes any difference)

The sql 2000 backup was without any extension and I have tried adding a .BAK and .MDF extension to it but still no luck :(

Here's what I have done
1) Started MSSMS (Microsoft SQL Server management studio)
2) Right clicked on Databases
3) Selected the Restore Database option
4) Entered DB1 in the field for To Database
5) Selected the from Device option
6) Noticed that it only accepts a .BAK or a .TRN extension
7) SO i renamed my database (which was originally without any extension to .BAK)
8) But still no luck and I get the following error message

Restore failed for Server 'ZM-FZ140E'. (Microsoft.SqlServer.Smo)

System.Data.SqlClient.SqlError: Directory lookup for the file "D:\Program Files\Microsoft SQL Server\MSSQL\data\DMART.mdf" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo)

Can someone please please help me. Ofcourse I have the option to uninstall SQL 2005 and install SQL 2000 but i really want to learn SQL 2005 so ant help will be much appreciated.

Thanks & Regards.

-J

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-19 : 08:28:19

A guess:

The original db was present in path:
D:\Program Files\Microsoft SQL Server\MSSQL\data\DMART.mdf

When restoring it is looking for the same path.
You have one item with caption "Option". Click on it.

In right pane you will see "Restore the database as". Enter the new path there.

Also have a look at:
http://www.sqlteam.com/article/backup-and-restore-in-sql-server-full-backups

Kristen
Test

22859 Posts

Posted - 2010-11-19 : 12:09:01

See

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE%20syntax%20/%20example,Restore%20Full%20and%20all%20TLogs%20backups,Restore

for example of Restore Syntax. That will enable you to specify the path where you want it restored.

Then see:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230

which are the steps to upgrade a database to SQL2008 (SQL2005 is pretty much the same, so all the steps should still work under SQl2005)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources