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

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[vbcol=seagreen]
>news:fbb301c43e79$757bd070$a501280a@.phx.gbl...
be[vbcol=seagreen]
files[vbcol=seagreen]
you[vbcol=seagreen]
just
>
>.
>
|||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[vbcol=seagreen]
>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
>
>.
>
|||> 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.

Attach a read-only restored database

hi all
can i attach a read only restored database. I restored a
database Test in Stand by Mode. After that i detached that database and
while restoring i m getting msg "Cannot attach a database that was
being restored.". Is it o that i can not attach a readonly restored
detached database ?
Parveen BeniwalHi
A read only database will remain read only if you restore it with the
RESTORE WITH RECOVERY option, you do not need to put it into standby as you
will not be restoring any transaction logs.
John
"Parv" wrote:

> hi all
> can i attach a read only restored database. I restored a
> database Test in Stand by Mode. After that i detached that database and
> while restoring i m getting msg "Cannot attach a database that was
> being restored.". Is it o that i can not attach a readonly restored
> detached database ?
> Parveen Beniwal
>|||Hi,
I think I have the same question, worded differently:
We are using our SAN to make snapshot copies of the database files of a
log-shipped copy of a database (of course these copies are in read-only
mode). We want to then use those copies. However, we cannot get those
files to attach. We get the same error "Cannot attach a database that was
being restored".
Is there some trick to attaching database files that are in read-only mode?
Note that it is ok if the database copy loses it's read-only status during
the attach process, since we don't plan to restore additional logs to that
copy.
Thanks!
--Chip Matthes
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> A read only database will remain read only if you restore it with the
> RESTORE WITH RECOVERY option, you do not need to put it into standby as yo
u
> will not be restoring any transaction logs.
> John
> "Parv" wrote:
>|||Hi
This works fine! With restore the database stays read only, with attach it
gets reset
CREATE DATABASE [MyReadOnlyDatabase]
ON
( NAME = 'MyReadOnlyDatabase_data',
FILENAME = 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MyReadOnlyDatabase.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'MyReadOnlyDatabase_log',
FILENAME = 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MyReadOnlyDatabase.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
ALTER DATABASE [MyReadOnlyDatabase] SET READ_ONLY
GO
EXEC sp_helpdb [MyReadOnlyDatabase]
GO
EXEC sp_detach_db [MyReadOnlyDatabase]
GO
EXEC sp_attach_db 'MyReadOnlyDatabase',
'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MyReadOnlyDatabase.mdf',
'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MyReadOnlyDatabase.ldf'
GO
EXEC sp_helpdb [MyReadOnlyDatabase]
GO
ALTER DATABASE [MyReadOnlyDatabase] SET READ_ONLY
GO
EXEC sp_helpdb [MyReadOnlyDatabase]
GO
BACKUP DATABASE [MyReadOnlyDatabase]
TO DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\MyReadOnlyDatabase.bak'
GO
DROP DATABASE [MyReadOnlyDatabase]
GO
RESTORE DATABASE [MyReadOnlyDatabase]
FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\MyReadOnlyDatabase.bak'
GO
EXEC sp_helpdb [MyReadOnlyDatabase]
GO
HTH
John
"Chip Matthes" wrote:
[vbcol=seagreen]
> Hi,
> I think I have the same question, worded differently:
> We are using our SAN to make snapshot copies of the database files of a
> log-shipped copy of a database (of course these copies are in read-only
> mode). We want to then use those copies. However, we cannot get those
> files to attach. We get the same error "Cannot attach a database that was
> being restored".
> Is there some trick to attaching database files that are in read-only mode
?
> Note that it is ok if the database copy loses it's read-only status during
> the attach process, since we don't plan to restore additional logs to that
> copy.
> Thanks!
> --Chip Matthes
> "John Bell" wrote:
>

Attach a read-only restored database

hi all
can i attach a read only restored database. I restored a
database Test in Stand by Mode. After that i detached that database and
while restoring i m getting msg "Cannot attach a database that was
being restored.". Is it o that i can not attach a readonly restored
detached database ?
Parveen BeniwalHi
A read only database will remain read only if you restore it with the
RESTORE WITH RECOVERY option, you do not need to put it into standby as you
will not be restoring any transaction logs.
John
"Parv" wrote:
> hi all
> can i attach a read only restored database. I restored a
> database Test in Stand by Mode. After that i detached that database and
> while restoring i m getting msg "Cannot attach a database that was
> being restored.". Is it o that i can not attach a readonly restored
> detached database ?
> Parveen Beniwal
>|||Hi,
I think I have the same question, worded differently:
We are using our SAN to make snapshot copies of the database files of a
log-shipped copy of a database (of course these copies are in read-only
mode). We want to then use those copies. However, we cannot get those
files to attach. We get the same error "Cannot attach a database that was
being restored".
Is there some trick to attaching database files that are in read-only mode?
Note that it is ok if the database copy loses it's read-only status during
the attach process, since we don't plan to restore additional logs to that
copy.
Thanks!
--Chip Matthes
"John Bell" wrote:
> Hi
> A read only database will remain read only if you restore it with the
> RESTORE WITH RECOVERY option, you do not need to put it into standby as you
> will not be restoring any transaction logs.
> John
> "Parv" wrote:
> > hi all
> > can i attach a read only restored database. I restored a
> > database Test in Stand by Mode. After that i detached that database and
> > while restoring i m getting msg "Cannot attach a database that was
> > being restored.". Is it o that i can not attach a readonly restored
> > detached database ?
> >
> > Parveen Beniwal
> >
> >|||Hi
This works fine! With restore the database stays read only, with attach it
gets reset
CREATE DATABASE [MyReadOnlyDatabase]
ON
( NAME = 'MyReadOnlyDatabase_data',
FILENAME = 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MyReadOnlyDatabase.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'MyReadOnlyDatabase_log',
FILENAME = 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MyReadOnlyDatabase.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
ALTER DATABASE [MyReadOnlyDatabase] SET READ_ONLY
GO
EXEC sp_helpdb [MyReadOnlyDatabase]
GO
EXEC sp_detach_db [MyReadOnlyDatabase]
GO
EXEC sp_attach_db 'MyReadOnlyDatabase',
'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MyReadOnlyDatabase.mdf',
'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\MyReadOnlyDatabase.ldf'
GO
EXEC sp_helpdb [MyReadOnlyDatabase]
GO
ALTER DATABASE [MyReadOnlyDatabase] SET READ_ONLY
GO
EXEC sp_helpdb [MyReadOnlyDatabase]
GO
BACKUP DATABASE [MyReadOnlyDatabase]
TO DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\MyReadOnlyDatabase.bak'
GO
DROP DATABASE [MyReadOnlyDatabase]
GO
RESTORE DATABASE [MyReadOnlyDatabase]
FROM DISK = 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\MyReadOnlyDatabase.bak'
GO
EXEC sp_helpdb [MyReadOnlyDatabase]
GO
HTH
John
"Chip Matthes" wrote:
> Hi,
> I think I have the same question, worded differently:
> We are using our SAN to make snapshot copies of the database files of a
> log-shipped copy of a database (of course these copies are in read-only
> mode). We want to then use those copies. However, we cannot get those
> files to attach. We get the same error "Cannot attach a database that was
> being restored".
> Is there some trick to attaching database files that are in read-only mode?
> Note that it is ok if the database copy loses it's read-only status during
> the attach process, since we don't plan to restore additional logs to that
> copy.
> Thanks!
> --Chip Matthes
> "John Bell" wrote:
> > Hi
> >
> > A read only database will remain read only if you restore it with the
> > RESTORE WITH RECOVERY option, you do not need to put it into standby as you
> > will not be restoring any transaction logs.
> >
> > John
> >
> > "Parv" wrote:
> >
> > > hi all
> > > can i attach a read only restored database. I restored a
> > > database Test in Stand by Mode. After that i detached that database and
> > > while restoring i m getting msg "Cannot attach a database that was
> > > being restored.". Is it o that i can not attach a readonly restored
> > > detached database ?
> > >
> > > Parveen Beniwal
> > >
> > >

Assigning a lower priority to some users in SQL Server.

I have a production database used by a web site, and at the same time a group of read-only users who can query the database directly “without the web site”

When one of the users runs a complex query, it slows down the server, and affects the web site.

Is it possible to change the SQL User account or SQL User Group’s priority to low?

You know, the same like in the Task Manager and Windows, I can change a process to low, so it will not affect the important processes, can I do this in SQL Server, and is there any workaround.

in my humble opinion

its the query that needs to be changed

or if your using 2005 you can implement HA feature

such as database snapshot, mirroring etc.

|||Besides the guessing, does anyone have a real solution? Are there sql execution priorities available in SQL Server? Or they are just in the deal databases, like Oracle?|||

if you are so convince that thats the best query you can write and there is no

room for improvement then you can schedule your heavy process to run

on offpeak times.

if that heavy process cross the line of tolerable performance your only option

is to kill that process.

the solution to your problem are

1. send readonly report users to a database snapshot if you are using 2k5

2. schedule the process to run on offpeak times

3. use page caching, fragment caching and most importanctly database caching in asp.net or on your website so you dont rely much on your database

By the way, what does this complex query do? if you would not mind.

how complex is it?

ASPNETDB.MDF is read-only....

Hello everyone.

I am to create a sample site using the club site example. I had a problem with the IIS but now it is solved.

Now i copyied the site to the IIS and it is running. But i cannot access ASPNETDB.MDF. It keep saying that the database is read only... It is not read only!!!

Can any one help me??

System.Data.SqlClient.SqlException: Failed to update database "C:\INETPUB\WWWROOT\NEO\APP_DATA\ASPNETDB.MDF" because the database is read-only.

Thank you in advance.

Iasonas

Try granting the NETWORK SERVICE account R/W access to the contents of your app_data folder (mdf's and ldf's)and the folder itself.

If that doesn't work, please take a look at this post:

http://forums.asp.net/thread/906040.aspx

Why not using search feature in this formus:) Maybe you issue is a common one, then you can find some possible solution by searching the formus rather than just waiting:)