In certain situations you do not want to be a DBA
Last week I was asked by a colleague to take care of a database. No one had bothered to really work/administrate with this database since 2008 but everyone is using this DB….
what I saw there was already a quite a desaster. The database was no longer visible to the SQL Server, just as there was no backup: (
So I tried to query the database again to attach to the SQL server, the message which is then followed lies not make me happy..
File activation failure. The physical file name "xyz logDeletion.ldf" may be incorrect. The log can not be rebuilt because the database was not cleanly shut down. Msg 1813, Level 16, State 2, Line 1 Could not open new database 'xyz'. CREATE DATABASE is aborted.
What has happened? The database was not available, a corrupt log file … The weekend may be more beautiful?
After many many hours reading test and I can only recommend always to Satisfy the backup exists.
Everyone who gets into a situation like this, I recommend the following steps.
Backup of the MDF / NDF and eventually present LOG files
A new database created with exactly the same name as the corrupt database(and file names eg. mdf,ndf,ldf)
Shut down the SQL server.
All files from the corrupt database in the directory to copy to the new DB is. Not the log file overwrite.
SQL Server starts, the DB is now in status ‘SUSPECTET’
ALTER DATABASE SET SINGLE_USER Your Database
ALTER DATABASE Your Database SET EMERGENCY
DBCC CHECKDB (Your Database, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE SET MULTI_USER Your Database
Test all this steps first with a dummy Database. Paul Randal has a great articel, read this first try this , then try again! understand what you are doing
so far i hope you will never come in this situations….