Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Taking the snapshots of a database

Posted on 2006-11-15
3
Medium Priority
?
228 Views
Last Modified: 2006-12-04
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
Comment
Question by:k_rasuri
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 17954092
ARe u talking abt snap shot replication.

if so check this

Snapshot Agent, snapshot replication in BOL
0
 
LVL 29

Accepted Solution

by:
Nightman earned 900 total points
ID: 17954105
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
 
LVL 29

Expert Comment

by:Nightman
ID: 17954123
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question