November 13, 2009 at 5:10 am
One of our SharePoint admins renamed a test server and the rename put the content database into permanent (Restoring) state. It's a test server and we have no backup. We don't have the disk space to restore a backup from production.
I've detached the database but now it won't reattach. Error is below. It mentions something about rebuilding the log. How do I reattach the MDF data file and rebuild a new log file?
TITLE: Microsoft SQL Server Management Studio
------------------------------
Attach database failed for Server 'SNCH2XXX20V'. (Microsoft.SqlServer.Smo)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An error occurred while processing the log for database 'WSS_Content_portal'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
Could not open new database 'WSS_Content_portal'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 9004)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=9004&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
November 13, 2009 at 5:16 am
Why no backup?
See this post for a way to hack the database back into the server and get things working. http://sqlinthewild.co.za/index.php/2009/06/09/deleting-the-transaction-log/. Don't delete the transaction log, just use the hack with both files and see if it works. If the db is in a state other than online afterwards, post and we'll work through it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2009 at 5:53 am
nice article - yours? thanks for the help...
No backup becasue it's a test server.
i tried the part at the end (create a new db, stop sql, delete files, rename old db mdf to new mdf) in order to trick sql into attaching thefile but i don't get a recover pending state. i see the db in management studio but cannot open it or see properties. when i try properties:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Database 'WSS_Content_portal_2' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4053&EvtSrc=MSSQLServer&EvtID=945&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
And if I try to set emergency mode:
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "E:\MSSQL\DATA\WSS_Content_portal_2.mdf". Operating system error 5: "5(Access is denied.)".
File activation failure. The physical file name "D:\MSSQL\LOGS\WSS_Content_portal_2.ldf" may be incorrect.
November 13, 2009 at 6:58 am
OLDCHAPPY (11/13/2009)
nice article - yours?
Yup. My blog
No backup becasue it's a test server.
Doesn't mean it shouldn't be backed up. Personal opinion: If you can't recreate a DB completely and easily either from another or from source data, it should be backed up.
i tried the part at the end (create a new db, stop sql, delete files, rename old db mdf to new mdf) in order to trick sql into attaching thefile but i don't get a recover pending state. i see the db in management studio but cannot open it or see properties.
Ok, that's progress and that's good. The recovery pending is a symptom of a deleted log. Since yours wasn't deleted, you won't get that.
Unable to open the physical file "E:\MSSQL\DATA\WSS_Content_portal_2.mdf". Operating system error 5: "5(Access is denied.)".
Permissions. Make sure that the SQL service account has full permissions on the directory (E:\MSSQL\DATA) and the file (WSS_Content_portal_2.mdf)
It's not uncommon for permissions to be reset upon detach. Stop the SQL service, fix the permissions, restart SQL, post again as to where you are. Don't try to set Emergency mode, shouldn't be needed here. One step at a time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2009 at 7:13 am
you are truly worthy of your avatar...
IT WORKS!!!!
thanks for the assist!
November 13, 2009 at 9:48 am
Glad to hear it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply