Modeblue

In MSSQL2k, a lot of users would use the detach command within the GUI to kill all the active processes on a DB, this functionality is somewhat cut down in MSSQL2005 – you can see the processes & kill them individually, but not as a bulk kill.

To detach a database in Tsql, you can use:
EXEC sp_detach_db 'database', 'true'
This will detach the DB and skip checks on doing so.

Attaching database files to a DB Server is similar – a simple attach command would be:
exec sp_attach_db @dbname = ‘database’,
@filename1 = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\data.mdf’,
@filename2 = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\data_log.ldf’
However, if you wish to attach a database, and you have the MDF file, but not the log file, you can attach the DB (albeit losing anything that was only in the logs) with a attach command:
EXEC sp_attach_single_file_db @dbname = ‘database’,
@filename1 = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\data.mdf’

This is very useful for restoring DBs, for recovering old servers or from backups and for binning a log if errors occured during the period saved in them.

However, this doesn’t work with multiple logfiles – I learnt this the hard way & have now been waiting 3 days for a 700Gb database to restore from the very recent backup I had to hand (I’m a fool, not an idiot!).

The moral of the story is don’t delete files unless you are 100% ceratin you no longer need them!