Attach Database

Is there a way to attach a database and leave it in a read-
only mode to restore additional transaction log files just
like restoring from a backup file '
Thanks.You are talking about two different things here. When you attach a database,
the db is going to be online, unlike 'restoring from a backup'.
Could you explain what it is that you are trying to do?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:fa8f01c43e76$29d56350$a601280a@.phx.gbl...
Is there a way to attach a database and leave it in a read-
only mode to restore additional transaction log files just
like restoring from a backup file '
Thanks.|||Why it is so different. In both cases, the database can be
online. It is much faster to attach a database than
restoring a database from a backup. If the database is
large (More that 50 GB) copying the *.mdf and *.ldf files
to a backup server rather than backing up it may take
shorter time.

>--Original Message--
>You are talking about two different things here. When you
attach a database,
>the db is going to be online, unlike 'restoring from a
backup'.
>Could you explain what it is that you are trying to do?
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"David" <anonymous@.discussions.microsoft.com> wrote in
message
>news:fa8f01c43e76$29d56350$a601280a@.phx.gbl...
>Is there a way to attach a database and leave it in a
read-
>only mode to restore additional transaction log files just
>like restoring from a backup file '
>Thanks.
>
>.
>|||Like the other post said the restoration and attaching are two different
things. Now if you want to put a database into read-only mode after the
attach you can issue the following T-SQL command:
exec sp_dboption 'yourdb','read only','true'
To turn off read only issue the follwoing:
exec sp_dboption 'yourdb','read only','false'
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:fa8f01c43e76$29d56350$a601280a@.phx.gbl...
> Is there a way to attach a database and leave it in a read-
> only mode to restore additional transaction log files just
> like restoring from a backup file '
> Thanks.|||What?
If you're attaching a database you detached, how are you confident that you
can accurately apply transaction logs to it to bring it up to date?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:fbb301c43e79$757bd070$a501280a@.phx.gbl...[vbcol=seagreen]
> Why it is so different. In both cases, the database can be
> online. It is much faster to attach a database than
> restoring a database from a backup. If the database is
> large (More that 50 GB) copying the *.mdf and *.ldf files
> to a backup server rather than backing up it may take
> shorter time.
>
>
> attach a database,
> backup'.
> message
> read-|||I am stopping the SQL Server services to copy the *.mdf
and *.ldf file. When I started the services transaction
log backups resumes according to the maintenance plan. SO,
what is so different. What does Stopping and restarting
services changes''/

>--Original Message--
>What?
>If you're attaching a database you detached, how are you
confident that you
>can accurately apply transaction logs to it to bring it
up to date?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"David" <anonymous@.discussions.microsoft.com> wrote in
message
>news:fbb301c43e79$757bd070$a501280a@.phx.gbl...
be[vbcol=seagreen]
files[vbcol=seagreen]
you[vbcol=seagreen]
just[vbcol=seagreen]
>
>.
>|||That's not a proper detach! So now you're assuming that your hack method
will allow you to attach such an MDF file without problems? Have you tested
this scenario? Why would you use this method as opposed to accepted and
proven methods?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:fbfa01c43e7c$80b3cb70$a501280a@.phx.gbl...[vbcol=seagreen]
> I am stopping the SQL Server services to copy the *.mdf
> and *.ldf file. When I started the services transaction
> log backups resumes according to the maintenance plan. SO,
> what is so different. What does Stopping and restarting
> services changes''/
>
> confident that you
> up to date?
> message
> be
> files
> you
> just|||It is a HACK method to you and not to me. What difference
would it make if I detach it and copy it '? The
question is why the same options (READ_ONLY, SINGLE_USER
e.t.c) that can be applied to backup restore can not be
applied to attach database...........

>--Original Message--
>That's not a proper detach! So now you're assuming that
your hack method
>will allow you to attach such an MDF file without
problems? Have you tested
>this scenario? Why would you use this method as
opposed to accepted and
>proven methods?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"David" <anonymous@.discussions.microsoft.com> wrote in
message
>news:fbfa01c43e7c$80b3cb70$a501280a@.phx.gbl...
SO,[vbcol=seagreen]
you[vbcol=seagreen]
can[vbcol=seagreen]
is[vbcol=seagreen]
When[vbcol=seagreen]
from a[vbcol=seagreen]
do?[vbcol=seagreen]
in[vbcol=seagreen]
a[vbcol=seagreen]
files[vbcol=seagreen]
>
>.
>|||> What difference would it make if I detach it and copy it '?
Because the system does a proper detach and makes sure the db/log are in
sync and can be attached to other systems flawlessly. If you stop the SQL
Server service, this synchronization is not necessarily going to be done for
you (because this SQL Server knows it can resume any ongoing operations, or
roll them back, when the server comes back online...).
What is the BENEFIT you are deriving from stopping the service and copying
the files (which is, absolutely, a hack) as compared to using generally
accepted methods (restore/backup, detach/attach, etc)?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||To make a long story short,
You cannot do this. In order to restore the transaction logs you have to
first restore the database and then restore all the logs in the proper
order, starting with the first one after teh most recent backup.. You
cannot pick and choose which logs you want to restore. So attaching is a
moot point because you still have to restore the database backup in order
to restore any of the logs..
To answer your question more simply:
SQL Server was not designed to work in the manner in which are suggesting.
Rand
This posting is provided "as is" with no warranties and confers no rights.sql