Snapshot Database Creation

CREATE DATABASE Test_Snapshot ON
(
NAME = N'Test_Datafile',
FILENAME = N'D:\DB_Snapshots\test_snapshot.ss'
)
AS SNAPSHOT OF Test;

1.Test_Datafile' is the logical name for Test(primary DB) database, which is same logical name for snapshot database.
2. Only physical location is different
3.Snashot Database

• is a read-only static view of a database.
• This option is available only Enterprise Edition.
• Can create in same Instance only.It is not possible with different Instanaces
• Can not take backup of the Snapshot Database.

4.To drop the Snapshot Database
DROP Database [Test_Snapshot ]

5.Hourly based Snapshot Database:

• Create Sql Server Agent job
• Configure the Agent job as first drop snapshot database
• Re-create Snapshot database
• Schedule the agent job every hour

Tran Log/Differential/File group Backups can not take for Master Database

Only full database backups of master database can be performed. Transactional log, differential or filegroup backups of master are not allowed. Thus if you create a Database Maintenance Plan for all the system databases or if you select the master database and you select the Back up the transaction log as part of the maintenance plan option, the backup transaction log step for the master database will fail with the error message.