MS SQL Server - Re: Error while attempting to attach database file from a READ-ONL

Author

Re: Error while attempting to attach database file from a READ-ONL

Steven Belbin

2006-03-14, 9:23 am

Thanks for the reply. However, this is not a situation of detaching and then
re-attaching a database files in a READ-ONLY mode.

The situation that I facing with is more analogous to an image of the
database files after a volume snapshot has been done. The images of the
database files which I am attempting to attach are just after a performing a
recovery checkpoint on the database has been performed. The image of database
files is one in which the database still remains online and are still
attached to the SQL Server 2005 instance.

What I need is to be able to configure SQL Server 2005 instance or to
provide as an option for the ATTACH command, requesting SQL Server 2005 not
to start a recovery procedure when attaching this database.

Thanks in advance to any that replies to this posting...

"Tibor Karaszi" wrote:

> I just tried this, and I could detach a database, mark the files as read.only and then attach the
> database. No error. Trying to create a table in the database resulted in an error message.
>
> Perhaps the database was created with a prior revision number of SQL Server and the attach process
> need to make some changes. Try to verify the revision numbers between the detaching SQL Server and
> the attaching.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "Steven Belbin" <Steven Belbin@discussions.microsoft.com> wrote in message
> news:29BFAB41-B698-41CD-96C3- 38EFC3A3D703@microso
ft.com...
>
>
>

Roger Wolter[MSFT]

2006-03-15, 3:23 am

It's not trying to do a recovery. The database is from an older version of
SQL Server so it must be updated before it can be attached. You will have
to write enable it to attach it and then make it read only.

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

"Steven Belbin" < StevenBelbin@discuss
ions.microsoft.com> wrote in message
news:DE17612A-A79C-4A58-9341- 436B8DDFC707@microso
ft.com...[color=darkred]
> Thanks for the reply. However, this is not a situation of detaching and
> then
> re-attaching a database files in a READ-ONLY mode.
>
> The situation that I facing with is more analogous to an image of the
> database files after a volume snapshot has been done. The images of the
> database files which I am attempting to attach are just after a performing
> a
> recovery checkpoint on the database has been performed. The image of
> database
> files is one in which the database still remains online and are still
> attached to the SQL Server 2005 instance.
>
> What I need is to be able to configure SQL Server 2005 instance or to
> provide as an option for the ATTACH command, requesting SQL Server 2005
> not
> to start a recovery procedure when attaching this database.
>
> Thanks in advance to any that replies to this posting...
>
> "Tibor Karaszi" wrote:
>

Tibor Karaszi

2006-03-15, 7:29 am

To the best of my knowledge, only doing a CHECKPOINT will not mean that the database files will have
the "clean bit" set in them, and without that bit, SQL Server will try to perform recovery on
attach.

Roger certainly has more insight to the reasons to your particular error message, but even if it
happened to be a version issue and that will be sorted out, I'd be very careful with such an
implementation.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/

"Steven Belbin" < StevenBelbin@discuss
ions.microsoft.com> wrote in message
news:DE17612A-A79C-4A58-9341- 436B8DDFC707@microso
ft.com...[color=darkred]
> Thanks for the reply. However, this is not a situation of detaching and then
> re-attaching a database files in a READ-ONLY mode.
>
> The situation that I facing with is more analogous to an image of the
> database files after a volume snapshot has been done. The images of the
> database files which I am attempting to attach are just after a performing a
> recovery checkpoint on the database has been performed. The image of database
> files is one in which the database still remains online and are still
> attached to the SQL Server 2005 instance.
>
> What I need is to be able to configure SQL Server 2005 instance or to
> provide as an option for the ATTACH command, requesting SQL Server 2005 not
> to start a recovery procedure when attaching this database.
>
> Thanks in advance to any that replies to this posting...
>
> "Tibor Karaszi" wrote:
>

Steven Belbin

2006-03-15, 11:23 am

Thanks Roger for the reply.

However, this is not the case of a database that was created from a prior
version of SQL Server. This database which I attempting to attach with
read-only attributes is one that is created on the very same release SQL
Server 2005, in fact even when I attempt to attach on the very SQL Server
2005 instance that created this error message occurs.

Tibor's remarks about the "clean bit" make sense, since I attempting to
attach a database files from the read-only volume image, where it is possible
that database was not offline at the time of the image.

To provide more context, I work in the data protection business in which we
offer a read-only volume that is a point in time image of files from a
read-write volume.

Even though you do not our wonderful software, you too can experience this
with the following steps:
[Try with SQL Server 2000 to see that there is a difference]

a) Configure the SQL Server instance (NT service) to manual.

b) Create a new database, wait for the database to be created.

c) Issue a CHECKPOINT command on this new database.

d) Power down the server (not a shutdown) were simulating a crash.

e) Power up the server.
[Make surethe SQL Server instance NT service has not started].

f) Copy the database files into a different directory, changing the file
system attributes for read-only.

g) Start the SQL Server instance.

h) Attach a new database using the copied files from the step (f).
[You will probably need to give it a new name, since the name already
exists.]

o On SQL Server 2005, the attach fails with the error 3415 message.

o On SQL Server 2000, the attach succeeds and can access the database.

Since a databases which our product is protecting maybe huge and it would
take a long time to transfer from a read-only volume onto a read-write volume
are the primary reasons why I need to know if there is a way in which to turn
off the recovery procedure for SQL Server 2005 release during a database
attach operation.

Thanks...

"Tibor Karaszi" wrote:

> To the best of my knowledge, only doing a CHECKPOINT will not mean that the database files will have
> the "clean bit" set in them, and without that bit, SQL Server will try to perform recovery on
> attach.
>
> Roger certainly has more insight to the reasons to your particular error message, but even if it
> happened to be a version issue and that will be sorted out, I'd be very careful with such an
> implementation.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www. solidqualitylearning
.com/
>
>
> "Steven Belbin" < StevenBelbin@discuss
ions.microsoft.com> wrote in message
> news:DE17612A-A79C-4A58-9341- 436B8DDFC707@microso
ft.com...
>
>
>

Sponsored Links