SQL Server Questions and Answers – Database Snapshots

This set of SQL Server Multiple Choice Questions & Answers (MCQs) focuses on “Database Snapshots”.

1. Which of the syntax is used for creating database snapshot in SQL Server?
a)

CREATE Snapshot database_snapshot_name
    ON
    (
        NAME = logical_file_name,
        FILENAME = 'os_file_name'
    )

b)

CREATE DATABASESNAPSHOT database_snapshot_name
    ON
    (
        NAME = logical_file_name,
        FILENAME = 'os_file_name'
    )

c)

CREATE DATABASE database_snapshot_name
    ON
    (
        NAME = logical_file_name,
        FILENAME = 'os_file_name'
    )

d) None of the mentioned
View Answer

2. Point out the correct statement.
a) Snapshots can be used in conjunction with database mirroring for reporting purposes
b) A snapshot is write-only, static view of a database
c) A snapshot is read-only, dynamic view of a database
d) None of the mentioned
View Answer

3. Which of the following scenario prefers not to use database snapshots?
a) Database Snapshots should be seen as high availability option
b) Database Snapshots should not be seen as an alternative to regular backups of your data
c) Snapshots can be created very quickly
d) All of the mentioned
View Answer

4. Which of the following code will DROP an existing snapshot before creating new?
a)

IF NOT EXISTS(
    SELECT 
        * 
    FROM 
        sys.databases 
    WHERE 
        name = 'DatabaseA_Snapshot' 
    AND source_database_id IS NOT NULL
    )
BEGIN
    CREATE DATABASE [DatabaseA_Snapshot] 
    ON (NAME=DatabaseA, FileName='<whatever>') 
    AS SNAPSHOT OF [DatabaseA]
END

b)

IF EXISTS(
    SELECT 
        * 
    FROM 
        sys.databases 
    WHERE 
        name = 'DatabaseA_Snapshot' 
    AND source_database_id IS NOT NULL
    )
BEGIN
    CREATE DATABASE [DatabaseA_Snapshot] 
    ON (NAME=DatabaseA, FileName='<whatever>') 
    AS SNAPSHOT OF [DatabaseA]
END

c)

IF NOT EXISTS(
    SELECT 
        * 
    FROM 
        system.databases 
    WHERE 
        name = 'DatabaseA_Snapshot' 
    AND source_database_id IS NOT NULL
    )
BEGIN
    CREATE DATABASE [DatabaseA_Snapshot] 
    ON (NAME=DatabaseA, FileName='<whatever>') 
    AS SNAPSHOT OF [DatabaseA]
END

d) None of the mentioned
View Answer

5. Point out the wrong statement.
a) You cannot used SQL Server Management Studio for creating a database snapshots
b) Snapshots have to be created using T-SQL
c) All recovery models support database snapshot
d) A database can have one and only one snapshot
View Answer

6. Which of the following code will throw error?
a)

CREATE DATABASE AWDB_Snapshot_20080522 ON (
NAME = master,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AWDB_Snapshot.ss')
AS SNAPSHOT OF master;

b)

CREATE DATABASE AWDB_Snapshot_2008 ON (
NAME = AdventureWorks_Data,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AWDB_Snapshot.ss')
AS SNAPSHOT OF AdventureWorks;

c)

CREATE DATABASE AWDB_Snapshot_20080 ON (
NAME = AdventureWorks_Data,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AWDB_Snapshot.ss')
AS SNAPSHOT OF AdventureWorks;

d)

CREATE DATABASE Snapshot_20080522 ON (
NAME = Adventure,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AWDB_Snapshot.ss')
AS SNAPSHOT OF Adventure;

7. To revert the database, which of the following Transact-SQL statement is used?
a) RESTORE Snapshot <database_name> FROM DATABASE_SNAPSHOT = <database_snapshot_name>
b) RESTORE DATABASE <database_name> FROM DATABASE_SNAPSHOT = <database_snapshot_name>
c) REST DATABASE <database_name> FROM DATABASE_SNAPSHOT = <database_snapshot_name>
d) None of the mentioned
View Answer

8. Which of the following message comes when you try to update or delete the data snapshot?
a) Row Deletion
b) The row was committed
c) Row has been updated
d) Failed to update the database
View Answer

9. Syntax for creating database snapshot in SQL Server is _______________
a) DROP DATABASE [snapshottest];
b) DELETE DATABASE [snapshottest];
c) DROP Snapshot [snapshottest];
d) None of the mentioned
View Answer

10. What should be done to check to see if data still exists in the Snapshot?
a) Run Delete on Database Snapshot
b) Run Update on Database Snapshot
c) Run Select on Database Snapshot
d) Run Insert on Database Snapshot
View Answer

Sanfoundry Global Education & Learning Series – SQL Server.

To practice all areas of SQL Server, here is complete set of 1000+ Multiple Choice Questions and Answers.