Featured Database Articles

SQL Server 2005 - Automating Creation of Database Snapshots - Page 2

Step 5

Now let's query the table Hubble_Galaxies from the original database MyDB and from the SnapShot view MyDB_Snapshot1.

Copy and paste the query below into the Microsoft SQL Server Management Studio's Query window and execute it. This statement displays all of the rows from the table Hubble_Galaxies from the database MyDB. [Refer Fig 1.1]

use MyDB
go
Select * from Hubble_Galaxies
go

Fig 1.1

Copy and paste the query below into the Microsoft SQL Server Management Studio's Query window and execute it. This statement displays all of the rows from the table Hubble_Galaxies, from the snapshot MyDB_Snapshot1 (the snapshot view of the database MyDB.) [Refer Fig 1.2]

When this scheduled jobs run daily, it deletes the previous weeks database snapshot and creates a new database snapshot. [Refer Fig 1.7]

Fig 1.7

The naming convention of the database snapshot is MyDB_Snapshot_Dayoftheweek

The following SQL statement will list all of the database snapshots available on a SQL Server instance. [Refer Fig 1.8]

select * fromsys.databaseswhere source_database_id is not null

Fig 1.8

Note: By changing the naming convention and the schedule of this process, you can convert this daily database snapshot process to a weekly or monthly database snapshot process.

Conclusion

This article demonstrated the creation of a database snapshot and the automating of the creation of database snapshots. By creating a database snapshot everyday, it would be more convenient to go back to a particular day to get data, or compare or to monitor the status of the data. The database can be reverted to a database snapshot using the RESTORE command.