Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Aug 3, 2005

SQL Server 2005 - Automating Creation of Database Snapshots

By Muthusamy Anantha Kumar aka The MAK

SQL Server 2005 is packed with many new features. One of the new features that I would like to discuss in this article is Database Snapshots, which are read only static views of a database. SQL Server 2005 allows you to create multiple snapshots on a database. In this article, I would like to demonstrate the creation of database snapshots and automating the creation of database snapshots.

First let's simulate the whole snapshot creation process.

Step 1

Copy and paste the query below into the Microsoft SQL Server Management Studio's Query window and execute it. This statement creates the database MyDB.

Create Database MyDB on Primary
(Name ='Mydb_Data',
FileName= 'C:\data\MyDB_Data.Mdf',
Size=100MB,
MaxSize=200MB,
FILEGROWTH=10%)
Log on
(Name = 'Mydb_Log',
FileName= 'C:\data\MyDB_Log.Ldf',
Size=30MB,
MaxSize=50MB,
FILEGROWTH=10%);

Step 2

Let us create a table and insert some rows.

Copy and paste the query below into the Microsoft SQL Server Management Studio's Query window and execute it. This statement creates the table Hubble_Galaxies and inserts 5 rows to the table.

Use MyDB
go
Create table Hubble_Galaxies (Id int, name varchar(100))
go
insert into Hubble_Galaxies values (1,'MilkyWay')
insert into Hubble_Galaxies values (2,'Spiral Galacy NGC 1300')
insert into Hubble_Galaxies values (3,'Whirpool Galacy M51')
insert into Hubble_Galaxies values (4,'Galaxy NGC 1427A')
insert into Hubble_Galaxies values (5,'NGC 3370')
go

Step 3

Now let's create a snapshot for the database MyDB.

Copy and paste the query below into the Microsoft SQL Server Management Studio's Query window and execute it. This statement creates the snapshot MyDB_Snapshot1 for the database MyDB.

use master
go
Create Database MyDB_Snapshot1  on
(Name ='Mydb_Data',
FileName= 'C:\data\MyDB_Data.SS1')
AS SNAPSHOT of MyDB;
go

This would create a file C:\data\MyDB_Data.SS1 as shown in Figure 1.0


Fig 1.0

Step 4

Now let's delete some rows from the original database.

Copy and paste the query below into the Microsoft SQL Server Management Studio's Query window and execute it. This statement deletes two rows from the MyDB database.

use MyDB
go
delete from Hubble_Galaxies where id in (3,5)
go


MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date