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'

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'

4. Suspect Database.

USE Testdb

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';

Status

--------

SUSPECT

ALTER DATABASE Testdb SET EMERGENCY; == This lets us access to the database, but

ALTER DATABASE Testdb SET SINGLE_USER;

DBCC CHECKDB (Testdb, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;