Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database Snapshots Expand / Collapse
Author
Message
Posted Monday, January 12, 2009 9:54 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, February 8, 2013 12:35 AM
Points: 285, Visits: 65
Comments posted to this topic are about the item Database Snapshots
Post #635147
Posted Tuesday, January 13, 2009 12:19 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 2, 2014 6:41 AM
Points: 1,287, Visits: 784
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



Post #635169
Posted Tuesday, January 13, 2009 6:35 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 26, 2014 6:16 AM
Points: 859, Visits: 594
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


Peter Rijs
BI Consultant, The Netherlands
MCITP BI Dev & DB Dev (SQL 2008 & 2005)
Post #635333
Posted Tuesday, January 13, 2009 7:02 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 2, 2014 6:41 AM
Points: 1,287, Visits: 784
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 ;)




Post #635366
Posted Thursday, January 15, 2009 7:35 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:13 AM
Points: 2,036, Visits: 1,375
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.
Post #637118
Posted Thursday, January 15, 2009 8:13 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 26, 2014 6:16 AM
Points: 859, Visits: 594
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
MCITP BI Dev & DB Dev (SQL 2008 & 2005)
Post #637190
Posted Thursday, January 15, 2009 8:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:13 AM
Points: 2,036, Visits: 1,375
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.

:)
Post #637207
Posted Thursday, January 15, 2009 9:20 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, June 26, 2014 6:16 AM
Points: 859, Visits: 594
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
MCITP BI Dev & DB Dev (SQL 2008 & 2005)
Post #637287
Posted Friday, January 16, 2009 1:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:13 AM
Points: 2,036, Visits: 1,375
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.
Post #637798
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse