How to attach DB in Standby Mode

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 2005 Forums

SQL Server Administration (2005)

How to attach DB in Standby Mode

Author

Topic

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2010-07-02 : 17:13:33

I had a databse in stand by mode. I deatched it, Now I am not able to attach it, Any suggestion or link.

Thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-03 : 03:31:57

You cannot simply attach it. Only databases that are online when they are attached can be reattached. To get it back, you're going to have to hack it back into the server and it may go back successfully. See http://sqlskills.com/BLOGS/PAUL/post/Disaster-recovery-101-hack-attach-a-damaged-database.aspx

Standby as in log shipping? It may simply be easier to simply restore a full backup of the primary.

--
Gail Shaw
SQL Server MVP

Yeoh Ray Mond
Starting Member

49 Posts

Posted - 2010-07-03 : 08:00:19

Or if the failure to attach the database was because of system error 5 (access is denied), adjust the permissions of the database files so that the SQL Server service startup account has full rights to those files.

Ray Mond
SQLBackupRestore.com - your quick guide to SQL Server backup and recovery issues
SQL Image Viewer - retrieve, view, convert and export images directly from SQL Server, SQLite and Firebird databases.
SQL Data Sets - share and distribute SQL Server, Oracle and PostgreSQL data sets securely and easily

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-03 : 09:56:27

A database that was in standby cannot be reattached, it has nothing to do with OS permissions.

I wish SQL would not allow such DBs to be detached in the first place.

--
Gail Shaw
SQL Server MVP

Yeoh Ray Mond
Starting Member

49 Posts

Posted - 2010-07-03 : 22:23:46

>> A database that was in standby cannot be reattached, it has nothing to do with OS permissions.

The OP never mentioned how the database got into standby mode, it could've been a result of a restore process, in which case, it's true that it cannot be detached and reattached. It could also have been due to a log backup (example below). OS permissions do play a part if the SQL Server service startup account differs from the log-on user account.

The following example demonstrates both points. Would be clearer if your SQL Server service startup account is a Windows account that's not your currently logged-on user.

-- create the database, note the Windows security permissions on the mdf file
create database logtest102

-- back up the database
backup database logtest102 to disk = 'e:\temp\logtest102.bak' with format

-- back up the log, set to standby mode
backup log logtest102 to disk = 'e:\temp\logtest102_log.bak' with format, standby = 'e:\temp\logtest102.und'

-- prove that the table is in standby/read-only mode
create table logtest102..x103 (col1 int) -- results in an error

-- detach the database, note the Windows security permissions on the mdf file now
sp_detach_db logtest102

-- reattach the database
sp_attach_db 'logtest102', 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\logtest102.mdf', 'E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\logtest102_log.ldf'

-- assuming the necessary rights are in place, the database is reattached, but the database is in normal mode, no longer in read-only mode

Thanks.

Ray Mond
SQLBackupRestore.com - your quick guide to SQL Server backup and recovery issues
SQL Image Viewer - retrieve, view, convert and export images directly from SQL Server, SQLite and Firebird databases.
SQL Data Sets - share and distribute SQL Server, Oracle and PostgreSQL data sets securely and easily

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-04 : 05:45:39

*Edit*: Removing garbage

Kristen
Test

22859 Posts

Posted - 2010-07-04 : 05:54:15

Our house rule is to never detach a database unless we have a backup. And if we have a backup its usually easier to use RESTORE instead of De/Re-attach ...

Yeoh Ray Mond
Starting Member

49 Posts

Posted - 2010-07-04 : 08:18:33

>> once the DB is in standby, it can be detached, but cannot be reattached.

Did you at least try the example that I provided? An online database could be put into a standby state, detached, then re-attached without any problems.

Ray Mond
SQLBackupRestore.com - your quick guide to SQL Server backup and recovery issues
SQL Image Viewer - retrieve, view, convert and export images directly from SQL Server, SQLite and Firebird databases.
SQL Data Sets - share and distribute SQL Server, Oracle and PostgreSQL data sets securely and easily

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-07-04 : 09:16:27

Interesting. May be because there's nothing in the undo files.

p.s. SQL 2005+ strips the permissions (other than SQL service account) off of the files when detaching the DB. Security. That permission is all that's necessary to reattach. Only can have a problem if someone fiddled with the permissions while the DB was detached, and errors are very different.

Permissions will be something along the lines of "Cannot open database file. Error 5", otherwise will be "Cannot attach a database that was being restored."

--
Gail Shaw
SQL Server MVP

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2010-07-05 : 16:05:27

Thanks all of you for giving valuable suggestions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources