Check out our new blog post on Internals of Database Snapshot Part-2. This blog is the joint effort of Suhas and me.
Check out our new blog post on Internals of Database Snapshot. This blog is the joint effort of Suhas and me.
http://blogs.msdn.com/b/suhde/archive/2011/07/31/internals-of-database-snapshot.aspx
It is always on ease having a backup and recovery strategies in place but few times it also important to recover a database to appoint which doesn’t require a full database restore or in shot no hard efforts. While working with one of my previous organization I was asked to do something which actually made me interested in using Database snapshot feature.
Scenario: A New modules was designed and developed for finance. Once the Module will get deployed in production excel data files need to be uploaded. At the backed few SP will execute which will do processing of data and finance related data will be updated /flag (inactive).
I have been asked to design a recovery strategy for the Database which recovers the database before the time of deployment and without downtime. I thought on all recovery models and at last decided to user database snapshot.
Database snapshot give me a flexibility to revert back to the time when the snapshot was created. As the snapshot database is read-only we can rum comparison between OLPT database and database snapshot.
Combing back to the original question, let’s say Deployment has to start at 1:00 AM, will stop the customer /user access to the application and database so that no transaction should take place. Once the application and database is inaccessible to the customer run select * from sys.sysprocesses to check what all open transaction are in place other then background processes.
Create the database Snapshot.
|
CREATE ( NAME = SPri1_dat, ( NAME = SPri2_dat, ( NAME = SGrp1Fi1_dat, ( NAME = SGrp1Fi2_dat, ( NAME = SGrp2Fi1_dat, ( NAME = SGrp2Fi2_dat, AS GO |
In case you think after the deployment there is some issue with the module or data and you need to revert back. Restore the snapshot which will revert the changes to the database till the point of snapshot.
Here is an example
Revert the database to a database snapshot (No connection should exists to the source database to apply the snapshot.)
Now to verify that database is reverted successfully, check the records
While troubleshooting the replication issue I came across the very common error
–Msg 9002, Level 17, State 4, Line 1
–The transaction log for database ” is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
I did the same as suggested by the error, checking the sys.database for the reason why the error log is full.
select log_reuse_wait ,log_reuse_wait_desc from sys.databases where name =’Databases Name’
As a first step of troubleshooting I checked the logfile configuration to see if log file size is ristricted ,Auto Growth is enabled (is auto growth in percent or MB) and the initial size of the log file.
The Log file initial size is set to 3 MB (Default) and as we can see the log file is enabled for autogrowth with Ristricted file growth of 10 MB.
So, the maximum log file can grow upto 10 MB.
The question come’s in one’s mind is “How can we check the used percentage of the log file?”
To check the Log file used Percentage
DBCC SQLPERF(logspace)
| Database Name | Log Size (MB) | Log Space Used (%) | Status |
| Xzcvxz | 9.929688 | 99.76396 | 0 |
As u can see the logfile has been used till the last extend and there is no more space to grow.
Now the question is how to determine SQL Server database transaction log usage?
To determine the Log Usage there is an undocumented command
(DBCC LOGINFO(‘Database name’)
This will give you information about your virtual logs inside your transaction log. The primary thing to look at here is the Status column. Since this file is written sequentially and then looped back to the beginning, you want to take a look at where the value of “2″ is in the output. This will tell you what portions of the log are in use (2) and which are not in use Status = 0. Another thing to keep an eye on is the FSeqNo column. This is the virtual log sequence number and the latest is the last log. If you keep running this command as you are issuing transactions you will see these numbers keep changing.
| FileId | FileSize | StartOffset | FSeqNo | Status | Parity | CreateLSN |
| 2 | 253952 | 8192 | 2275 | 2 | 128 | 0 |
| 2 | 253952 | 262144 | 2276 | 2 | 128 | 0 |
| 2 | 253952 | 516096 | 2273 | 2 | 64 | 0 |
| 2 | 278528 | 770048 | 2274 | 2 | 64 | 0 |
| 2 | 262144 | 1048576 | 2277 | 2 | 64 | 1571000000037600000 |
| 2 | 262144 | 1310720 | 2278 | 2 | 64 | 1572000000050200000 |
| 2 | 262144 | 1572864 | 2279 | 2 | 64 | 1573000000040000000 |
| 2 | 262144 | 1835008 | 2280 | 2 | 64 | 1574000000050200000 |
| 2 | 262144 | 2097152 | 2281 | 2 | 64 | 1575000000050000000 |
Now as we know the issue however the confusion is how to resolve the same.
But wait
(1)What if there are transaction still running against the database.
To check the same run
DBCC Opentran() against the database.
Transaction information for database ‘xzcvxz’.
Oldest active transaction:
SPID (server process ID): 57
UID (user ID) : -1
Name : SELECT INTO
LSN : (3395:100:1)
Start time : Aug 29 2010 2:35:32:837AM
SID : 0×01
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
If there are still open transaction againt the database or if the database is setup for replication or CDC and the transaction are still not marked as replicated in the transaction log, the logfile can’t be shrinked.
Resolution
To resolve the same I go around multiliple ways will explain few workaround here.
1.If database is not setup for Logshipping,Replication,Mirroring and CDC.
1. set the database into simple recovery mode
2. Set the database back to full recovery mode.
3. Run the shrinkfile command.
DBCC SHRINKFILE (2,1)
or
DBCC SHRINKFILE (2)
or
DBCC SHRINKFILE (2,truncate_only)
(Mirosoft do not recommend to use truncate statement in production)
2.In case the database is setup for Replication (any topology) and transaction log file is growing tremendously and soon will be full. Transaction Log file can’t be shrinked as there are transaction which are not marked as replicated in the transaction log file.
Or
If transaction log is full and transaction written on the log file are not marked as replicated the logreader agent will keep trying to scan the log and mark the transaction as replicated but it will not succeed as there is no space to write transaction in the transaction (Mark the transaction as replicated).As the transaction are not marked as replicated DBA’s cant shrink the Log file.
To resolve the issue we can execute the below steps.
1. Lets say the trasaction log file is on drive D:\ of size 200 GB.
2. The transaction log file has grown by size 200 GB.
3. Create a new log file to some other drive.Let’s say on drive F:\ at the publisher database.
USE [master]
GO
ALTER DATABASE [xzcvxz] ADD LOG FILE ( NAME = N’xyz_log’, FILENAME = N’F:\xyz_01.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)
GO
4. As new log file is added to the database, SQL Server will start using it.the new transaction will be written to the newly added transaction log files.The Log reader agent will start scaning the old log file and will mark the transaction as distributed once sp_repcmd will replicate the commands or transaction to the distributor database.
5. Alter the Database in simple recovery mode.
ALTER DATABASE [xzcvxz] SET RECOVERY SIMPLE WITH NO_WAIT
6. Alter the Database again in full recovery mode.
ALTER DATABASE [xzcvxz] SET RECOVERY FULL WITH NO_WAIT
7. Now Shrink the Log file.
USE [xzcvxz]
DBCC SHRINKFILE (2)
Will write about how to shrink log file in Mirroring and Log shipping in my next article.
Here we will go over how to setup CDC on an exiting or new database in SQL Server 2008
How to setup Change Data Capture (CDC)
Create a Database or User the exiting Database
1. Enable the Database for CDC
Use <Database name>
exec sys.sp_cdc_enable_db
3. Enable Table for Change Data Capture
USE <DatabaseName>;
GO
EXECUTE sys.sp_cdc_enable_table
@source_schema = N’dbo’
, @source_name = N’Dep_Master’
, @role_name = N’cdc_Tables’
, @capture_instance=’cdc_dbo_DepMaster’;
4. Get Objects list which are enabled for CDC
exec sys.sp_cdc_help_change_data_capture
Use <Database Name>
SELECT sys.fn_cdc_get_min_lsn (‘@Capture_instance’)AS min_lsn;
7. Use <Database Name>
SELECT sys.fn_cdc_get_max_lsn()
8. Check all LSN’s
SELECT * FROM cdc.lsn_time_mapping
9. When u enable cdc for a table couple of things happens at the backgroud
1. creates @capture_instance Object
2. Creates two function as below. This will give u all the details
fn_cdc_get_all_changes_cdc_dbo_DepMaster
cdc.fn_cdc_get_net_changes_cdc_dbo_DepMaster
3. Creates two Jobs (Make sure SQL Server Agent Services are running)
cdc.CDC_capture
cdc.CDC_cleanup
10. Get all the changes happened on the object enabled for CDC
Declare @begin_time datetime,
@end_time datetime,
@from_lsn binary(10),
@to_lsn binary(10),
@max_lsn binary(10);
SET @begin_time=GETDATE()-1;
SET @end_time=GETDATE();
SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn (‘smallest greater than or equal’,@begin_time)
SELECT @to_lsn=sys.fn_cdc_map_time_to_lsn(‘largest less than or equal’,@end_time)
–SELECT @begin_time,@end_time,@from_lsn,@to_lsn
–Get all the changes happened on the object enabled for CDC
SELECT * FROM CDC.fn_cdc_get_all_changes_cdc_dbo_depmaster(@from_lsn,@to_lsn,’all’)
11. Get all the net changes done on each row of the object enabled for CDC.
Returns one net change row for each source row changed within the specified LSN range. That is, when a source row has multiple changes during the LSN range, a single row that reflects the final content of the row is returned by the function. For example, if a transaction inserts a row in the source table and a subsequent transaction within the LSN range updates one or more columns in that row, the function returns only one row, which includes the updated column values.
Declare @begin_time datetime,
@end_time datetime,
@from_lsn binary(10),
@to_lsn binary(10),
@max_lsn binary(10);
SET @begin_time=GETDATE()-1;
SET @end_time=GETDATE();
SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn (‘smallest greater than or equal’,@begin_time)
SELECT @to_lsn=sys.fn_cdc_map_time_to_lsn(‘largest less than or equal’,@end_time)
–SELECT @begin_time,@end_time,@from_lsn,@to_lsn
–Get all the changes happened on the object enabled for CDC
SELECT * FROM CDC.fn_cdc_get_all_changes_cdc_dbo_depmaster(@from_lsn,@to_lsn,’all’)
declare @begin_time datetime,@end_time datetime,@from_lsn binary(10),@to_lsn binary(10)
DECLARE @max_lsn binary(10);
SET @begin_time=GETDATE()-1;
SET @end_time=GETDATE();
SELECT @from_lsn=sys.fn_cdc_map_time_to_lsn (‘smallest greater than or equal’,@begin_time)
SELECT @to_lsn=sys.fn_cdc_map_time_to_lsn(‘largest less than or equal’,@end_time)
SELECT @max_lsn = MAX(__$start_lsn)
FROM cdc.fn_cdc_get_all_changes_cdc_dbo_depmaster(@from_lsn, @to_lsn, ‘all’);
SELECT sys.fn_cdc_map_lsn_to_time(@max_lsn);
Will write about how to use SSIS (No thrid party control) to perform ETL from CDCenabled table to Staging or Reporting Database in my next blog.
Below Script will generate the Drop Script of all the Constarints within a Database. ---Generates Script to Drop Constraints (Primary Key or Unique Key)--- --USE (DatabaseName) GO SET NOCOUNT ON --Declared Variables-- DECLARE @vNumDBs Int DECLARE @vCount Int DECLARE @SchemaName Varchar(MAX) DECLARE @TableName Varchar(MAX) DECLARE @ConstraintName Varchar(MAX) ---------------------- --Store Object and Schema Name-- DECLARE @Object_Holder TABLE (TabID int IDENTITY(1,1) , TableName varchar(max),Schemaname varchar(max)) INSERT INTO @Object_Holder(TableName,Schemaname) SELECT sys.objects.NAME AS TABLENAME, SCHEMA_NAME(sys.objects.SCHEMA_ID) AS SCHEMANAME from sys.objects INNER JOIN sys.indexes ON sys.objects.object_id = sys.indexes.object_id and sys.indexes.type_desc!='HEAP' GROUP BY sys.objects.name, SCHEMA_NAME(sys.objects.SCHEMA_ID),sys.objects.type HAVING (sys.objects.type='U') and sys.objects.name<>'sysdiagrams' order by sys.objects.name --and sys.objects.name='Test1' --------------------------------- SET @vNumDBs = @@RowCount SET @vCount = 1 While @vCount <= @vNumDBs BEGIN --Get SchemaName and Object Name for Each Object-- SELECT @SchemaName=Schemaname,@TableName=TableName FROM @Object_Holder WHERE TabID=@vCount --Print '['+@SchemaName+'].['+@TableName+']' SELECT @ConstraintName= name FROM sys.indexes WHERE is_primary_key=1 and is_unique in (0,1) and object_id=object_id(@TableName) --Print @vCount If @ConstraintName<>'' BEGIN Print 'ALTER TABLE['+@SchemaName+'].['+@TableName+'] DROP CONSTRAINT ['+@ConstraintName+']' --SELECT @ConstraintName END SET @ConstraintName='' SET @vCount = @vCount + 1 END SET NOCOUNT OFF
Download Updated Script : Index_Script
This script will help DBA's in scripting out all the existing
indexes(Clustered , Non-Clustered, Clustered with PK,
Clustered with Unique key, Non-Clustered with Unique Key etc.)
in a Database.Script will script the Partition Indexes which
exists on Partition Scheme,Primary Filegroup or on any Filegroup
In SQL Server 2008 there is no way to script a index with
Filegroup or Partition Scheme Name.
It seems to me as a BUG
as In SQL Server 2005 we can script the Index with Filegroup
and Partition Scheme Name.
Hope it will help lot of DBA's.
--------------------------------------------------------------
/*
Code Developed By : Mohd Sufian
Code Developed Date : 14th Novermber 2009
Code Developed Country : India
*/
SET NOCOUNT ON
---------------------------------------------------------------
DECLARE @vNumDBs Int
DECLARE @vCount Int
--Decalared Variable Index Info-----------------------
DECLARE @SchemaName Varchar(MAX)
DECLARE @TableName Varchar(MAX)
DECLARE @IndexName Varchar(MAX)
DECLARE @IndexType Varchar(MAX)
DECLARE @Index_Id Varchar(MAX)
DECLARE @Is_Primary_Key INT
DECLARE @Is_Unique_Key INT
DECLARE @Data_Space_id INT
--Declared Variable Included Column In Index----------
DECLARE @ColName VARCHAR(max)
DECLARE @Index_Column_id INT
DECLARE @KeyOrdinalid INT
DECLARE @partition_ordinal INT
DECLARE @IsDescendingKey INT
DECLARE @ColIncludedInPartitionFucntion VARCHAR(MAX)
-------------------------------------------------------
--Declare Storage Variable-----------------------------
DECLARE @Rowcount INT
DECLARE @Storage INT
DECLARE @IndexonFileGroup VARCHAR(MAX)
-------------------------------------------------------
--Declare Misleneous variables-------------------------
DECLARE @CommaSeprator VARCHAR(1)
-------------------------------------------------------
DECLARE @Object_Holder TABLE (TabID int IDENTITY(1,1) ,
TableName varchar(max),Schemaname varchar(max))
INSERT INTO @Object_Holder(TableName,Schemaname)
SELECT sys.objects.NAME AS TABLENAME,
SCHEMA_NAME(sys.objects.SCHEMA_ID) AS SCHEMANAME
from sys.objects
INNER JOIN sys.indexes ON
sys.objects.object_id = sys.indexes.object_id
and sys.indexes.type_desc!='HEAP'
GROUP BY sys.objects.name,
SCHEMA_NAME(sys.objects.SCHEMA_ID),sys.objects.type
HAVING (sys.objects.type='U') and sys.objects.name<>'sysdiagrams'
order by sys.objects.name --and sys.objects.name='Test1'
SET @vNumDBs = @@RowCount
SET @vCount = 1
While @vCount <= @vNumDBs
BEGIN
SELECT @SchemaName=Schemaname,@TableName=TableName
FROM @Object_Holder where TabID=@vCount
---Check for Indexes on Each Objects
DECLARE @vNumIndex Int
DECLARE @vCountIndex Int
Print '--Index Script for Object :::::'+@TableName
CREATE Table #Index_Info_Holder (Index_RowID INT IDENTITY(1,1),
Index_Name varchar(MAX),Index_Type varchar(MAX),Index_Id Int,
ObjectID INT,IsPrimaryKey INT,IsUnique INT,data_space_id INT)
INSERT INTO #Index_Info_Holder (Index_Name,Index_Type,Index_Id,
ObjectID,IsPrimaryKey,IsUnique,data_space_id)
SELECT name , type_desc ,index_id,object_id,is_primary_key,
is_unique,data_space_id FROM sys.indexes where
object_id=OBJECT_ID(@TableName) and type_desc!='HEAP'
--'CDS_BreakMaster')--(@TableName)
SET @vNumIndex = @@RowCount
SET @vCountIndex = 1
WHILE @vCountIndex <= @vNumIndex
BEGIN
SELECT @IndexName=Index_name ,@IndexType= Index_type ,
@Index_Id=index_id,@Is_Primary_Key=IsPrimaryKey,
@Is_Unique_Key=IsUnique,@Data_Space_id=data_space_id
FROM #Index_Info_Holder
where objectid=OBJECT_ID(@TableName)
and Index_RowID=@vCountIndex
If @IndexType='CLUSTERED' and @Is_Primary_Key=1 --OR
@IndexType='NON CLUSTERED' or
BEGIN
Print 'ALTER TABLE ' + '[' + @SchemaName + '].[' + @TableName +'] ' +
'ADD CONSTRAINT ['+@IndexName+']' +' PRIMARY KEY CLUSTERED '
Print '('
END
If @IndexType='NONCLUSTERED' and @Is_Unique_Key=1--OR @IndexType='NON CLUSTERED' or
BEGIN
Print 'ALTER TABLE ' + '[' + @SchemaName + '].[' + @TableName +'] '
+ 'ADD CONSTRAINT ['+@IndexName+']' +' UNIQUE NONCLUSTERED '
Print '('
END
If @IndexType='NONCLUSTERED' and @Is_Unique_Key=0 and @Is_Primary_Key=0
BEGIN
Print 'CREATE NONCLUSTERED INDEX ['+@IndexName+'] ON' + ' [' + @SchemaName + '].['
+ @TableName + ']'
Print '('
END
If @IndexType='CLUSTERED' and @Is_Unique_Key=0 and @Is_Primary_Key=0
BEGIN
Print 'CREATE CLUSTERED INDEX ['+ @IndexName +'] ON' + ' [' + @SchemaName + '].['
+ @TableName + ']'
Print '('
END
---Columns Included in Index--
DECLARE @vNumIndexIncludedCol Int
DECLARE @vCountIndexIncludedCol Int
CREATE TABLE #Index_IncludedColumnInfo
(
Index_IncludedColumnRowID INT IDENTITY(1,1),
Index_IncludedObjectId INT,
Index_IncludedColName Varchar(MAX),
Index_IncludedColID INT,
Index_IncludedColKeyOrdinal INT,
Index_IncludedColPartitionOrdinal INT,
Index_IncludedColPartitionIsDescendingKey INT
)
INSERT INTO #Index_IncludedColumnInfo
(Index_IncludedObjectId,Index_IncludedColName,Index_IncludedColID,
Index_IncludedColKeyOrdinal,Index_IncludedColPartitionOrdinal,
Index_IncludedColPartitionIsDescendingKey)SELECT object_id,
COL_NAME(object_id(@TableName),column_id),index_column_id,
key_ordinal,partition_ordinal,is_descending_key FROM
sys.index_columns where Object_Id=object_id(@TableName)|
and index_id=@Index_Id and key_ordinal<>0--and Partition_ordinal!=1
SET @vNumIndexIncludedCol = @@RowCount
SET @vCountIndexIncludedCol = 1
WHILE @vCountIndexIncludedCol <= @vNumIndexIncludedCol
BEGIN
SELECT @ColName=Index_IncludedColName,@Index_Column_id=Index_IncludedColID,
@KeyOrdinalid=Index_IncludedColKeyOrdinal,
@partition_ordinal=Index_IncludedColPartitionOrdinal,
@IsDescendingKey=Index_IncludedColPartitionIsDescendingKey
FROM #Index_IncludedColumnInfo WHERE
Index_IncludedColumnRowID=@vCountIndexIncludedCol
and Index_IncludedColKeyOrdinal<>0
If @vCountIndexIncludedCol=@vNumIndexIncludedCol
--or @vCountIndexIncludedCol != @vNumIndexIncludedCol
BEGIN
SELECT @CommaSeprator=' '
END
If @vCountIndexIncludedCol<>@vNumIndexIncludedCol
--and @vCountIndexIncludedCol != @vCountIndexIncludedCol
BEGIN
SELECT @CommaSeprator=','
END
If @IsDescendingKey=0
BEGIN
Print '['+@ColName+'] ASC' + @CommaSeprator
END
If @IsDescendingKey=1
BEGIN
Print '['+@ColName+'] DESC'
END
SET @ColName=''
SET @vCountIndexIncludedCol = @vCountIndexIncludedCol + 1
END
SELECT @ColIncludedInPartitionFucntion=COL_NAME(object_id(@TableName),column_id)
FROM sys.index_columns where Object_Id=object_id(@TableName) and index_id=@Index_Id
and Partition_ordinal=1
SELECT @Storage= Index_IncludedColPartitionOrdinal from #Index_IncludedColumnInfo
where Index_IncludedColPartitionOrdinal>0
Print ')'
If @IndexType='CLUSTERED' and @Is_Primary_Key=0 and @Is_Primary_Key=0
BEGIN
Print 'WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)'
END
If @IndexType='CLUSTERED' and @Is_Primary_Key=1
BEGIN
Print 'WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)'
END
If @IndexType='NONCLUSTERED' and @Is_Unique_Key=1--OR @IndexType='NON CLUSTERED' or
BEGIN
Print 'WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)'
END
If @IndexType='NONCLUSTERED' and @Is_Unique_Key=0 and @Is_Primary_Key=0
BEGIN
PRINT 'WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70)'
END
SELECT @IndexonFileGroup=[name] FROM SYS.DATA_SPACES WHERE data_space_id=@Data_Space_id
If @ColIncludedInPartitionFucntion IS NULL
BEGIN
SET @ColIncludedInPartitionFucntion=' '
END
If @ColIncludedInPartitionFucntion =''--IS NOT NULL
BEGIN
PRINT 'ON '+'['+@IndexonFileGroup+']'
--+ '(['+@ColIncludedInPartitionFucntion+'])'
END
If @ColIncludedInPartitionFucntion !=''
and @IndexonFileGroup<>'Primary'-- is not null --IS NOT NULL
BEGIN
PRINT 'ON '+'['+@IndexonFileGroup+']'+ '(['+@ColIncludedInPartitionFucntion+'])'
END
If @ColIncludedInPartitionFucntion !=''
and @IndexonFileGroup='Primary'-- is not null --IS NOT NULL
BEGIN
PRINT 'ON '+'['+@IndexonFileGroup+']'--+ '(['+@ColIncludedInPartitionFucntion+'])'
END
SET @Storage=''
DROP TABLE #Index_IncludedColumnInfo
Print '---------End of Index Script------------------------'
SET @vCountIndex = @vCountIndex + 1
END
DROP TABLE #Index_Info_Holder
--**********************************--
SET @vCount = @vCount + 1
END
SET NOCOUNT OFF
Note : The script will Generate the create script for Constraints and Indexes.
This topic describes the database snapshot feature for partitioned databases, which was new in Microsoft SQL Server 2005. Database snapshots are available only in SQL Server 2005 Enterprise Edition and later versions. All recovery models support database snapshots.
I have a partitioned database with few Files and FileGroups.
We have 24 Secondry Files accociated with 6 FileGroups and one Primary FileGroup.
SecondryFiles
Each filegroup consist of 4 secondry Data files .
FileGroups
Creating a database snapshot that has files and file groups, AdventureWork_001_snapshot , on AdventureWorks database.
All file should be specified while creating a database snapshot of database having files and file group otherwise you will receive the below error:
Msg 5127, Level 16, State 1, Line 4
All files must be specified for database snapshot creation. Missing the file “Logical File Name”.
Script to Create Database Snapshot for Partitioned Database
GO
–Create Partitioned Database Snapshot
BEGIN
CREATE DATABASE AdventureWork_001_snapshot ON
( NAME = AdventureWorks2008_Data, FILENAME =
‘D:\SQLServer 2008\SnapshotDB\AdventureWorks_Data.ss’),
( NAME = SFJan_2008, FILENAME =
‘D:\SQLServer 2008\SnapshotDB\SFJan_2008.ss’),
( NAME = SFFeb_2008, FILENAME =
‘D:\SQLServer 2008\SnapshotDB\SFFeb_2008.ss’),
( NAME = SFMar_2008, FILENAME =
‘D:\SQLServer 2008\SnapshotDB\SFMar_2008.ss’),
( NAME = SFApr_2008, FILENAME =
‘D:\SQLServer 2008\SnapshotDB\SFApr_2008.ss’),
( NAME = SFMay_2008, FILENAME =
‘D:\SQLServer 2008\SnapshotDB\SFMay_2008.ss’),
( NAME = SFJun_2008, FILENAME =
‘D:\SQLServer 2008\SnapshotDB\SFJun_2008.ss’),
( NAME = SFJuly_2008, FILENAME =
‘D:\SQLServer 2008\SnapshotDB\SFJuly_2008.ss’),
( NAME = SFAug_2008, FILENAME =
‘D:\SQLServer 2008\SnapshotDB\SFAug_2008.ss’),
( NAME = SFSep_2008, FILENAME =
‘D:\SQLServer 2008\SnapshotDB\SFSep_2008.ss’),
( NAME = SFOct_2008, FILENAME =
‘D:\SQLServer 2008\SnapshotDB\SFOct_2008.ss’),
( NAME = SFNov_2008, FILENAME =
‘D:\SQLServer 2008\SnapshotDB\SFNov_2008.ss’),
( NAME = SFDec_2008, FILENAME =
‘D:\SQLServer 2008\SnapshotDB\SFDec_2008.ss’),
( NAME = SFJan_2009, FILENAME =
‘D:\SQLServer 2008\SnapshotDB\SFJan_2009.ss’),
( NAME = SFFeb_2009, FILENAME =
‘D:\SQLServer 2008\SnapshotDB\SFFeb_2009.ss’),
( NAME = SFMar_2009, FILENAME =
‘D:\SQLServer 2008\SnapshotDB\SFMar_2009.ss’),
( NAME = SFApr_2009, FILENAME =
‘D:\SQLServer 2008\SnapshotDB\SFApr_2009.ss’),
( NAME = SFMay_2009, FILENAME =
‘D:\SQLServer 2008\SnapshotDB\SFMay_2009.ss’),
( NAME = SFJun_2009, FILENAME =
‘D:\SQLServer 2008\SnapshotDB\SFJun_2009.ss’),
( NAME = SFJuly_2009, FILENAME =
‘D:\SQLServer 2008\SnapshotDB\SFJuly_2009.ss’),
( NAME = SFAug_2009, FILENAME =
‘D:\SQLServer 2008\SnapshotDB\SFAug_2009.ss’),
( NAME = SFSep_2009, FILENAME =
‘D:\SQLServer 2008\SnapshotDB\SFSep_2009.ss’),
( NAME = SFOct_2009, FILENAME =
‘D:\SQLServer 2008\SnapshotDB\SFOct_2009.ss’),
( NAME = SFNov_2009, FILENAME =
‘D:\SQLServer 2008\SnapshotDB\SFNov_2009.ss’),
( NAME = SFDec_2009, FILENAME =
‘D:\SQLServer 2008\SnapshotDB\SFDec_2009.ss’)
AS SNAPSHOT OF AdventureWorks
END
Database Snasphot gets created once the above script is executed.
Databse snaspshot will have all the files and filegroups.
Database snapshot feature is available in MS-SQLServer2005 and SQLServer2008.It is supported by all recovery models of SQL Server 2005 and SQL Server 2008.
A Database snapshot is read-only, static view of a database (the source database).
A Database snapshot captures the data and committed transitions of the OLTP Database.
No uncommitted transactions are recorded in database snapshot.
Snapshot Database doesn’t work if OLTP Database is offline or detached.
Snapshots are basically created for reporting and real only purpose but can degrade system performance.
Scenarios1:
If Programmer need access to run queries on production server to see real time data u can create a Database Snapshot and provide access to them on the same.
Scenarios 2:
Database snapshot can be used for Reporting solutions.
Imp: Database Snapshot cannot be created on system databases except model database.
Imp: If Source Database is offline then snapshot database won’t work.
Code to Create Database Snapshot
Pass Database name as parameter whose snapshot is to be created
Create Procedure [dbo].[UspDbSnapShot]
(@Databasename varchar(100))
AS
BEGIN
SET NOCOUNT ON
Declare @DbName varchar(100);
Declare @DbSnapshotPath varchar(100);
Declare @DatabaseDataFile varchar(50);
Declare @CreateDBName varchar(100);
Declare @DateTime varchar(100);
Declare @Sql varchar(MAX);
Set @DbName=@Databasename
Set @DbSnapshotPath=‘D:\SQLServer 2008\SnapshotDB\’
Select @DatabaseDataFile= name from sys.master_files where database_id=db_id(@DbName) and file_id=1
SELECT @DateTime=LTRIM(STR(DATEPART(HOUR, GETDATE()))) + LTRIM(STR(DATEPART(MINUTE, GETDATE())))+ LTRIM(STR(DATEPART(SECOND, GETDATE())))+
+‘_’+ LTRIM(STR(DATEPART(DAY, GETDATE())))+ LTRIM(STR(DATEPART(MONTH, GETDATE()))) + LTRIM(STR(DATEPART(YEAR, GETDATE())))
SET @CreateDBName=@DbName+‘_’+‘snapshot’+ ‘_’+@DateTime
print @CreateDBName
SET @Sql=‘Create Database ‘ + @CreateDBName + ‘ ON ( NAME=’+ ””+@DatabaseDataFile+”” +‘,FILENAME=’+””+@DbSnapshotPath + @CreateDBName+‘.ss’+””+
+‘)AS SNAPSHOT OF ‘+ @DbName
execute (@sql)
SET NOCOUNT OFF
END
The same can be scheduled in a job to create Database snapshot at time intervals.
msufian