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
| CREATE PROCEDURE P_SAVE_ALL_BASES
@path VARCHAR(256)
AS
DECLARE C_DB CURSOR
FOR
SELECT CATALOG_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME NOT IN ('master', 'tempdb', 'model', 'msdb', 'pubs', 'Northwind')
DECLARE @dbname VARCHAR(128)
DECLARE @query VARCHAR(2000)
OPEN C_DB
FETCH C_DB INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @query = 'master.dbo.sp_addumpdevice ''disk'', ''' +@dbname+''', '''
+ @path + @dbname + '.dbb'''
EXEC (@query)
IF @@ERROR <> 0
PRINT 'ERREUR : '+@query
ELSE
PRINT 'OK : '+@query
SET @query = 'BACKUP DATABASE ' + @dbname + ' TO ' + @dbname
EXEC (@query)
IF @@ERROR <> 0
PRINT 'ERREUR : '+@query
ELSE
PRINT 'OK : '+@query
FETCH C_DB INTO @dbname
END
CLOSE C_DB
DEALLOCATE C_DB
GO |
Partager