forked from Arvindpoongothai/SQLDBAInventory
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathDB--Quick DBSnapshot
122 lines (107 loc) · 4.23 KB
/
DB--Quick DBSnapshot
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
--Quick DB Snapshot
/*
Snapshot script:
The script enables a quick creation of snapshots for databases.
It has two run modes defined by the optional parameter @exec_mode.
@exec_mode=0 (default) means printing the create snapshot statment and
@exec_mode=1 means creation of the snapshot database.
@database is mandatory input parameter.
Tested on SQL Server 2005, 2008, 2012.
Usage:
EXEC [dbo].[Create_UserDb_Snapshot] @database = 'AdventureWorksDW2012'
EXEC [dbo].[Create_UserDb_Snapshot] @database = 'AdventureWorksDW2012', @exec_mode=0
EXEC [dbo].[Create_UserDb_Snapshot] @database = 'CWNET', @exec_mode=1
Date created: 2014-Mar-12
Author: Igor Micev ([email protected]; [email protected])
*/
/***************************************************************************
Snapshot script:
The script enables a quick creation of snapshots for databases.
It has two run modes defined by the optional parameter @exec_mode.
@exec_mode=0 (default) means printing the create snapshot statement and
@exec_mode=1 means creation of the snapshot database.
@database is mandatory input parameter.
Tested on SQL Server 2008 and 2012.
Usage:
EXEC [dbo].[Create_UserDb_Snapshot] @database = 'AdventureWorksDW2012'
EXEC [dbo].[Create_UserDb_Snapshot] @database = 'AdventureWorksDW2012', @exec_mode=0
EXEC [dbo].[Create_UserDb_Snapshot] @database = 'CWNET', @exec_mode=1
Date created: 2014-Mar-12
Author: Igor Micev ([email protected]; [email protected])
***************************************************************************/
USE [master]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Create_UserDb_Snapshot]') AND [type] in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Create_UserDb_Snapshot]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[Create_UserDb_Snapshot]
@database varchar(50),
@exec_mode bit=0
as
begin
set nocount on
declare @err_text nvarchar(200)
if @database in ('master','model','tempdb')
begin
set @err_text = 'Snapshot is not allowed for master, model and tempdb databases.'
RAISERROR(@err_text,10,1)
return;
end
if object_id('tempdb..#usr_db_files_tbl') is not null
drop table #usr_db_files_tbl
create table #usr_db_files_tbl(
[logicalName] varchar(100),
[physicalName] varchar(200)
)
insert into #usr_db_files_tbl(logicalName,physicalName)
select [mf].[name],[mf].[physical_name]
from sys.master_files as [mf]
where [mf].[database_id]=db_id(@database) and [mf].[type]=0 and [mf].[name] not in ('master','model','tempdb')
if (select count(*) from #usr_db_files_tbl)=0
begin
set @err_text = 'The specified database does not exist.'
RAISERROR(@err_text,10,1)
return;
end
declare @dyn_sql nvarchar(max)
declare @extension varchar(20)='snap'+right(replace(replace(replace(convert(varchar(20),getdate(),113),'-',''),':',''),' ',''),4)
declare @curLogicalName varchar(100), @curPhysicalName varchar(200)
declare snap_cursor cursor for
select [t].[logicalName], [t].[physicalName] from #usr_db_files_tbl as [t]
open snap_cursor
declare @snapDbName nvarchar(100)
set @snapDbName=@database+'_snapshot_'+replace(replace(replace(convert(varchar(20),getdate(),113),'-',''),':',''),' ','')
fetch next from snap_cursor into @curLogicalName,@curPhysicalName
set @dyn_sql='CREATE DATABASE '+@snapDbName+' ON '+char(10)
while (@@FETCH_STATUS=0)
begin
set @dyn_sql+='(name='''+@curLogicalName+''', filename='''+replace(replace(@curPhysicalName,right(@curPhysicalName,4),'.'+@extension),right(@curPhysicalName,4),'.'+@extension)+''')'
fetch next from snap_cursor into @curLogicalName,@curPhysicalName
if (@@FETCH_STATUS=0)
set @dyn_sql+=', '+char(10)
end
set @dyn_sql+=char(10)
set @dyn_sql+='AS SNAPSHOT OF '+@database+char(10)
if @exec_mode=convert(bit,0)
print char(10)+@dyn_sql
if @exec_mode=convert(bit,1)
begin
begin try
exec sp_executesql @dyn_sql
print 'Snapshot database: '+@snapDbName+' created successfully.'
end try
begin catch
set @err_text = 'Snapshot for database '+@database+' cannot be created.'
RAISERROR(@err_text,16,1)
end catch
end
close snap_cursor
deallocate snap_cursor
drop table #usr_db_files_tbl
WAITFOR DELAY '00:00:01';
return;
end