SQL Server: Database Snapshots

• Available only in the Enterprise version.
• Snapshots provide read-only, static views of a database.
• Each snapshot capture the state of all committed data at the point of snapshot creation.
• Snapshots must be on the same server instance as the source database.
• Users can query the database snapshot

Why use database snapshots

• "Freeze data at a point-in-time:" Can be used for reporting data in a particular point in time. (i.e. financial quarter)
• If snapshot available for query in a mirrored server, it deflects read load from the production server.
• Protect agains administrative errors:
• Can be created just before a major change to the database. (i.e. bulk update, schema chenage). Reverting here is much faster than restoring a backup.
• Protect against user errors:
• In case of user errors, database can be restored to the time of snapshot creation.
• Manage a test database: guaratee identical data at each round of testing.

Snapshots use: sparce files copy-on-write operations

How snapshots work

• When you create a snapshot, no data is necessarily copied from the source database.
• The snapshot may be thought as being the source database itself.
• Copy from source to the snapshot occurs only as pages are modified in the source database.
• The snapshot uses one or more sparse files.
• Only as DMLs are issued, the original pages are copied to the snapshot before being modified, in a copy-on-write operation.
• Subsequent updates are not copied to the snapshot.
• Snapshots contain a catalog of changed pages: a bitmap with the list of pages in the source database that have changed since the snapshot creation.
• When a page is copied from the source to the snapshot sparse file, its corresponding bit in the catalog of changed pages is updated from o to 1.
• Growth rate:
• In this way, the size of the snapshot is proportional to the intensity of database writes on different pages.
• If most of the updates are repeated changes to a small subset of pages, the growth rate of the snapshot sparse files will be slow.
• On the other hand, if ALL original pages receive at least one update, the snapshot will grow to the size of the source database.
• Read operations in a snapshot always access the original data pages, either in the source database (those that have not been modified since the snapshot was created) or in the sparse files with original page data (for the pages modified after the snapshot creation).

Creating a snapshot

• Use the CREATE DATABASE ... AS SNAPSHOT OF statement. It has to include:
• One entry for each filename in the source database
• The logical filename has to be equal to the logical filename in the source database
• The physical filename indicates where the sparse file will be created.

CREATE DATABASE AdventureWorks2008R2_dbss ON
    ( name = adventureworks2008r2_Data,
            filename = 'c:\mssqldrive\e\AdventureWorks2008R2_Data.ss' )
            AS snapshot OF adventureworks2008r2;
GO

Checking snapshot files and file size:

• During database snapshot creation, sparse files are created by using the file names in the CREATE DATABASE statement.
• These file names are stored in sys.master_files in the physical_name column.
• The size-on-disk of the snapshot files is listed in the size column (in 8-kb page units).

SELECT name, physical_name, type_desc, state_desc, size
    FROM sys.master_files
    WHERE DB_NAME(database_id) = 'AdventureWorks2008R2_dbss';
name                       physical_name                                type_desc  state_desc size
-------------------------  -------------------------------------------  ---------  ---------- -------
adventureworks2008r2_Data  e:\mssqldrive\e\AdventureWorks2008R2_Data.ss ROWS       ONLINE     23024
FileStreamDocuments2008R2  c:\Program...\DATA\Documents2008R2           FILESTREAM ONLINE     0

• You can also use the fn_virtualfilestats system table-valued function.
• fn_virtualfilestats takes database_id and file_id as parameters.

SELECT database_id, FILE_ID, physical_name
    FROM sys.master_files
    WHERE DB_NAME(database_id) = 'AdventureWorks2008R2_dbss';
database_id file_id physical_name
----------- ------- ------------
11          1       e:\mssqldrive\e\AdventureWorks2008R2_Data.ss
11          65537   c:\Program...\DATA\Documents2008R2

SELECT * FROM fn_virtualfilestats(11,1);
GO
DbId FileId TimeStamp NumberReads BytesRead ... NumberWrites BytesWritten ... BytesOnDisk ...
---- ------ --------- ----------- ---------     ------------ ------------     -----------
11   1      23108022  195         1597440       163          1466368          3407872

Some restrictions:

• You cannot backup, restore or detach a database snapshot
• It must be in the same SQL Server instance as the source database
• Full-text indexes are not supported
• You cannot drop, detach or restore a source database if it has a snapshot.
• You cannot perform structural changes (i.e. add/remove filegroup) to the source database
  

Dropping a snapshot

DROP DATABASE AdventureWorks2008R2_dbss;

Reverting a database from a Snapshot

Restrictions:

• Only a single snapshot can exist against a source database before it can be reverted
• Reverting a database rebuilds the transaction log, so no recovery can be performed aftewards
• Source database and snapshot database must be offline

USE MASTER;
GO
RESTORE DATABASE AdventureWorks2008R2 
 FROM DATABASE_SNAPSHOT = 'AdventureWorks2008R2_dbss';
GO

Managing Snapshot (a) Check the MyDB database files (b) Verify rows on emp table (c) Create snapshot of MyDB database (d) Check created snapshot files (e) Check the size on disk of snapshot files (f) Insert data on emp table in the source database. (g) Query snapshot. Emp table is empty.

(a) Check the MyDB database files
sp_helpdb myDb; 

name          fileid filename                           filegroup size    usage
------------  ------ ------------------------------------ --------- -----   --------
MyDB_primary  1      c:\Program...\data\MyDB_Prm.mdf      PRIMARY   4096 KB data only
MyDB_archlog1 2      c:\Program...\data\MyDB_archlog1.ldf NULL      1024 KB log only
MyDB_FG1_Dat1 3      c:\Program...\data\MyDB_FG1_1.ndf    MyDB_fg1  1024 KB data only
MyDB_FG1_Dat2 4      c:\Program...\data\MyDB_FG1_2.ndf    MyDB_fg1  1024 KB data only
MyDB_FG1_Dat3 5      c:\Program...\data\MyDB_FG1_3.ndf    MyDB_fg1  1024 KB data only
MyDB_FG1_Dat4 6      c:\Program...\data\MyDB_FG1_4.ndf    MyDB_fg1  1024 KB data only
MyDB_FG2_Dat1 7      c:\Program...\data\MyDB_FG2_1.ndf    MyDB_fg2  1024 KB data only

(b) Verify rows on emp table
USE MyDB;
GO
SELECT * 
    FROM emp;
empid name
----- ----

(c) Create snapshot of MyDB database
CREATE DATABASE MyDB_dbss ON
    ( name = MyDB_primary,
                filename = 'c:\mssqldrive\e\MyDB_Prm.ss' ),
    ( name = MyDB_FG1_Dat1,
                filename = 'c:\mssqldrive\e\MyDB_FG1_1.ss' ),
    ( name = MyDB_FG1_Dat2,
                filename = 'c:\mssqldrive\e\MyDB_FG1_2.ss' ),
    ( name = MyDB_FG1_Dat3,
                filename = 'c:\mssqldrive\e\MyDB_FG1_3.ss' ),
    ( name = MyDB_FG1_Dat4,
                filename = 'c:\mssqldrive\e\MyDB_FG1_4.ss' ),
    ( name = MyDB_FG2_Dat1,
                filename = 'c:\mssqldrive\e\MyDB_FG2_1.ss' )
            AS snapshot OF MyDB;
GO
(d) Check created snapshot files
SELECT name, physical_name, type_desc, state_desc, size
    FROM sys.master_files
    WHERE DB_NAME(database_id) = 'MyDB_dbss';
name            physical_name                 type_desc state_desc size
-------------   ----------------------------- --------- ---------  ----
MyDB_primary c:\mssqldrive\e\MyDB_Prm.ss   ROWS      ONLINE     512
MyDB_FG1_Dat1 c:\mssqldrive\e\MyDB_FG1_1.ss ROWS      ONLINE     128
MyDB_FG1_Dat2 c:\mssqldrive\e\MyDB_FG1_2.ss ROWS      ONLINE     128
MyDB_FG1_Dat3 c:\mssqldrive\e\MyDB_FG1_3.ss ROWS      ONLINE     128
MyDB_FG1_Dat4 c:\mssqldrive\e\MyDB_FG1_4.ss ROWS      ONLINE     128
MyDB_FG2_Dat1 c:\mssqldrive\e\MyDB_FG2_1.ss ROWS      ONLINE     128

(e) Check the size on disk of snapshot files
SELECT database_id, FILE_ID, physical_name
    FROM sys.master_files
    WHERE DB_NAME(database_id) = 'MyDB_dbss';
database_id file_id physical_name
----------- ------- -------------
12          1       c:\mssqldrive\e\MyDB_Prm.ss
12          3       c:\mssqldrive\e\MyDB_FG1_1.ss
12          4       c:\mssqldrive\e\MyDB_FG1_2.ss
12          5       c:\mssqldrive\e\MyDB_FG1_3.ss
12          6       c:\mssqldrive\e\MyDB_FG1_4.ss
12          7       c:\mssqldrive\e\MyDB_FG2_1.ss

SELECT * 
    FROM fn_virtualfilestats(12,1);
DbId FileId TimeStamp NumberReads BytesRead ... NumberWrites BytesWritten ... BytesOnDisk ...
---- ------ --------- ----------- ---------     ------------ ------------     -----------
12   1      24067974  50          409600        3            24576            131072

(f) Insert data on emp table in the source database.
USE MyDB;
GO
DECLARE @a INT;
SELECT @a = 1;
WHILE (@a < 100) -- Insert 100 new rows
    BEGIN
    INSERT INTO emp
            VALUES (@a, 'John');
    SELECT @a = @a +1;
    END;
GO
SELECT COUNT(*) num_rows FROM emp;
num_rows
-------
99

(g) Query snapshot. Data is not there. 
USE MyDB_dbss;
GO
SELECT COUNT(*) num_rows  FROM emp;
num_rows
-------
0

Reverting database MyDB from database snapshot Mydb_dbss (a) Check num rows in emp, MyDB database. (a) Emp table is dropped in MyDB (b) Revert the database from MyDB_dbss (c) At the end, Emp table will have been recovered, but without the data inserted/updated after the snapshot had been created.

(a) check table emp;
USE MyDB;
SELECT COUNT(*) num_rows 
    FROM emp;
num_rows
-------
99

(b) drop table emp
USE MyDB;
DROP TABLEemp;
(c) Revert the database snapshot. At the end, the emp table will be available, 
but without the data inserted after the snapshot creation.
USE MASTER;
GO
RESTORE DATABASE mydb 
 FROM database_snapshot = 'mydb_dbss';
USE MyDB;
SELECT COUNT(*) num_rows 
    FROM emp;
num_rows
-------
0

SQL Server: Moving a Database

Three mechanisms for moving a database (within the same server, or between servers):

1. Backup and Restore
2. Copy Database Wizard
3. Detach/Attach

Detaching/Attaching databases

• Data files and transaction logs can be detached and then reattached to the same or different SQL Server instance.
• You may transfer databases across 32-bit and 64-bit environments.

Note that:

• Make sure you have a backup of the database before attaching it to a newer version server.
• Once you attach a SQL Server 2000 or 2005 database to a 2008 SQL Server, you will not be able to attach it back into previous version servers.
• After attaching a database to a new server, you will need to add users from the old server, if they are to continue to have access to the database.

You CANNOT detach a dataabse when:

• The database is replicated AND published. Before detaching you need to disable publishing (run sp_replicationdboption)
• Database contains a database snapshot.
• Database is being mirrored.
• While there are database sessions.
• Database is suspect. Needs to be put into emergency mode.
• Database is a system database.

Moving a database:

(a) Detach the database as follows:

    -- (1) Set database in single_user mode
USE MASTER;
ALTER DATABASE AdventureWorks2008R2
    SET single_user;
GO
    --(2) Check access mode 
SELECT name, user_access_desc
    FROM sys.databases
    WHERE name = 'adventureworks2008r2';
    --(3) Detach database. Set option to skip update statistics for true.
    -- By default, the detach operation retains any out-of-date optimization statistics  
    -- If you want updated optimization statistics to be gathered, set the parameter to 'false'
            .
EXEC sp_detach_db 'AdventureWorks2008R2', 'true';

    --(4) check that the database is no longer listed in the catalog
SELECT name     FROM sys.databases;

(b) MOVE/COPY data and log files to the new location
(c) Re-attach the database as follows:

USE MASTER;
GO
EXEC sp_attach_db @dbname = 'AdventureWorks2008R2' ,
    @filename1 = 'c:\mssqldrive\e\AdventureWorks2008R2_Data.mdf',
    @filename2 = 'c:\mssqldrive\e\AdventureWorks2008R2_Log.ldf';
    -- (2) Check that the database is in the catalog and the new file locations:

EXEC ssp_helpdb Adventureworks2008r2; 
name                 db_size    owner           dbid  created     status            compatibility_level
-------------------- ---------  --------------  ----  ----------  ----------------- --------------------
AdventureWorks2008R2 181.88 MB  Pholder\sqltest 9     May 10 2010 Status=ONLINE,... 100

name                       fileid filename                                    filegroup size      ...  usage
-------------------------  ------ ------------------------------------------  --------- --------       ------
AdventureWorks2008R2_Data  1      g:\sqldata\..\AdventureWorks2008R2_Data.mdf PRIMARY   184192 KB ...  data only
AdventureWorks2008R2_Log   2      g:\sqldata\..\AdventureWorks2008R2_Log.ldf  NULL      2048 KB ...    log only

Attaching a database

• Use sp_attach_db only if the database was previously detached with sp_detach_db, or copied.
• If you need to specify more than 16 files, use instead
• CREATE DATABASE dbname FOR ATTACH or
• CREATE DATABASE dbname FOR_ATTACH_REBUILD_LOG

(more information: here)

SQL Server: Database Configuration Options

Configuration Options

Autogrowth Recovery Model Compatibility Level Auto Shrink Restrict Access

Autogrowth - Property of each database file.

• Control how the file expands (FILEGROWTH clause).
• By default, each file assume values set in the model database.
• Configure automatic growth rate (percent or fixed-size)
• Configure maximum file size. Once reached, database goes into read-only mode.
• Full database generates Event ID 3758.
• Can be checked with sp_dbhelp, sp_dbhelp dbnamed, sp_helpfile filename
• If all files have a maxsize, the database has a maxsize.

Recovery model

Determines how the transaction log is used: how transactions are logged, whether the transaction log is backed up, and what restore options are available.
Three values: { SIMPLE | FULL | BULK-LOGGED }

SIMPLE recovery model

• Minimal logging.
• Minimizes administrative overhead for the Transaction Log (TL).
• Backups may need to be performed more often.
• Here the use of differential backups can help reduce the backup overhead.
• TL truncated after each checkpoint. During a checkpoint the dirty pages are written from bugger memory to the data file and the TL is truncated.
• Transaction logs(TL) cannot be backed up or restored.
• You also cannot restore individual data pages.
• Recovery possible only to the end of the last backup.

When to use simple recovery model?

• test environments, development, read-only production scenarios.

BULK-LOGGED recovery model

• Minimal logging performed for bulk actions: Logs contain only the information that a bulk operation has occurred, but not the data changes incurred with such operation.
• These include:
• SELECT INTO,
• Some INSERT INTO that use SELECT statement (OPENROWSET (bulk...). TABLOCK hint)
• BULK INSERT operations,
• Write actions performed by the BCP program
• UPDATE with WRITE clause
• CREATE, ALTER or DROP INDEX
• Intended as temporary to be used when large bulk operations need to be performed in the database.

Restore possible?

• In Bulk-Logged mode, a backup of the transaction logs also includes all data extents marked as updated by a bulk operation. This guarantees that a restore can be performed, even though not all necessary change information is available in the transaction logs.
• Point-in-time recovery: possible ONLY if no bulk action have occurred since the last full backup.

why use Bulk-logged model?

• Allows for high-performance bulk copy operations.
If many or large bulk operations are to be performed, this configuration minimizes the use of transaction logs (improving performance with less write operations) while still guaranteeing data availability if a restore is needed.

FULL recovery model

• Full logging.
• Logs never truncated.
• Can be used to recover to an arbitrary point in time.

To view the Recovery model:

(a) Use the Object Explorer on SMSS. Select database; right-click and choose database properties.
(b) Use sp_helpdb [dbname]
(c) Query sys.databases

USE MASTER;
GO
SELECT name, recovery_model_desc 
FROM sys.databases;
name    recovery_model_desc
------- --------------
master  SIMPLE
tempdb  SIMPLE
model   FULL
msdb    SIMPLE
TESTSQL FULL
MyDB    FULL

(d) To view the recovery mode of the available backup sets use msdb.dbo.backupset catalog table

i.e Change the recovery model of TESTSQL database to perform bulk copy operation:

-- (1) Backup database
BACKUP DATABASE TESTSQL TO DISK='C:\mssqlbackup\TESTSQL.bak'
GO
Processed 304 pages for database 'TESTSQL', file 'TESTSQL' on file 1.
Processed 3 pages for database 'TESTSQL', file 'TESTSQL' on file 1.
BACKUP DATABASE successfully processed 307 pages in 0.397 seconds (6.022 MB/sec).

-- (2) Change recovery model to BULK_LOGGED
USE MASTER;
GO
SELECT name, recovery_model_desc FROM sys.databases;
name    recovery_model_desc
------- --------------
master  SIMPLE
tempdb  SIMPLE
model   FULL
msdb    SIMPLE
TESTSQL BULK_LOGGED
MyDB    FULL

--(3) Perform BULK operation

--(4) Change recovery model back to FULL
ALTER DATABASE TESTSQL SET recovery FULL

--(5) Backup database to include new data
BACKUP DATABASE TESTSQL TO DISK='C:\mssqlbackup\testsql.bak'
GO

Compatibility Level.

• Database-specific.
• Provides only partial backward compatibility.
• Default 100.
• ALTER DATABASE dbname SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }
• ALTER DATABASE Replaces sp_dbcmptlevel procedure
• Do not change compatibility level while users are connected to the database.
• (changes may affect query plan compilation)
• Set database to single-user access mode before altering compatibility level.
• i.e. ALTER DATABASE SET SINGLE_USER

Further reading and examples:

Books online

Database Size: Auto Shrink

• Available, but perhaps not a good idea to use.
• Its disk and processor intensive: file reorganization.
• Just don't: Auto_shrink will increase Index fragmentation enormously.
• If you need to shrink, do it manually (DBCC SHRINKFILE and DBCC SHRINKDATABASE).
• When AUTO_SHRINK is on, both data and log files can be automatically shrunk. Files are shrunk when more than 25% of the file contains free space. Files are then shrunk to a size where 25% of the file is unused.
• Check a good discussion here.

-- To set AUTO_SHRINK on|off: 
ALTER DATABASE SET AUTO_SHRINK { ON | OFF }

To check the status of AUTO_SHRINK:
SELECT name, is_auto_shrink_on  FROM sys.databases;
go

name       is_auto_shrink_on
master     0
tempdb     0
model      0
msdb       0
TESTSQL    0

alter database TESTSQL
   set auto_shrink ON;
go

select name, is_auto_shrink_on
from sys.databases;
go

name       is_auto_shrink_on
master     0
tempdb     0
model      0
msdb       0
TESTSQL    1

Restrict Access.

Specify which users may access the database.
SINGLE_USER

• Used for maintenance actions.
• Any single user may connect, but only one user is accepted at a time.
• To switch to single_user mode when other sessions are active, you need to disconnect them first.

-- The command below allow 6 minutes for current sessions to complete. 
-- After this any transaction will be rolled back: 

 ALTER DATABASE Sales
   SET SIGLE_USER
   WITH ROLLBACK AFTER 360;

MULTI_USER

• Default and normal state for production.
• Allows multiple user access.

RESTRICTED

• Only members of the db_owner, dbcreator, or sysadmin roles.