How to determine which file to attach

How to determine which file to attach

Step 1

User can find out which are the file attached to a particular database in this case it is Demo by simply using the following command,

USE Demo

EXEC SP_HELPFILE

The SQL Analyzer will return a list of file associated with this file.

How to detach and attach Database to MSSQL Server

How to detach and attach Database to MSSQL Server

Why would you need to attach a database to MSSQL Server? Sometime when your system fails and you only manage to backup a your .MDF file(s) and no backup is available.

Step 1

Then in order to activate your database again, or attach it to a healthy running system, use to following command :-

exec sp_attach_db @dbname='Demo',

@filename1='D:\Demo\Demo_data.mdf',

@filename2='D:\Demo\Demo2.mdf',

@filename3='D:\Demo\demo_log.ldf'

Step 2

Detaching a database can be accomplish by executing the following command

EXEC SP_DETACH_DB Demo

Whereby Demo is our database name.

How to find a list of database available in the system

How to find a list of database available in the system

Step 1

To find a list of database attached to MSSQL Server, this will normally be shown when you are in MSSQL Server Enterprise Manager.

Step 2

But to have MSSQL Server SQL analyzer, you can find a list of attached database in your system by :

EXEC sp_helpdb

How to remove Database

How to remove Database

Step 1

To remove database from MS SQL Server, go to Enterprise Manager and then select Demo database. Right click on it and select Delete.

Step 2

Or by pressing Delete or by using T-SQL command in SQL Analyzer

DROP DATABASE Demo