Vous êtes ici :
Ce script simplifie la création d’instantanés de bases de données (database snapshot) en créant la procédure sp_createsnapshot
Transact-SQL
|
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 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 |
/*------------------------------------------------------------------- [SCRIPT] sp_createsnapshot [DATABASE] master [DESCRIPTION] Créer un snapshot d'une base de données. [CREE/MODIFIE PAR] DATAFLY - Arian Papillon Utilise aussi la fonction F_EXTRACT_FILE (auteur Frédéric Brouard) [DATE] 20190125 -------------------------------------------------------------------*/ /****** Object: StoredProcedure [dbo].[sp_createsnapshot] Script Date: 29/11/2016 16:12:55 ******/ --EXEC sp_createsnapshot -- @dbname = 'AdventureWorks', -- @pathoverride = 'c:\temp\', -- @noexecute = 1, -- @snapshotname = 'AdventureWorks_snapshot' , -- @help = 0 USE [master] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- Fonction F_EXTRACT_FILE (auteur Frédéric BROUARD) CREATE OR ALTER FUNCTION dbo.F_EXTRACT_FILE ( @FILE NVARCHAR(1024) , @WHAT CHAR(1) = 'N' ) RETURNS NVARCHAR(1024) AS BEGIN -- si @WHAT = 'N' renvoi le nom, -- @WHAT = 'E' renvoi l'extension, -- @WHAT = 'P' renvoi le path, IF ( @WHAT IS NULL ) OR ( @WHAT NOT IN ( 'N', 'E', 'P' )) OR ( @FILE IS NULL ) OR ( @FILE = '' ) RETURN NULL; DECLARE @POINT SMALLINT , @SLASH SMALLINT , @LEN SMALLINT , @DATA NVARCHAR(1024); SET @LEN = LEN(@FILE); IF @WHAT IN ( 'N', 'E' ) SET @POINT = @LEN - CHARINDEX('.', REVERSE(@FILE)) + 1; IF CHARINDEX('.', @FILE) = 0 SET @POINT = @LEN + 1; IF @WHAT IN ( 'N', 'P' ) SET @SLASH = @LEN - CHARINDEX('\', REVERSE(@FILE)) + 1; IF CHARINDEX('\', @FILE) = 0 SET @SLASH = 0; IF @WHAT = 'N' RETURN SUBSTRING(@FILE, @SLASH + 1, @POINT - @SLASH - 1); IF @WHAT = 'P' AND @SLASH = 0 RETURN ''; IF @WHAT = 'P' RETURN SUBSTRING(@FILE, 1, @SLASH); IF @WHAT = 'E' AND @POINT > @LEN RETURN ''; IF @WHAT = 'E' RETURN SUBSTRING(@FILE, @POINT + 1, LEN(@FILE) - @POINT); RETURN NULL; END; GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- Procédure sp_createsnapshot CREATE OR ALTER PROCEDURE [dbo].[sp_createsnapshot] @dbname [sysname] = '', @pathoverride [VARCHAR](255) = '', @noexecute [BIT] = 0, @snapshotname [sysname] = '' OUTPUT, @help [BIT] = 0 WITH EXECUTE AS CALLER AS SET NOCOUNT ON; IF @help = 1 BEGIN PRINT 'Help Specified.'; GOTO printoptions; END; IF @dbname NOT IN ( SELECT name FROM master.sys.databases ) OR LEN(@dbname) = 0 BEGIN PRINT 'Warning! Database [' + @dbname + '] specified cannot be found.'; GOTO printoptions; END; IF @dbname IN ( 'master', 'model', 'tempdb' ) BEGIN PRINT 'snapshot creation on database [' + @dbname + '] is not allowed.'; GOTO printoptions; END; DECLARE @now DATETIME; DECLARE @ssname sysname; DECLARE @cmd VARCHAR(MAX); DECLARE @uniqueid sysname; SET @now = GETDATE(); --create a unique name that we will use in the snapshot name --and within each datafile to avoid conflict with others SET @uniqueid = REPLACE(STR(DATEPART(yyyy, @now), 4) + STR(DATEPART(mm, @now), 2) + STR(DATEPART(dd, @now), 2) + STR(DATEPART(hh, @now), 2) + STR(DATEPART(mi, @now), 2) + STR(DATEPART(ss, @now), 2), ' ', '0'); --dbname becomes __SX IF @snapshotname <> '' SET @ssname = @snapshotname; ELSE SET @ssname = @dbname + '_' + @uniqueid + '_SX'; SET @cmd = 'CREATE DATABASE [' + @ssname + '] ON '; --loop through datafiles and assign a unique name SELECT @cmd = @cmd + CHAR(10) + '(NAME = ''' + RTRIM(name) + ''', FILENAME = ''' + CASE WHEN @pathoverride <> '' THEN @pathoverride ELSE dbo.F_EXTRACT_FILE(physical_name,'P') END + dbo.F_EXTRACT_FILE(physical_name,'N') + '_' + dbo.F_EXTRACT_FILE(physical_name,'E') + '_' + @uniqueid + '._sx''),' FROM sys.master_files WHERE type <> 1 -- ignore logfile since snapshots do not create one AND database_id = DB_ID(@dbname); SET @cmd = LEFT(@cmd, LEN(@cmd) - 1); --take away extra trailing comma left from SELECT file assignment SET @cmd = @cmd + CHAR(10) + ' AS SNAPSHOT OF [' + @dbname + ']'; --complete statement PRINT @cmd; IF @noexecute = 0 BEGIN PRINT '-- Script execution specified...'; BEGIN TRY EXEC (@cmd); PRINT 'Snapshot Database ' + @ssname + ' created from ' + @dbname; END TRY BEGIN CATCH PRINT 'Snapshot creation failed (' + ERROR_MESSAGE() + ')'; END CATCH; END; ELSE PRINT '-- Script execution overridden. Run this output to create.'; SET @snapshotname = @ssname; RETURN; printoptions: SET @snapshotname = NULL; PRINT ' @dbname sysname = '''', --required option (database to create snapshot on) @pathoverride VARCHAR(255) = '''', --path to override snapshot files location @noexecute bit = 0, --optional option, when set to 1 snapshot script will not be executed and only output creation statements. @snapshotname sysname = '''' OUTPUT, --snapshot name (could be generated if empty), output parameter @help bit = 0 --optional option (view help) ====================================================== '; GO |
Table of Contents