January 12, 2009 at 9:54 pm
Comments posted to this topic are about the item Database Snapshots
January 13, 2009 at 12:19 am
If you did not update your books online then you missed this one:
5 December 2005
New content:
Added limitation about using database snapshots with log shipping.
Added a note about resource consumption.
Changed content:
Corrected technical error to clarify that you cannot bring a filegroup online when a database snapshot exists.
Missed 1 point 🙁
January 13, 2009 at 6:35 am
Robbert Hof (1/13/2009)
If you did not update your books online then you missed this one:...
Changed content:
Corrected technical error to clarify that you cannot bring a filegroup online when a database snapshot exists. [/i]
Missed 1 point 🙁
I use BOL from the MSDN site, and there it indeed states it cannot be done.
Since that is also the correct answer to this QotD, I don't get your point (no pun intended). Or did you yourself use an offline BOL (strange combination, if you think of it)?
Correct answer:
No
Explanation:
It is not possible. This is because bringing a file back online involves restoring it, which is not possible if a database snapshot exists on the database.
Ref: Limitations on Database Snapshots - http://msdn.microsoft.com/en-us/library/ms189940.aspx%5B/quote%5D
Peter Rijs
BI Consultant, The Netherlands
January 13, 2009 at 7:02 am
I used BOL (April 2006) installed on a workstation it says:
Bring a filegroup online.
You can bring a filegroup online in the source database; however, this does not affect its existing database snapshots. Any filegroup that is offline at the time of snapshot creation remains offline in the snapshots.
More or less Books OffLine indeed 😉
January 15, 2009 at 7:35 am
I think the original question is slightly misleading; only files can be taken offline individually - there is no ALTER DATABASE statement to take a filegroup offline.
January 15, 2009 at 8:13 am
liam.north (1/15/2009)
I think the original question is slightly misleading; only files can be taken offline individually - there is no ALTER DATABASE statement to take a filegroup offline.
I don't think that is misleading at all.
"...bring ... online if a snapshots is created on source database?": IMHO that would directly point to a NO, no matter whether file, filegroup or database is to be brought online.
Having said that:
- I agree the question would benefit from better wording
- thanks for a little extra info on the online/offline statements/functionality
Peter Rijs
BI Consultant, The Netherlands
January 15, 2009 at 8:23 am
Peter Rijs (1/15/2009)
I don't think that is misleading at all."...bring ... online if a snapshots is created on source database?": IMHO that would directly point to a NO, no matter whether file, filegroup or database is to be brought online.
I am probably being pedantic, but as long as no changes have been made to a database, it can be taken offline/brought online regardless of whether a snapshot exists - I've just tried it! Granted that is not a very realistic scenario as there would be little point in taking a snapshot if one knew that a database was not going to change.
What is prevented by a snapshot is a RESTORE of a database.
🙂
January 15, 2009 at 9:20 am
Lempster (1/15/2009)
I am probably being pedantic, but as long as no changes have been made to a database, it can be taken offline/brought online regardless of whether a snapshot exists - I've just tried it! Granted that is not a very realistic scenario as there would be little point in taking a snapshot if one knew that a database was not going to change.
What is prevented by a snapshot is a RESTORE of a database.
🙂
Well bligh me! I think that calls for yet another update to the BOL!
As I have no experience with snapshots, and also have no place to play around with them, I depended solely on the BOL for my answers on this matter.
To get to the bottom: as soon as there is 1 change in the source database, you can not get it back online after taking it offline with the snapshot in place?
Peter Rijs
BI Consultant, The Netherlands
January 16, 2009 at 1:55 am
From my limited testing (done some more since previous post) it seems that a database can be taken offline and brought online even if a snapshot exists and changes have been made since the snapshot was taken. This makes sense if you think about it because snapshots are used for restore purposes and so if you are happy to persist your changes (i.e. you don't want to restore), there should be no reason why you can't then take a database offline and bring it back online again.
However, if a file is taken offline (using ALTER DATABASE MODIFY FILE (NAME = , OFFLINE)), it can only be brought online by restoring the file from a backup...and that's what the existence of a snapshot prevents.
As I said previously, it is not possible to take a FILEGROUP offline explicitly, but because taking a FILE offline makes all the objects in the filegroup that the file belongs to inaccessible, it is essentially the same thing.
So to go back to the original post, I think that answer is correct but the question should have referred to a FILE rather than a FILEGROUP. 😉
On a sidenote, an interesting behaviour of taking a database offline using Management Studio (as opposed to T-SQL) is that if you cancel the offline command, say because you realise you have an open connection to the database, the database will still go offline once the open connection has been closed, i.e. cancelling has no effect.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply