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...
> 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.
> >
> >
> >.
> >|||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...
>> 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.
>> >
>> >
>> >.
>> >
>
>.
>|||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...
> 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...
> >> 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.
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||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...
>> 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...
>> >> 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.
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>|||> 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.|||Rand voiced my opinion much more succinctly. ;-)|||I did not mean to be harsh on anyone. I am aware of all of
this. It was just an idea I was throwing it may be
possible one day..........
I was looking for a way out from database backup taking 4
hours every night and restoring on the backup server. When
you have a few database on production server that take too
long to backup and restore along with all the other
processes (Nightly loads, data extracts e.t.c) there isn't
enough time to do everything. Plus a cheap management who
don't want to pay for any third party tools.
Thanks everyone.........
>--Original Message--
>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.
>.
>|||Log shipping? Transactional replication? Clustering?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"David" <anonymous@.discussions.microsoft.com> wrote in message
news:fd9401c43ea6$c3862c20$a601280a@.phx.gbl...
> I did not mean to be harsh on anyone. I am aware of all of
> this. It was just an idea I was throwing it may be
> possible one day..........
> I was looking for a way out from database backup taking 4
> hours every night and restoring on the backup server. When
> you have a few database on production server that take too
> long to backup and restore along with all the other
> processes (Nightly loads, data extracts e.t.c) there isn't
> enough time to do everything. Plus a cheap management who
> don't want to pay for any third party tools.
> Thanks everyone.........|||It is SQL Server 2000 Standard. Our manager thinks
Transactional replication is resource intense......
>--Original Message--
>log shipping is the best way and the cheapest way, if you
have sql server enterprise edition
> -- Aaron Bertrand - MVP wrote: --
> Log shipping? Transactional replication?
Clustering?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "David" <anonymous@.discussions.microsoft.com> wrote
in message
> news:fd9401c43ea6$c3862c20$a601280a@.phx.gbl...
> > I did not mean to be harsh on anyone. I am aware
of all of
> > this. It was just an idea I was throwing it may be
> > possible one day..........
> >> I was looking for a way out from database backup
taking 4
> > hours every night and restoring on the backup
server. When
> > you have a few database on production server that
take too
> > long to backup and restore along with all the other
> > processes (Nightly loads, data extracts e.t.c)
there isn't
> > enough time to do everything. Plus a cheap
management who
> > don't want to pay for any third party tools.
> >> Thanks everyone.........
>
>.
>|||> Our manager thinks
Well, that's scary on its own.
However, instead of letting the manager think something, why not test it and
prove it one way or the other? Rejecting an option because of someone's
perception is a huge mistake, and the manager should be the *first* to
recognize that, if he's worth his salt.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Log shipping can be performed with any edition, you just cannot just the built-in stuff in EM and Maint wiz
for it. Search and you will find scripts on the net that does the same.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"roy" <anonymous@.discussions.microsoft.com> wrote in message
news:7F7BB8B6-90B8-471F-91B9-6111E79BF8AE@.microsoft.com...
> log shipping is the best way and the cheapest way, if you have sql server enterprise edition
> -- Aaron Bertrand - MVP wrote: --
> Log shipping? Transactional replication? Clustering?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "David" <anonymous@.discussions.microsoft.com> wrote in message
> news:fd9401c43ea6$c3862c20$a601280a@.phx.gbl...
> > I did not mean to be harsh on anyone. I am aware of all of
> > this. It was just an idea I was throwing it may be
> > possible one day..........
> >> I was looking for a way out from database backup taking 4
> > hours every night and restoring on the backup server. When
> > you have a few database on production server that take too
> > long to backup and restore along with all the other
> > processes (Nightly loads, data extracts e.t.c) there isn't
> > enough time to do everything. Plus a cheap management who
> > don't want to pay for any third party tools.
> >> Thanks everyone.........
>
>