Today's Script will drop all database snapshots and create a database snapshot for all online read writeable user databases. I create this script for use in a database mirroring partnership so that the snapshots could be used on the mirroring partner so to impact the mirroring principal less. As such I set the mirroring partnership servers at the beginning of the script because the mirroring partners databases are inaccessible so I have to retrieve the file information from the mirroring principal. This can be changed to run on servers not in a mirroring partnership.
Other than that the only other thing to update is the @SnapshotDirectory variable to the path where you want the snapshots to exist. Each snapshot has a prefix of 'snap_' and a suffix of the time in the format of '_hh00' this was because the snapshots where created on an hourly basis.
/*
-----------------------------------------------------------------
Drop And Create Database Snapshots
-----------------------------------------------------------------
For more SQL resources, check out SQLServer365.blogspot.com
-----------------------------------------------------------------
You may alter this code for your own purposes.
You may republish altered code as long as you give due credit.
You must obtain prior permission before blogging this code.
THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
-----------------------------------------------------------------
*/
/*
* Name: spDropAndCreateDatabaseSnapshots
* Description: This procedure drops all database snapshots and creates a databsae snapshot of
* all online read / writable user databases
* Returns:
* Source Control:
* Execution: EXEC dbo.spDropAndCreateDatabaseSnapshots
* VERSION CHANGES
* Release Initials CostBefore CostAfter
* v1.0 CMc N/A 0.0033275
*/
DROP PROCEDURE [dbo].[spDropAndCreateDatabaseSnapshots] ;
GO
CREATE PROCEDURE [dbo].[spDropAndCreateDatabaseSnapshots]
AS
BEGIN
SET NOCOUNT ON
-- Declare Variables
DECLARE @MinDBID INT
DECLARE @MaxDBID INT
DECLARE @DatabaseName VARCHAR(100)
DECLARE @SQL VARCHAR(6000)
DECLARE @SQL1 VARCHAR(2000)
DECLARE @SQL2 VARCHAR(2000)
DECLARE @SQL3 VARCHAR(2000)
DECLARE @SQL4 VARCHAR(2000)
DECLARE @SnapshotPrefix VARCHAR(5)
DECLARE @SnapshotName VARCHAR(200)
DECLARE @SnapshotSeperator VARCHAR(1)
DECLARE @SnapshotHour VARCHAR(3)
DECLARE @SnapshotMin VARCHAR(2)
DECLARE @SnapshotExtension VARCHAR(5)
DECLARE @SnapshotDirectory VARCHAR(100)
DECLARE @MinFileID INT
DECLARE @MaxFileID INT
DECLARE @DBSnapshotName VARCHAR(100)
DECLARE @MinSnapshotID INT
DECLARE @MaxSnapshotID INT
DECLARE @ServerName VARCHAR(15)
-- Set Variables
SET @SnapshotDirectory = 'D:\Snapshot\'
SET @SnapshotPrefix = 'Snap_'
SET @SnapshotSeperator = '_'
SELECT @SnapshotHour = DATEPART(hh, GETDATE())
-- Set the servername for the mirroring partner to pick up file names for each database
IF @@SERVERNAME = 'PARTNERSERVER'
SET @ServerName = 'PRINCIPLESERVER'
IF @@SERVERNAME = 'PRINCIPLESERVER'
SET @ServerName = 'PARTNERSERVER'
-- If time is before 10am then add a leading 0 for consistancy
IF LEN(@SnapshotHour) < 2
SET @SnapshotHour = '0' + @SnapshotHour
SET @SnapshotHour = @SnapshotSeperator + @SnapshotHour
SET @SnapshotMin = '00'
SET @SnapshotExtension = '.snap'
-- Check for temporary tableS and drop it if it exists
IF OBJECT_ID('tempDB.dbo.#Database') IS NOT NULL
DROP TABLE [#Database] ;
IF OBJECT_ID('tempDB.dbo.#SQL2') IS NOT NULL
DROP TABLE #SQL2 ;
IF OBJECT_ID('tempDB.dbo.#Snapshot') IS NOT NULL
DROP TABLE #Snapshot ;
-- Create temporary tables
CREATE TABLE #Database
(
ID INT IDENTITY(1, 1),
DatabaseName VARCHAR(100)
)
CREATE TABLE #SQL2
(
ID INT IDENTITY(1, 1),
SQL2 VARCHAR(2000)
)
CREATE TABLE #Snapshot
(
ID INT IDENTITY(1, 1),
SnapshotName VARCHAR(2000)
)
-- Check for existing database snapshots and delete them
IF EXISTS ( SELECT name
FROM sys.databases
WHERE --snapshot_isolation_state = 1
--AND
name NOT IN ( 'master', 'model', 'msdb', 'tempdb',
'distribution' )
AND LEFT(name, 5) = 'Snap_' )
BEGIN
-- Insert all database snapshot names into a temporary table
INSERT INTO #Snapshot ( SnapshotName )
SELECT name
FROM sys.databases
WHERE --snapshot_isolation_state = 1
--AND
name NOT IN ( 'master', 'model', 'msdb',
'tempdb', 'distribution' )
AND LEFT(name, 5) = 'Snap_'
-- Set Variables for the drop snapshot loop
SELECT @MinSnapshotID = MIN(ID),
@MaxSnapshotID = MAX(ID)
FROM #Snapshot
-- Begin loop to drop snapshots
WHILE @MinSnapshotID <= @MaxSnapshotID
BEGIN
-- Get SnapshotName
SELECT @DBSnapshotName = SnapshotName
FROM #Snapshot
WHERE ID = @MinSnapshotID
-- Build DROP DATABASE COMMAND
SET @SQL = 'DROP DATABASE ' + @DBSnapshotName + ';'
-- Try Catch block to execute SQL and handle errors
BEGIN TRY
-- Drop Database Snapshots
EXEC ( @SQL
)
END TRY
BEGIN CATCH
SELECT @DatabaseName,
message_id,
severity,
[text],
@SQL
FROM sys.messages
WHERE message_id = @@ERROR
AND language_id = 1033 -- British English
END CATCH
-- Get the next SnapshotName ID
SET @MinSnapshotID = @MinSnapshotID + 1
-- End Loop
END
END
-- Create Database Snapshots for all Online Read/Writable databases
IF EXISTS ( SELECT name
FROM sys.databases
WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb',
'distribution', 'reports',
'reportserver', 'reportservertempdb' )
AND DATABASEPROPERTYEX(name, 'Updateability') = 'READ_WRITE'
AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
)
BEGIN
-- Insert Online, Read/Writable database names into temporary table
INSERT INTO #Database ( DatabaseName )
SELECT name
FROM sys.databases
WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb',
'distribution', 'reports',
'reportserver', 'reportservertempdb' )
AND DATABASEPROPERTYEX(name, 'Updateability') = 'READ_WRITE'
AND DATABASEPROPERTYEX(name, 'Status') = 'ONLINE'
SELECT @MinDBID = MIN(ID),
@MaxDBID = MAX(ID)
FROM #Database
-- Begin Loop
WHILE @MinDBID <= @MaxDBID
BEGIN
-- Get DatabaseName
SELECT @DatabaseName = DatabaseName
FROM #Database
WHERE ID = @MinDBID
-- Build up snapshot string
SET @SnapshotName = @SnapshotPrefix + @DatabaseName
+ @SnapshotHour + @SnapshotMin
-- Create Start of SQL command to be run
SET @SQL1 = 'USE master;
CREATE DATABASE ' + @SnapshotName + '
ON '
-- Remove records from table ready for next database
TRUNCATE TABLE #SQL2
-- Build command to Insert files into temp table
SET @SQL2 = 'INSERT #SQL2
SELECT ''( NAME = '' '
+ '+ '''' + name + '', FILENAME = ''''' + @SnapshotDirectory + ''' + name + '''
+ @SnapshotHour + @SnapshotMin + '.snap'''')''
FROM ' + @ServerName + '.' + @DatabaseName + '.sys.database_files
WHERE type = 0
AND state = 0'
--print @SQL2
-- Try Catch block to execute SQL and handle errors
BEGIN TRY
-- Insert files into tmp table
EXEC ( @SQL2
)
END TRY
BEGIN CATCH
SELECT @DatabaseName,
message_id,
severity,
[text],
@SQL
FROM sys.messages
WHERE message_id = @@ERROR
AND language_id = 1033 -- British English
END CATCH
-- Set Variables for the append , loop
SELECT @MinFileID = MIN(ID),
@MaxFileID = MAX(ID)
FROM #SQL2
-- Begin Loop to append , to the end of all except the last record
WHILE @MinFileID < @MaxFileID
BEGIN
-- Append , to the end of the current record
UPDATE #SQL2
SET SQL2 = SQL2 + ','
WHERE ID = @MinFileID
-- Get the next DatabaseName ID
SET @MinFileID = @MinFileID + 1
-- End Loop
END
SELECT @MinFileID = MIN(ID),
@MaxFileID = MAX(ID)
FROM #SQL2
SET @SQL3 = ''
-- Begin Loop to concatenante all files
WHILE @MinFileID <= @MaxFileID
BEGIN
-- Append , to the end of the current record
SELECT @SQL2 = SQL2
FROM #SQL2
WHERE ID = @MinFileID
SET @SQL3 = @SQL3 + @SQL2
-- Get the next DatabaseName ID
SET @MinFileID = @MinFileID + 1
-- End Loop
END
-- Create End of SQL command to be run
SET @SQL4 = 'AS SNAPSHOT OF ' + @DatabaseName + ';'
-- Concatenate SQL variables ready for execution
SET @SQL = @SQL1 + @SQL3 + @SQL4
-- Try Catch block to execute SQL and handle errors
BEGIN TRY
-- Create Database Snapshots
EXEC ( @SQL
)
END TRY
BEGIN CATCH
SELECT @DatabaseName,
message_id,
severity,
[text],
@SQL
FROM sys.messages
WHERE message_id = @@ERROR
AND language_id = 1033 -- British English
END CATCH
-- Get the next DatabaseName ID
SET @MinDBID = @MinDBID + 1
-- End Loop
END
END
END
Enjoy!
Chris
