[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

Taking the snapshots of a database

I have a  database called 'projectserver'
I took the backup of this database and restored to a new database 'projectsnap' on the same server.
Now i want to use this 'projectsnap' database as a reporting database for the actual database
To accomplish this i want to take snapshots of the 'projectserver' and load the data at that particular instance into 'projectsnap'
The main objective of this operation is that i can go back to any particular time and see how the data looked like at that instance of time in 'projectsnap'
     I searched the net but didnt find proper information as how to do this...please help


thanks,
kishore
0
k_rasuri
Asked:
k_rasuri
  • 2
1 Solution
 
Gautham JanardhanCommented:
ARe u talking abt snap shot replication.

if so check this

Snapshot Agent, snapshot replication in BOL
0
 
NightmanCTOCommented:
Assuming you want to take a proper snapshot using SQL 2005

USE AdventureWorks
go

USE master
go

CREATE DATABASE AdventureWorksSnap
ON
( NAME = N'AdventureWorks_Data',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorksSnap_Data.mdfss')
-- , SIZE = 167872KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )
AS SNAPSHOT OF AdventureWorks  <<<< ----------
go

USE AdventureWorksSnap
go

SELECT * FROM person.contact
go

taken verbatim from http://www.sqlskills.com/blogs/kimberly/SearchView.aspx?q=snapshot
0
 
NightmanCTOCommented:
Have a look at
'How Database Snapshots Work' (http://msdn2.microsoft.com/en-us/library/ms187054.aspx)
and
'How to: Create a Database Snapshot (Transact-SQL)' (http://msdn2.microsoft.com/en-us/library/ms175876.aspx)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now