How to detach and attach a SQL database?

book

Article ID: 100023226

calendar_today

Updated On:

Resolution

Using OSQL commands:

1. Make sure the database is not accessed by multiple user. Start the SQL service with -m as the startup type which will start the service in single user mode.(Figure 1)

Figure 1

Open a command line window and connect to the Microsoft SQL Server instance

To connect to the Backup Exec instance of SQL Express, type:

osql -E -S server name\INSTANCENAME

You can also use the below mentioned command for putting a specific database under Single User Mode instead of putting the entire instance under Single User Mode:

1>EXEC sp_dboption 'DATABASENAME', 'single user', 'TRUE'

2>Go

2. Stop all Backup Exec related services. Use Windows Services.msc or in Backup Exec Services Manager.

3. To detach the database, type the following command at the OSQL prompt:

1>EXEC sp_detach_db DATABASENAME

2>Go

4. To attach the database, type the following command at the OSQL prompt:

osql -E -S server name\INSTANCENAME

1> use master

2> go

1> sp_attach_db DATABASENAME, 'mdf files path' , 'ldf files path'

2> go

Note: Default backup exec instance name : BKUPEXEC
Default backup exec database name : BEDB
Default mdf file path : X:\program files\Symantec\backup exec\data\BEDB_dat.mdf
Default ldf file path : X:\program files\Symantec\backup exec\data\BEDB_log.ldf

Using SQL management studio:

1. Configure the database to run in single user mode by going to the database properties->option->restrict access (Figure 2)

Figure 2

2. Right click the database name->tasks->detach and click OK on the database detach window.

3. Right click databases->attach->add and point to the mdf file. It will automatically take the ldf file to the attach databases window.

4. Click OK to complete the attach database process