Voici une procédure SQL que vous pouvez utiliser pour créer un snapshot de base de données de manière automatique. Par exemple dans une tâche planifié de l’agent SQL Server :
CREATE PROCEDURE dbo.sp__DB_SNAP @DB NVARCHAR(128), @PATH NVARCHAR(256)
AS
IF NOT EXISTS(SELECT *
FROM master.sys.DATABASES
WHERE name = @DB
AND source_database_id IS NULL
AND state_desc = 'ONLINE')
BEGIN
RAISERROR('Le nom de base %s n''existe pas sur ce serveur ou n''est pas en état copiable.', 16, 1, @DB);
RETURN;
END;
IF RIGHT(@PATH, 1) = '\'
SET @PATH = @PATH + '\';
DECLARE @T TABLE (file_exists bit, file_is_dir bit, parent_dir_exists bit);
INSERT INTO @T
EXEC master.sys.xp_fileexist @PATH;
IF NOT EXISTS(SELECT 0
FROM @T
WHERE file_is_dir = 1)
BEGIN
RAISERROR('Le chemin passé en arguement, n''est pas un répertoire valide.' , 16, 1);
RETURN
END
DECLARE @SQL VARCHAR(MAX);
SET @SQL = 'CREATE DATABASE [' + @DB +'_SNAP_'
+ REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(23), CURRENT_TIMESTAMP, 121), '-', ''), ' ', '_'), ':', ''), '.', '_')
+ '] ON '
SELECT @SQL = @SQL + '( NAME = ' + name +', FILENAME = '''
+ @PATH + REVERSE(SUBSTRING(REVERSE(physical_name), 1, CHARINDEX('\', REVERSE(physical_name)) - 1))
+ '''),'
from sys.master_files
WHERE type = 0
AND database_id = DB_ID(@DB)
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1) + ' AS SNAPSHOT OF ['
+ @DB + ']'
EXEC (@SQL)
GO
AS
IF NOT EXISTS(SELECT *
FROM master.sys.DATABASES
WHERE name = @DB
AND source_database_id IS NULL
AND state_desc = 'ONLINE')
BEGIN
RAISERROR('Le nom de base %s n''existe pas sur ce serveur ou n''est pas en état copiable.', 16, 1, @DB);
RETURN;
END;
IF RIGHT(@PATH, 1) = '\'
SET @PATH = @PATH + '\';
DECLARE @T TABLE (file_exists bit, file_is_dir bit, parent_dir_exists bit);
INSERT INTO @T
EXEC master.sys.xp_fileexist @PATH;
IF NOT EXISTS(SELECT 0
FROM @T
WHERE file_is_dir = 1)
BEGIN
RAISERROR('Le chemin passé en arguement, n''est pas un répertoire valide.' , 16, 1);
RETURN
END
DECLARE @SQL VARCHAR(MAX);
SET @SQL = 'CREATE DATABASE [' + @DB +'_SNAP_'
+ REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(NVARCHAR(23), CURRENT_TIMESTAMP, 121), '-', ''), ' ', '_'), ':', ''), '.', '_')
+ '] ON '
SELECT @SQL = @SQL + '( NAME = ' + name +', FILENAME = '''
+ @PATH + REVERSE(SUBSTRING(REVERSE(physical_name), 1, CHARINDEX('\', REVERSE(physical_name)) - 1))
+ '''),'
from sys.master_files
WHERE type = 0
AND database_id = DB_ID(@DB)
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 1) + ' AS SNAPSHOT OF ['
+ @DB + ']'
EXEC (@SQL)
GO
Exemple de lancement :
EXEC sp__DB_SNAP 'DB_ETG', 'C:\SNAP\'
Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES
Expert S.G.B.D relationnelles et langage S.Q.L
Moste Valuable Professionnal Microsoft SQL Server
Société SQLspot : modélisation, conseil, formation,
optimisation, audit, tuning, administration SGBDR
Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.
Expert S.G.B.D relationnelles et langage S.Q.L
Moste Valuable Professionnal Microsoft SQL Server
Société SQLspot : modélisation, conseil, formation,
optimisation, audit, tuning, administration SGBDR
Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.
L’entreprise SQL Spot
Le site web sur le SQL et les SGBDR
