May 3, 2010 at 10:28 am
We created a snapshot on drive E:, but have now found that we are running out of space. We need to keep the snapshot because we are running a large number of reports against it and they all need to run against the same data.
Is it possible to move this snapshot to another drive on the same server?
Alternatively, is there a mechanism for backing up the data contained in the snapshot (and the source database) and then restoring this to a single database?
regards,
Ian
May 3, 2010 at 10:54 am
No and no.
Snapshots can't be moved after they are created. They can't be detached (the one way to move a database) and, since they're readonly, alter database (the other way to move a database) fails.
A snapshot can't be backed up at all. You could, I guess, export the data via the snapshot (to get data at the time it was created) and then create a database and import all the data back.
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
May 4, 2010 at 2:18 am
Suspected as much.
Thanks very much for reply Gail.
May 4, 2010 at 2:29 am
Just an additional comment. I do not personally recommend the use of snapshots for long-term reporting, where a single snapshot needs to exist for a long time. Because they cannot be backed up and because of the things that can require it to be dropped/invalidate it, the risk of the snapshot becoming unavailable is just too high.
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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply