28 lines
895 B
Transact-SQL
28 lines
895 B
Transact-SQL
USE [master]
|
|
GO
|
|
|
|
|
|
BEGIN TRY
|
|
|
|
DECLARE @debug INT = 0
|
|
|
|
SELECT @@SERVERNAME AS 'DbServer'
|
|
|
|
|
|
/* select 'dropCreateScript' for desired snapshot, copy into new window, change 1 to 0, run */
|
|
|
|
SELECT @@SERVERNAME AS 'DbServer', s.name AS 'SnapShot', s.database_id AS SN_Id, d.name AS 'Source_Database', d.database_id AS DB_Id, s.create_date,
|
|
'EXECUTE [master].dbo.p_DbTool__ReCreate_Snapshot ' + CAST(@debug AS VARCHAR(1)) + ', '''+ d.name + ''', ''' + s.name + ''';' AS ReCreateScript,
|
|
'EXECUTE [master].dbo.p_DbTool__Drop_Snapshot ' + CAST(@debug AS VARCHAR(1)) + ', ''' + s.name + ''';' AS DropScript
|
|
FROM sys.databases s
|
|
INNER JOIN sys.databases d ON d.database_id = s.source_database_id WHERE NOT (s.source_database_id IS NULL) --ORDER BY s.name
|
|
ORDER BY @@SERVERNAME, REPLACE(REPLACE(d.name, '__00', ''), '__01', ''), s.name
|
|
|
|
END TRY
|
|
|
|
|
|
BEGIN CATCH
|
|
EXECUTE [master].dbo.sp_RethrowError
|
|
END CATCH
|
|
|