Dettach_Attach Database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Dettach_Attach Database

Author

Topic

vdavid70
Yak Posting Veteran

67 Posts

Posted - 2007-01-23 : 08:09:24

Hello,

How else can i put a database in single user mode appart from the one below.

Every time i try to do this it comes up with error saying the database cannot be put in single user mode cause it is in use.

Please check the code below to see if i am doing something wrong.

use Sostenutotest

EXEC sp_dboption 'Sostenutotest ', 'single user', true

go

EXEC sp_detach_db 'Sostenutotest', 'true'
go
EXEC sp_attach_db @dbname = N'Sostenutotest',
@filename1 = N'X:\Microsoft SQL Server\MSSQL\Data\Sostenutotest\Sostenutotest.MDF',
@filename2 = N'X:\Microsoft SQL Server\MSSQL\Data\Sostenutotest\Sostenutotest_1.MDF',
@filename3 = N'X:\Microsoft SQL Server\MSSQL\Data\Sostenutotest\Sostenutotest_2.MDF',
@filename4 = N'X:\Microsoft SQL Server\MSSQL\Data\Sostenutotest\Sostenutotest_3.MDF',
@filename5 = N'X:\Microsoft SQL Server\MSSQL\Data\Sostenutotest\Sostenutotest_Log.LDF'

EXEC sp_dboption 'Sostenutotest ', 'single user', false

Kristen
Test

22859 Posts

Posted - 2007-01-24 : 12:55:37

"How else can i put a database in single user mode"

This is what I use:

ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Kristen

madhuotp
Yak Posting Veteran

78 Posts

Posted - 2007-01-25 : 00:15:50

execute the same script from Master not from the user database which u r attaching. ie

For
use Sostenutotest

Replace with

Use Master

Madhu

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-25 : 00:52:25

You don't need to have the database in single user mode. You need to get the users out of the database and keep them out till you detach it. The best way is to take it offline so no one can connect to it.


use master
go
alter database Sostenutotest set offline with rollback immediate

EXEC sp_detach_db 'Sostenutotest', 'true'

EXEC sp_attach_db @dbname = N'Sostenutotest', 
@filename1 = N'X:\Microsoft SQL Server\MSSQL\Data\Sostenutotest\Sostenutotest.MDF', 
@filename2 = N'X:\Microsoft SQL Server\MSSQL\Data\Sostenutotest\Sostenutotest_1.MDF',
@filename3 = N'X:\Microsoft SQL Server\MSSQL\Data\Sostenutotest\Sostenutotest_2.MDF',
@filename4 = N'X:\Microsoft SQL Server\MSSQL\Data\Sostenutotest\Sostenutotest_3.MDF',
@filename5 = N'X:\Microsoft SQL Server\MSSQL\Data\Sostenutotest\Sostenutotest_Log.LDF'

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources