locked
What is the meaning of Database Snapshot? RRS feed

  • Question

  • I just ran this code which is a slight modification of MS sample script provided with the samples downoadable from CodePlax as anadjunct to AdvenrueWorks DBs. It is at C:\Program Files\Microsoft SQL Server\100\Samples\Engine\Administration\DatabaseSnapshot\Scripts\DatabaseSnapshotForAW.sql I replaced AW with my DFB name. What I've got was a copy of my onw DB. Why do I need it? What is the meaning of this script? To create a copy?

    Thanks.

    Thi is the script:

    /*============================================================================
      File:     CreateDatabaseSnapshotForAW.sql
    
      Summary:  Creates a database snapshot of the Analytical sample database.
                This script uses advanced techniques to create a filegroup and file
                name list to use in the creation of the snapshot.  This enables
                the code to function correctly regardless of additional filegroups.
    ============================================================================*/
    
    SET NOCOUNT ON;
    
    -- Change to the master database
    USE [master];
    GO
    
    PRINT 'Drop existing snaphost';
    PRINT '';
    
    -- Drop the snapshot of the Analytical database, if it exists
    IF  EXISTS (SELECT [name] FROM [master].[dbo].[sysdatabases] WHERE [name] = N'AnalyticalSnapshot')
    DROP DATABASE [AnalyticalSnapshot];
    GO
    
    PRINT 'Create the snaphost';
    PRINT '';
    
    -- Create a snapshot of the Analytical database
    DECLARE @DbName sysname;
    DECLARE @Sql nvarchar(max);
    DECLARE @Pos int;
    
    SET @DbName = N'Analytical';
    
    EXECUTE (
    N'DECLARE @Sql NVARCHAR(MAX); 
    
    SET @Sql = N''CREATE DATABASE [' + @DbName + N'Snapshot] ON ''; 
    
    SELECT @Sql = @Sql + CHAR(13) + N''(NAME=['' + [name] + N''],'' 
        + CHAR(13) + N''FILENAME=N'''''' +  
        LEFT([physical_name], 
            CHARINDEX(N''.'', 
                [physical_name], 
                LEN([physical_name]) - 5) - 1) 
        + N''Snapshot'' 
        + SUBSTRING([physical_name], 
            CHARINDEX(N''.'', 
                [physical_name], 
                LEN([physical_name]) - 5), 5) + N''''''),'' 
    FROM [master].[sys].[master_files] 
    WHERE [type] = 0 
        AND [database_id] = DB_ID(N''' + @DbName + N''');
    
    SET @Sql = LEFT(@Sql, LEN(@Sql) - 1) + N'' AS SNAPSHOT OF [' + @DbName + N'];''
    
    PRINT @Sql;
    PRINT '''';
    
    EXECUTE (@Sql);'
    ); 
    GO
    
    PRINT 'Display the snapshot databases'
    PRINT '';
    
    -- Display the snapshot databases
    SELECT N'[' + db1.[name] + N'] is a snapshot of database ['+ db2.[name] + N']' AS 'Snapshot Databases'
    FROM [master].[sys].[databases] db1 
        INNER JOIN [master].[sys].[databases] db2 
        ON db1.[source_database_id] = db2.[database_id];
    GO
    


    AlexB
    Saturday, May 2, 2009 4:12 PM

Answers

  • Hello Alex

    If you want to get a new read only database similar to an existing database, it is  a bit tedious task of recreating the whole database structure and data manually. Hence in order to achieve this functionality easily, SQL Server 2005 onwards a new feature called "Database Snapshots" came into existence.

    Database Snapshots allows you to create a read-only copy of your database that can be used for other purposes, such as reporting, auditing or recovering data. Currently, you can do that by running a backup and restoring it to another database. However, the big advantage you gain by using Database Snapshots instead is the speed at which a snapshot occurs, as well as the ability to create multiple snapshots at different points in time quickly.

    The initial thought is that the snapshot needs to make a complete copy of your database so the data stays static. If this were the case, the snapshot would offer no advantage over a backup and restore.

    Here's what actually happens: When you create the snapshot, a shell of your database file is created. When data is read from the snapshot, it is actually being read from the primary database. As data changes in your primary database, SQL Server then writes out what the data looked like prior to the data change into a sparse file. The snapshot will then read the sparse file for the data that has been changed and continue to read the primary database for data that has not changed.

    If you look at the picture directly below, 90% of the data has not been changed in the primary database, so 90% of the data is still retrieved from here when a query is issued. On the reverse, 10% of the data has changed to satisfy the results for a query that uses the data it would read from the sparse file.

    Refer this link for more information about "Database Snapshots"

    http://msdn.microsoft.com/en-us/library/ms175158.aspx

    Thanks
    Sreekar

    Sunday, May 3, 2009 3:42 PM

All replies

  • Hello Alex

    If you want to get a new read only database similar to an existing database, it is  a bit tedious task of recreating the whole database structure and data manually. Hence in order to achieve this functionality easily, SQL Server 2005 onwards a new feature called "Database Snapshots" came into existence.

    Database Snapshots allows you to create a read-only copy of your database that can be used for other purposes, such as reporting, auditing or recovering data. Currently, you can do that by running a backup and restoring it to another database. However, the big advantage you gain by using Database Snapshots instead is the speed at which a snapshot occurs, as well as the ability to create multiple snapshots at different points in time quickly.

    The initial thought is that the snapshot needs to make a complete copy of your database so the data stays static. If this were the case, the snapshot would offer no advantage over a backup and restore.

    Here's what actually happens: When you create the snapshot, a shell of your database file is created. When data is read from the snapshot, it is actually being read from the primary database. As data changes in your primary database, SQL Server then writes out what the data looked like prior to the data change into a sparse file. The snapshot will then read the sparse file for the data that has been changed and continue to read the primary database for data that has not changed.

    If you look at the picture directly below, 90% of the data has not been changed in the primary database, so 90% of the data is still retrieved from here when a query is issued. On the reverse, 10% of the data has changed to satisfy the results for a query that uses the data it would read from the sparse file.

    Refer this link for more information about "Database Snapshots"

    http://msdn.microsoft.com/en-us/library/ms175158.aspx

    Thanks
    Sreekar

    Sunday, May 3, 2009 3:42 PM
  • Thank you.
    AlexB
    Monday, May 4, 2009 2:03 AM