Restore Database issue - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Restore Database issue

Author

Topic

kcpatel
Starting Member

3 Posts

Posted - 2011-09-22 : 18:18:25

Hi..
I have installed SQL Server 2008 R2 and i got some dbs in SQL 2000 version. How can i restore that Backup file thru Restore option of 2008 server ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-22 : 18:21:04

It's just a normal restore. SQL takes care of the upgrade process. If your software supports 2008, then be sure to change the compatibility level of the databases to 100 (from 80) after the restore.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

kcpatel
Starting Member

3 Posts

Posted - 2011-09-22 : 18:55:36

Thanks for the quick response..
by assuming that its a normal restore, I have tried to restore SQL 2000 DB in 2008 server but its gives me following error.
TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore failed for Server 'SERVER'. (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: File 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\superlynx.mdf' is claimed by 'MasterDB_Data1'(3) and 'MasterDB_Data'(1). The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&LinkId=20476

------------------------------------------------------------

What could be the reason for this error and how can I resolved it..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-22 : 19:23:35

So did you try the WITH MOVE option? You can do it from the restore wizard by going to the options page. You must fix the paths and/or file names.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Kristen
Test

22859 Posts

Posted - 2011-09-23 : 02:30:18

Steps to restoring a file using SQL commands:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54300#214941

Steps to upgrade a database to SQL 2008:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources