April 13, 2010 at 3:30 am
Hello,
in a sql srv 2008 I have to backup and restore (every 2-3 days) 2 or 3 filegroups (containing each 1 file). Recovery model is Simple in my DB.
These filegroups can be marked as ReadOnly (no write access on them).
SQL Server online backup & restore method allows to perform backup and restore filegroup in a DB in online status and in simple recovery model, BUT filegroup HAVE TO be altered to Read_Only status.
So there is a problem.. As far as I know, the TSQL
[font="Courier New"]ALTER DATABASE [DBNAME] MODIFY FILEGROUP MYFG READ_ONLY[/font]
give this error message in a multi-user DB state:
[font="Courier New"]Msg 5070, Level 16, State 2, Line 1
Database state cannot be changed while other users are using the database 'DBNAME'[/font]
In a single user state.. no problem...
So, what can I do?? I must keep online my DB and I have to set as readonly some filegroups..
Help! Thanks!!
Simone
April 16, 2010 at 3:47 am
You should have been posted this issue in sql 2008 section to get faster(any) response.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 17, 2010 at 2:59 am
Yes, but I think this is an issue in the backup behaviour of 2005\2008 (they're very similar) so I posted here..
April 19, 2010 at 2:18 am
put the database in SINGLEUSER mode and then do the alteration
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 19, 2010 at 2:58 am
Ok, it will work, I am sure, but if there will be a lot of "w3wp user" (web servers) connected.... boom, connection down during my "single user alter db command"... 🙁
April 19, 2010 at 3:18 am
These kind of task generally done in off peak hours with the proper intimation to users
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 10, 2011 at 4:57 am
Is there any possibility to modify single filegroup to readonly in multi user mode ?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply