SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Snapshots


Database Snapshots

Author
Message
bharti.m
bharti.m
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 67
Comments posted to this topic are about the item Database Snapshots
Robbert Hof
Robbert Hof
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1358 Visits: 834
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 Sad



Peter Rijs
Peter Rijs
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1010 Visits: 602
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 Sad


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
Robbert Hof
Robbert Hof
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1358 Visits: 834
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 Wink



Lempster
Lempster
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3216 Visits: 1657
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.
Peter Rijs
Peter Rijs
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1010 Visits: 602
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
Lempster
Lempster
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3216 Visits: 1657
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.

Smile
Peter Rijs
Peter Rijs
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1010 Visits: 602
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.

Smile

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
Lempster
Lempster
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3216 Visits: 1657
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. Wink

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search