March 2, 2012 at 2:46 am
Hi,
Please clarify below doubts.
1.How do i restore db back to a specific time frame from dbsnapshot?
(For example i have taken a DBSnapshot @ 8:00 ,second
@8:15 ,third @8:30)..How will I take back my DB to 8:15?
2.I have created dbsnapshot for mirrored database.
is dbsnapshot always sync with mirrored db ?
Thanks in advance.
March 2, 2012 at 2:58 am
1.How do i restore db back to a specific time frame from dbsnapshot?
(For example i have taken a DBSnapshot @ 8:00 ,second
@8:15 ,third @8:30)..How will I take back my DB to 8:15?
From http://msdn.microsoft.com/en-us/library/ms189281(v=sql.100).aspx:
To revert a database to a database snapshot
1.Identify the database snapshot to which you want to revert the database
2.Drop any other database snapshots.
3.Perform the revert operation.
A revert operation requires RESTORE DATABASE permissions on the source database. To revert the database, use the following Transact-SQL statement:
RESTORE DATABASE <database_name> FROM DATABASE_SNAPSHOT =<database_snapshot_name>
Where <database_name> is the source database and <database_snapshot_name> is the name of the snapshot to which you want to revert the database. Notice that in this statement, you must specify a snapshot name rather than a backup device.
2.I have created dbsnapshot for mirrored database.
is dbsnapshot always sync with mirrored db ?
A DB snapshot is a read only point in time static view of the database - it will only contain data at the time the snapshot was created and not reflect any modifications that occur after the snapshot was created.
March 2, 2012 at 3:01 am
Thanks for the clarification winash 🙂
March 5, 2012 at 6:15 am
Snapshots are a really useful feature, but there are some things you should be aware of:
They reduce the write I/O performance of your database as changed database pages get copied into the snapshot file. The more snapshots you have, the bigger the overhead.
They can cause a large amount of file system fragmentation. I'd advise storing them on a dedicated volume and they are probably a good candidate for solid state storage.
Snapshots have some limitations e.g. They don't support full-text indexes or filestream filegroups.
Dropping database snapshots (or any database) causes the procedure cache to be flushed. This can cause performance problems as queries need to be recompiled.
If you use asynchronous DB mirroring with snapshots created against the mirror, the performance overhead might be less of an issue.
DBA Dash - Free, open source monitoring for SQL Server
March 6, 2012 at 3:27 am
Thanks it solved my problem too
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply