April 5, 2012 at 9:04 am
Can we detach a database snapshot?
The interface gives me the option to detach but when I click on detach, it gives an error saying "Cannot detach database snapshot".
April 5, 2012 at 9:15 am
sunny.tjk (4/5/2012)
Can we detach a database snapshot?The interface gives me the option to detach but when I click on detach, it gives an error saying "Cannot detach database snapshot".
A database snapshot is a sort of "differential view" of the database it was created from.
It can't live without the originating database, hence it can't be detached.
More on database snapshots: http://msdn.microsoft.com/en-us/library/ms175158.aspx
Hope this helps
Gianluca
-- Gianluca Sartori
April 5, 2012 at 9:15 am
No, you cannot detach database snapshots.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 5, 2012 at 9:30 am
Gianluca Sartori (4/5/2012)
sunny.tjk (4/5/2012)
Can we detach a database snapshot?The interface gives me the option to detach but when I click on detach, it gives an error saying "Cannot detach database snapshot".
A database snapshot is a sort of "differential view" of the database it was created from.
It can't live without the originating database, hence it can't be detached.
More on database snapshots: http://msdn.microsoft.com/en-us/library/ms175158.aspx
Hope this helps
Gianluca
Thanks Gianluca.
I'm also trying to figure out if I could schedule a job to generate snapshot databases at regular intervals of a user database .
April 5, 2012 at 9:34 am
No backup or restore of snapshots either. A limited feature, IMHO, useful for quick looks at data, or restores of the main db after some upgrade.
Database snapshots: http://msdn.microsoft.com/en-us/library/ms175158.aspx, good list of limitations in there.
April 5, 2012 at 9:34 am
You could, just watch the IO impact.
Lots of database snapshots can have a serious impact on data modifications in the source DB. By lots, I don't mean hundreds, 5 or 10 could have a noticable effect depending how good your IO subsystem is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 5, 2012 at 9:42 am
GilaMonster (4/5/2012)
You could, just watch the IO impact.Lots of database snapshots can have a serious impact on data modifications in the source DB. By lots, I don't mean hundreds, 5 or 10 could have a noticable effect depending how good your IO subsystem is.
Gail, do you mean that snapshot databases aren't good for OLTP environment where INSERTS/UPDATES/DELETES are quite often? If yes, can snapshots be used for reporting environments?
April 5, 2012 at 9:47 am
sunny.tjk (4/5/2012)
GilaMonster (4/5/2012)
You could, just watch the IO impact.Lots of database snapshots can have a serious impact on data modifications in the source DB. By lots, I don't mean hundreds, 5 or 10 could have a noticable effect depending how good your IO subsystem is.
Gail, do you mean that snapshot databases aren't good for OLTP environment where INSERTS/UPDATES/DELETES are quite often? If yes, can snapshots be used for reporting environments?
I know of several companies that use snapshot databases on mirrored databases for reporting purposes. They limit the number snapshot databases to one or two depending on reporting requirements.
April 5, 2012 at 9:56 am
sunny.tjk (4/5/2012)
GilaMonster (4/5/2012)
You could, just watch the IO impact.Lots of database snapshots can have a serious impact on data modifications in the source DB. By lots, I don't mean hundreds, 5 or 10 could have a noticable effect depending how good your IO subsystem is.
Gail, do you mean that snapshot databases aren't good for OLTP environment where INSERTS/UPDATES/DELETES are quite often?
Yes. They can slow down data modifications in the source DB. One or two should be OK, depends on the IO subsystem how many it can handle.
http://www.sqlservercentral.com/articles/Performance+Tuning/64080/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 5, 2012 at 10:06 am
Thank you.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply