Database Technologies‎ > ‎MSSQL‎ > ‎

Miscellaneous

 1. Log File Missing/Deleted

In some cases it is found that System administrator delete the log file
when the service is stopped, in that case how to restore the database

EXEC sp_attach_single_file_db@dbname='<db_name>'
@physname='c:\path\to\the\mdf\file .mdf'

above will create a new log file.


2. Recovering database from .mdf file.

If you have only the mdf file.

1) create the database with same name as the original database.
2) After stopping the sql server, overwrite the mdf file onto new one.
3) Delete the .ldf file.
4) Start the sql server, it will create .ldf file on automatically. 
 
3. Different Structure at Production and DR.
 
There is situation where the Drive structure at the Production and the
Standby servers are different.

You have added a ndf file on the production server on F: drive and
F: drive doesn't exist on the DR server then what you do ?

Here you could restore the log file i.e  .trn file before which you have added new ndf
file.

restore log <database_name>
with move <new logical name> to '<Physical Path>'
with standby='<path to tuf file>'

 
3. Moving Master, MSDB Datafiles to different Drive.
 
Change the startup parameters to include
 
-c -m -T36008
 
Trace flag 3608 prevents SQL Server from recovering any database except the master database
 
Restart SQL Server.
Make sure SQL Server Agent is stopped.
 
sp_detach_db 'msdb'
go
 
Move datafile and log file to new location
 
Remove -c -m -T36008 from startup parameters
 
Restart the SQL Server.
 
Re-atach the msdb as follows
 
sp_attach_db 'msdb','<new Drive>:\Data\msdbdata.mdf','<new Drive>:\Data\msdblog.ldf'
go
 
 
 

4. Suspect Database.


    USE Testdb
    GO

    Msg 945, Level 14, State 2, Line 1
    Database 'Testdb' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Now let's check the database status:

    SELECT DATABASEPROPERTYEX ('Testdb', 'STATUS') AS 'Status';
    GO

    Status
    --------
    SUSPECT


    ALTER DATABASE Testdb SET EMERGENCY;                == This lets us access to the database, but
    GO
    ALTER DATABASE Testdb SET SINGLE_USER;
    GO
    DBCC CHECKDB (Testdb, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
    GO
Comments