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