1 / 20

Database Snapshots

Said Salomon timelord@timelordshangout.com. Database Snapshots. Who am I?.

elysia
Download Presentation

Database Snapshots

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Said Salomon timelord@timelordshangout.com Database Snapshots

  2. Who am I? • I has over 25 year experience as an Information Technology Professional. He has a vast array of abilities in the field in the areas of Network, Desktop Support, DBA, Staff Project Management, Application Software Development, Business Analysis and Quality Assurance. I have Microsoft certifications as a MCTS, MCPS, and MCNPS, and multiple certifications from the Insurance Institute of America. Current Said is a DBA at Unitrin Direct Insurance.

  3. Philadelphia SQL Server User Group (PSSUG)Up coming events Specializing in Database and Business Intelligence topics for Philly and SJ For More Info: http://www.pssug.org

  4. Agenda • What is Database Snapshot • Why use Database Snapshot • What can be done with Database Snapshot • How does a Database Snapshot work • Review basic T-SQL syntax • Demo • Q&A

  5. What is Database Snapshot? • Microsoft – “A database snapshot is a read-only, static view of a database, called the source database. In Microsoft SQL Server 2005 and later versions, you must use Transact-SQL statements for creating, reverting to, and deleting a database snapshot. However, you can use SQL Server Management Studio to view existing database snapshots.”

  6. What is it??? • A database snapshot is a point in time read only snapshot. It looks like a copy of the database but normally takes up less than 10% space.

  7. How Database snapshots work

  8. When to use a Database Snapshot

  9. Restrictions on Database Snapshots • Enterprise only • can not backup a snapshot • The source database cannot be dropped, detached, or restored. • Performance is reduced • Snapshots of the model, master, and tempdb databases are prohibited

  10. Create Database Snapshot • CREATE DATABASE ssAdventureWorks_MySnap ON ( NAME = AdventureWorks_Data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_MySnap.ss' ) AS SNAPSHOT OF AdventureWorks; GO

  11. Select from Database Snapshot • SELECT * FROM [ssAdventureWorks_MySnap].[HumanResources].[Employee]

  12. Restoring from Database Snapshot • RESTORE DATABASE AdventureWorks from DATABASE_SNAPSHOT = 'ssAdventureWorks_MySnap'; GO

  13. Dropping Database Snapshot • DROP DATABASE [ssAdventureWorks_MySnap]

  14. Demo • Demo

  15. Snapshot Creation Time

  16. Multiple Snapshots

  17. Snapshot Response Time

  18. Snapshot Index Creation

  19. Resources • Microsoft http://msdn.microsoft.com/en-us/library/ms175158.aspx • Limts: http://msdn.microsoft.com/en-us/library/ms189940.aspx • Performance: http://sqlcat.com/whitepapers/archive/2008/02/11/database-snapshot-performance-considerations-under-i-o-intensive-workloads.aspx • http://www.timelordshangout.com

  20. Philadelphia SQL Server User Group (PSSUG)Up coming events Specializing in Database and Business Intelligence topics for Philly and SJ For More Info: http://www.pssug.org

More Related