1

I've created a local SQL Server instance (using SQL Server 2017). I'm trying to restore a backup of a database created on a SQL Server 2012 server, for testing purposes.

The problem is that it gives me an error when I try to restore it:

TITLE: Microsoft SQL Server Management Studio

Restore of database 'XXXX' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)

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

System.Data.SqlClient.SqlError: 'MyTable' does not contain an identity column. (Microsoft.SqlServer.SmoExtended)

Does anybody have any idea of what can be done to restore it? Does this may have something to do with server configurations? Thanks a lot.

@Max Vernon, I checked the log following your suggestion and I was surpried do see that, although message said "Restore failed", the log says something a bit different: Error: 3165, Severity: 16, State: 1. Database 'XXXX' was restored, however an error was encountered while replication was being restored/removed. The database has been left offline. See the topic MSSQL_ENG003165 in SQL Server Books Online.

So, I refreshed the Databases node just to find out that the DB was really there, offline. I've just brought it online, changed from "single user" to "multi user" and it's working fine :) About the actual error (3165..) I don't now why it happened, but te DB is just for tests and it's working, so I'll not worry about it for now. Thanks a lot for your help. All these "stack exchanges" communities are amazing.

3

Do the restore using T-SQL with the RESTORE DATABASE ... statement - that should provide a more meaningful error.

In brief, restore database works like this:

RESTORE DATABASE [database_name]
FROM DISK = N'\Path\to\the\backup\file.bak'
WITH RECOVERY
   , MOVE N'file name' TO N'new\path\for\this\file.mdf'
.
.
.

I wrote a blog post with code that automates creating the RESTORE DATABASE statement. You simply point the code at an existing database backup file, and it will generate a valid restore command which you can then use to restore the database.

Using T-SQL to restore the database will provide a much more fine-grained set of error messages than using the SSMS GUI.

Also, I'd check the SQL Server Error Log to see if there are any pertinent details. I also wrote a blog post with a quick script that can be used to show details from the Error Log.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.