Error Restoring Database 1

[ousoonerjoe]

Using 2008 R2 (also happened with 2008).

When restoring a copy of the production database to a point in time restore, the process terminates with the following error:

System.Data.SqlClient.SqlError: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. (Microsoft.SqlServer.Smo)

After cancelling the error, the database is in an Off-Line, Single-User state. A simple right click to "Bring On Line" and then setting the database to Multi-User Mode in the properties brings the database into a full and ready state. After numerous data checking and verifying, the database was in fact restored in its entirety. It is just not able to be restored to an active state. This has been happening ever since it was upgraded from 2005.

Has anyone else encountered this issue?

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[TheBugSlayer]

This sounds like the type of error you get when the TRY CATCH block is not constructed properly? Are you using any anywhere in your code? If yes, post a snippet...

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[ousoonerjoe]

I'm using the "Restore Database..." option under the Right-Click menu. There is NO code.

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[TheBugSlayer]

I understand, but I am talking about the code in the transactions that your RESTORE is restoring...In any case, can you try a RESTORE VERIFYONLY first and see if you get the error? Just for elimination.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[ousoonerjoe]

Ah... sorry... misunderstood what you were referring to there.

I ran the tests as you requested: All returned No Errors.

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[TheBugSlayer]

Weird. You may wanna look at the transactions in your DB just before you run the restore operation. For this you can use the transaction DMVs. Start with sys.dm_tran_session_transactions and sys.dm_tran_database_transactions. If you' are the only user at the time of the check, then repeated checks should tell you which transactions are not changind their state. Then you can take it from there. Read up on them if you' re not familiar with them.

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[ousoonerjoe]

I'll give that a shot and see what I can find.

Thanks for the suggestion.

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------