.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SQL 2008 Fails to restore a database

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :


I have a Windows 2008 R2 Server running SQL2008 (not R2).

I havew been trying to restore a database using the Restore Database Tool in Enterprise Manager. That is Right click the database, select Tasks ->Restore ->Database.

On the General page I have selected my source as a Database Backup and selected the file location. I have selected the only backup set in the file.

On the General Tab I have selected the 'Overwrite the existing Database (with Replace)'

When I click the OK button to action my selections I receive the following Error message:

Restore failed for Server 'WIN2008SQL'.  (Microsoft.SqlServer.SmoExtended)
System.Data.SqlClient.SqlError: File 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MAESTRODB.ndf' is claimed by 'MAESTRO_INDEX_DATFILE'(4) and 'MAESTRO_DATFILE'(3). The WITH MOVE clause can be used to relocate one or more files. (Microsoft.SqlServer.Smo)

I have not been able t ofind a solution to this...


View Complete Post

More Related Resource Links

Restore 2005 database on 2008 R2 Trial

I've downloaded SQL Server 2008 R2 Trial and installed it on Windows Server 2008 trial running under Virtual PC on XP. I've made backups of databases on my SQL Server 2005 and tried to restore them using a) the wizard - results in a messagebox saying "Specified cast is not valid" and no backup sets are available in the Restore Database window. b) using T-SQL - results in Msg 3183, Level 16, State 2, Line 1 RESTORE detected an error on page (0:0) in database "dp" as read from the backup set. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. Using the wizard and RESTORE on 2005 for the same database backup works fine. c) Detach on 2005 and Attach on 2008 results in: SQL Server detected a logical consistency-based I/O error: incorrect pageid... Detach and Attach worked fine on 2005 and DBCC CHECKDB revealed no errors. Thanks for any info...

how to restore msdb database on sql 2008 from sql 2005 backup?

Currently, I am unable to do it. It gives me the error than I cannot be restored because it was created by a different version of the server. What is the work-around? I hate scripting all my maintenance plans and alerts, there are tonns of themJulieShop

Error when running an SQL script to restore a database on SQL Server 2008 R2 Express

Hi, At work, I use SQL Server 2008 Express running on Windows XP Professional Service Pack 3. At home, I also use SQL Server 2008 Express running on Windows XP Professional Service Pack 3. Whenever I do work on the database at work, I usually restore the database at home (or vice versa) I have been using the following SQL scripts to back up the database and to restore the same database (at work or at home). BackupRealEstate.sql USE Master BACKUP DATABASE [RealEstate] TO DISK = N'F:\My Documents\My Database\RealEstate.bak' WITH NOFORMAT, INIT, NAME = N'RealEstate-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO RestoreRealEstate.sql USE Master RESTORE DATABASE [RealEstate] FROM DISK = N'F:\My Documents\My Database\RealEstate.bak' WITH  FILE = 1, NOUNLOAD, REPLACE, STATS = 10 GO Everything worked fine until I replaced my 8yo home computer with a newer and faster one. My new home computer is running Windows 7 Home Premium and I use the latest SQL Server 2008 R2 Express. When I tried to run the above restore SQL script, I got the following error message: Msg 3101, Level 16, State 1, Line 2 Exclusive access could not be obtained because the database is in use. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally. There was no other application/process using this database. Can someone please help me? Regards,

SQL Server 2008 Windows Authentication Mode fails for Database Engine, error 18456

I installed SQL Server 2008 Developer Edition (10.0.1600.22.080709-1414) on a Windows Vista 32-bit development machine with Windows Authentication Mode only (no SQL Server Authentication).   Since this is a development machine, I saw no need for Mixed Mode when I did the install.  The SQL Server Management Studio allows me to login to Analysis Services, Integration Services, Reporting Services, BUT NOT the Database Engine!Windows Authentication for the Database Engine gives the following error: EventID: 18456Login failed for user MYDOMAIN\MYLOGIN'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]The 'fix' I have found online is to login as SA in SQL Server Authentication mode & add MYDOMAIN\MYLOGIN as a administrator for the Database Engine.  Unfortunately, I can't since I didn't install SQL Server Authentication mode (only Windows Authentication Mode).  It appears my only recourse is to uninstall, then reinstall in Mixed mode, then to login as SA in SQL Server Authentication mode & add MYDOMAIN\MYLOGIN as a administrator for the Database Engine. Before I do so, does anyone know of a better approach?Scott D Duncan

SQL Server 2008 Database Restore(Database Mirroring)

HI,I tried to impliment Database mirrorin in SQL Server 2008.I have 3 Sqlserver 2008 Eneterprise Edition in 3 diiferent computers with same domain.Pricnicipal server =TCP://SPHDev.sphinet.com.sg:5022.Mirror=TCP://SPHPrimary.sphinet.com.sg:5022 and witness =TCP://wsc211.sphinet.com.sg:5022.But when i click start mirroring button i got an error TITLE: Database Properties------------------------------ An error occurred while starting mirroring. ------------------------------ADDITIONAL INFORMATION: Alter failed for Database 'SPH_MSCRM'.  (Microsoft.SqlServer.Smo) ------------------------------ An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The server network address "TCP://SPHPRIMARY.sphinet.com.sg:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418).This is the error message i got from Error Log"2009-06-03 17:01:08.89 spid27s     Database mirroring connection error 2 'DNS lookup failed with error: '11001(No such host is known.)'.' for 'TCP://SPHPRIMARY.sphinet.com.sg:5022"I restored Principal server DB  by using RESTORE WITH NORECOVERY and now this  database in mirror server is now look like as DBName(Restoring....).How i can identify

SQL Server 2008 R2 rebuild master database fails with error -2068643838

I want to change the server collation setting for SQL 2008 R2.  I'm using the command: Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MyServerName /SQLSYSADMINACCOUNTS="MyDomain\Special Account Group" "NT AUTHORITY\SYSTEM" "NT SERVICE\MSSQLSERVER" "NT SERVICE\SQLSERVICEAGENT" sa /SQLCOLLATION=Latin1_General_CI_AI The setup program starts, runs a while, then exits with a return code: Error result: -2068643838 Result facility code: 1203 Result error code: 2 The final lines of the Detail.txt file in the ...\Setup Bookstrap\Log\<specific timestamp>\ directory are: 2010-07-12 14:54:58 Slp: Running Action: ProduceStatusLogsBeforeFinishPage 2010-07-12 14:54:59 Slp: Completed Action: ProduceStatusLogsBeforeFinishPage, returned True 2010-07-12 14:55:00 Slp: Received request to add the following file to Watson reporting: C:\Users\<MyLoginName>\AppData\Local\Temp\2\tmpDAAF.tmp 2010-07-12 14:55:00 Slp: The following is an exception stack listing the exceptions in outermost to innermost order 2010-07-12 14:55:00 Slp: Inner exceptions are being indented 2010-07-12 14:55:00 Slp: 2010-07-12 14:55:00 Slp: Exception type: Microsoft.SqlServer.Setup.Chainer.Workflow.NoopWorkflowException 2010-07-12 14:55:00 Slp:     Message: 2010-07-12 14:55:00 Slp:         The state of your

restore SQL server 2005 database on new SQL server 2008 not working

please any one advice me ,i have Great Plain database on SQL server 2005 when i tried to install new SQL server 2008 and restore my Database the GreatPlain not working well as well the Data Base

AS Database Restore fails: OLAPinfo element .... (namespace) cannot appear under BackupLog. (Micros


We encounter this problem when restoring a backup from the SAME sql server version (2008R2):

TITLE: Microsoft SQL Server Management Studio

The OlapInfo element at line 1, column 445 (namespace ) cannot appear under BackupLog.




SQL 2008 SP2 succefull but instance start fails - Script level upgrade for database 'master' faile


after installing SP2 instance starts and stops with error:

Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 5831, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

Already tried without success:
- enabled SA account (was disabled) http://support.microsoft.com/kb/960781/en-us
- uninstalled SP2 -> other error, but same problems with running the script



restore 2005 database into 2008 error 3154


I am trying to restore a 2005 database to a 2008 database.  This is the code that was generated by Management studio:




restore 2005 database into 2008 error 3154


I am trying to restore a 2005 database to a 2008 database.  This is the code that was generated by Management studio:




2008 R2- Restoring MASTER database from v10.00.1600 to v10.50.1600 fails with error..


Hey Guys,

I backed up the master database on a machine with SQL Server 2008 version 10.00.1600. Then I turned on SQL Server in single-user mode on the new machine with SQL Server 2008 R2 version 10.50.1600 with the following command (using sqlcmd)


Running this command gets me the following error.....

Msg 3168, Level 16, State 1, Server U15417546, Line 1
The backup of the system database on the device D:\master.bak cannot be restored because it was created by a different version of the server (10.00.1600) than this server (10.50.1600). Msg 3013, Level 16, State 1, Server U15417546, Line 1
RESTORE DATABASE is terminating abnormally.

Since i am restoring from a lower version, to a higher version, i assume the restore should be able to complete? I have not been able to find much help regarding this, so any information would be much appreciated.


Backup and Restore SQL Express 2008 database


Hi All,

How can backup and restore SQL Express 2008 database using c#.net

Thank you


BACKUP WITH CHECKSUM against an encrypted database - restore fails


I have five databases sitting on a new SQL 2008 R2 server.  The databases originated on SQL Server 7, went through both a SQL 2000 and a SQL 2005 upgrade, and last month were migrated to this new SQL 2008 server.  All five databases are in SQL 2005 compatibility mode.  All five databases are using SQL 2008's full database encryption.

Over the weekend, I changed the page verification of these databases from "torn page" to "checksum".  Nightly full backups were completed on Sunday night, and successfully restored to our reporting and hot-standby servers.  Yesterday, I added the WITH CHECKSUM keyword to the backup jobs.  Nightly full backups ran last night, completed successfully.  The restores of those same backups to reporting and standby FAILED, with several assertion failure messages.

Today, I created a brand new database, in SQL 2008 compatibility mode and "checksum" page verification.  BACKUP WITH CHECKSUM produces a restorable backup.  I then enabled encryption on that database, and did the same BACKUP WITH CHECKSUM operation.  The resulting backup cannot be restored, NOT EVEN on the same server.  The restore appears to complete, giving the normal output stating how many pages it processed, but the "recovery" portion of the restore throws an exception.

sql server 2008 adventureworks database


dear Friends

For my MCTS 70 -433 examination, I recently installed sql server 2008 and installed the sample databases too. but my issue is, in that book they have mentioned a database called "AdventureWorks2008" and the nice thing is I don't have a such a database with me in my sql server 2008. 

And the query is 

-- MERGE Statement
USE AdventureWorks2008
MERGE INTO	Sales.SalesOrderDetailHistory AS SODH
USING Sales.SalesOrderDetail AS SOD
ON SODH.SalesOrderID = SOD.SalesOrderID
AND SODH.SalesOrderDetailID = SOD.SalesOrderDetailID
INSERT (LineTotal, SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty
, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount
, rowguid, ModifiedDate, Cancelled)
VALUES(LineTotal, SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty
, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount
, rowguid, ModifiedDate, DEFAULT)
UPDATE SET SOHD.Cancelled = 'True'
OUTPUT $action , inserted.* ;	

Please help me to overcome this issue and let me know where can I download that database too.

Thanking you

happy Coding

SQL2008 - Cannot Restore a new database from a full backup of a different one, database in use

I am trying to restore a brand new database from a copy of another one and am encountering an error message stating: System.Data.SqlClient.SqlError: The file <Backup source database file location> cannot be overwritten.  It is being used by database <Backup Source Database>. (Microsoft.SqlServer.Smo) I am running the Restore procedure with the REPLACE option and am wondering why it is stating that the source database is the database in use when I am trying to restore and overwrite a completely different database. This issue happens when running the replace both in C# using SMO and when manually restoring with SqlServer Management Studio Interesting note is, the original source database is created / deployed using a database project within VisualStudios 2010 and then deployed to SqlServer through VisualStudios. Any thoughts on why this is happening? Thanks in advance for any help!

SQL Server 2008 installation fails There was an error generating the XML document. Error code 0x84B

My system config: window XP - SP3 4 GB RAM, I have VS2008 with SP1, VS2010 Ultimate installed First when I tried to install SQL 2008 R2 Dev edition I got an error saying vs2008 SP1 was not installed, infact I had the SP1 installed in my m/c.  I did reinstalled the SP1 and tried to install SQL again. while in the final installation process I got the error  "Setup has encountered the following error: There was an error generating the XML document. Error code 0x84B10001." I uninstall the SQL server again to remove the files which were created from Unsuccessful installation I removed the register entry as suggested by this forum. reinstalled but no chance, I have tried all the option which I can get know in web. Please help me. here is the log files details  Exception type: Microsoft.SqlServer.Chainer.Infrastructure.ChainerInfrastructureException 2010-08-26 14:54:53 Slp:     Message: 2010-08-26 14:54:53 Slp:         There was an error generating the XML document. 2010-08-26 14:54:53 Slp:     Stack: 2010-08-26 14:54:53 Slp:         at Microsoft.SqlServer.Chainer.Infrastructure.DataStoreService.SerializeObject(String rootPath, Object objectToSerialize, Boolean saveToCache) 2010-08-26 14:54:53 Slp:   &nb
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend