Introduction to Database Snapshots in SQL Server 2005

Posted on by
Reply

Database Snapshots is a new feature introduced in SQL Server 2005. It provides a mechanism that enables us to save an exact read only copy of a database when the snapshot was taken. It would be highly beneficial that a database snapshot is taken when certain bulk changes to the table data is made or you need to maintain an exact copy of your database as it was at the end of the day, each day.

Some points to be noted while working with Database Snapshots.

Database snapshot can be created using T-SQL script only, it cannot be implemented using the Management Studio.

  • Database snapshot of a database should exist on the same SQL Instance as the Source Database. Why this is so would be beyond the point of discussion for this article, so I would explain in another blog soon.
  • Database snapshots cannot be backed up. (But in the Object Explorer you will see that the menu options are available to backup)
  • Database snapshot cannot be updated or modified (That is why it is referred as read only Copy)
  • It is available only in Enterprise Edition of SQL Server 2005.

Script to create the Source Database

USE [master]
GO

CREATE DATABASE [Snapshot_Source] ON PRIMARY

( NAME = N'Snapshot_Source_Data', FILENAME =
N'D:\Snapshot_Source_Data.mdf' , SIZE = 4096KB,

MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N'Snapshot_Source_log',
FILENAME = N'D:\Snapshot_Source_Log.ldf' ,

SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

Creating table called DemoTable using the following script

CREATE TABLE [Snapshot_Source].[dbo].[DemoTable](

[Name] [varchar](50) NULL,

[Age] [int] NULL )

ON [PRIMARY]

Inserting records in the DemoTable

INSERT INTO [Snapshot_Source].[dbo].[DemoTable]

VALUES ( 'A' , 30 )

INSERT INTO [Snapshot_Source].[dbo].[DemoTable]

VALUES ( 'B' , 35 )

INSERT INTO [Snapshot_Source].[dbo].[DemoTable]

VALUES ( 'C' , 37 )

INSERT INTO [Snapshot_Source].[dbo].[DemoTable]

VALUES ( 'D' , 40 )

Run the following script in the Query Editor

SELECT * FROM [Snapshot_Source].[dbo].[DemoTable]

CREATE DATABASE SNAPSHOT_01OCT2009_6AM ON PRIMARY

( NAME = N'Snapshot_Source_Data',

FILENAME = N'D:\SNAPSHOT_01OCT2009_6AM.ss' )

AS SNAPSHOT OF Snapshot_Source;

GO

Modifying the Data in the Source Database

Let’s modify the data in the source database by the following update statements.

UPDATE [Snapshot_Source].[dbo].[DemoTable]

SET [AGE] = 50 WHERE [Name] = 'A'

UPDATE [Snapshot_Source].[dbo].[DemoTable]

SET [AGE] = 55 WHERE [Name] = 'B'

Verifying the data at the source database and also from the snapshot by running the following script in the Query Editor.

SELECT * FROM [SNAPSHOT_01OCT2009_6AM].[dbo].[DemoTable]

SELECT * FROM [Snapshot_Source].[dbo].[DemoTable]

You can use a database snapshot in situations which needs a continuous read only copy of the database at a point of time. For example, you have a reporting application which requires data to be available and it is ok to have the data as it was at the end of day yesterday. A scheduled database snapshot of your transaction based Database which runs after the working hours each day would be a good solution.

Dropping a Database Snapshot

Dropping a database snapshot is same as dropping a database, using the Drop Database command.

The following command would delete the database snapshot that was used in this example.

DROP DATABASE SNAPSHOT_01OCT2009_6AM

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru to know when there is new content.

–Bru Medishetty

Downgrading SQL Server Enterprise Edition to Standard Edition

Posted on by
8

In this blog, we shall look at something that may not be done as frequently as you would want to. It is Downgrading a SQL Server 2005 from an Enterprise Edition to Standard Edition.

First you need to know why such an exercise is carried out any way. The reason would be, due to oversight, an Enterprise Edition was installed which in the first place must have been a Standard Edition, or the decision makers decide to go with Standard Edition of SQL Server due to the cost difference in the both the Editions.

Whatever the reason behind it, as a DBA you are asked to plan and carry out the exercise with a little or no downtime.

Here are the steps I identified to perform the Downgrade to SQL Standard on the same server which has the SQL Enterprise Edition. This method does not need any additional resources whatsoever.

Before beginning the process, make a note of the current Installation details such as

  • Instance Name ( Default or Named Instance)
  • Other Server Options such as AWE, Authentication, Login Auditing, Database default Locations etc..
  • Network Protocols enabled and the TCP/IP Port No etc
  • The components installed, such as Full Text Search, Reporting Services, Analysis Services or Integration Services if any.

Steps to Perform Downgrade the SQL Server Enterprise to Standard

  • Ensure the Application and the Databases are not in Use.
  • Check for any Jobs running on the Server and if none are active, disable all the jobs, and if there are a number of Jobs on that Server, it’s a good idea stop the SQL Server Agent.
  • Perform a Backup of all the Databases, use a script to backup all the databases including the System Databases.
  • Make a copy of the Data and Log Files of the System Databases. (Needed to Overwrite existing MSDB and Model Database Files after Installation of Std Edition)
  • Run the Script that creates all Logins on the SQL Instance and store that script as Create Logins Script. Follow the Link provided at the end of the Blog to know the exact procedure.
  • Detach the User Databases. Make ready a Script for detaching all User Databases in order to reduce the downtime.
  • Uninstall the SQL Enterprise instance, restart the operating system.
  • Install SQL Server Standard Edition and other SQL Server components.
  • Apply the latest service pack for the Standard Edition. (A restart is required.)
  • Check for the Configuration settings such as AWE, Default Data and Log File Location, TCP/IP Port No, etc if needed change them to the earlier noted values.
  • Stop the SQL Server and make a physical copy of the Master, MSDB, Model and System Restore Databases (Copy of the Brand New System Databases for Standard Edition, if incase we need it, we can use these to bring back to the point of installation of Standard Edition.)
  • Restart the SQL Server and attach the User Databases using the Attach User databases Script.
  • After Successful execution of the Script, check for the databases list in SQL Management Studio. Should see all the User databases.
  • Stop the SQL Server and when the SQL Server is stopped, overwrite the system Databases MSDB and Model and Start the SQL Server. After the restart, there must be some Jobs available under the SQL Agent.
  • Check for any Logins available after overwriting the system databases, (SQL Server logins will be not available), run Create Logins Script in the Master Database.

Please find the procedure described in the following TechNet Article for scripting Logins from one Server to another Server. http://support.microsoft.com/kb/918992/

This method would ensure that you need no additional resources, on the other side if something fails, you don’t have working SQL System to fall back onto. As always having a separate server where you can perform this would ensure that you have a good and working SQL Server in case of failure.

So it’s a Best Practice to have a plan to quickly revert to your old Installation or bring up the old SQL Server with Enterprise Edition.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty