SQL:StoredProcedure:MyDataBaseBackup

出自ProgWiki

用途

• 資料庫備份

程式碼

/* 物件:  StoredProcedure [dbo].[MyDataBaseBackup] */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROC [dbo].[MyDataBaseBackup]
(
	@PATH AS NVARCHAR(255)
	,@db AS NVARCHAR(255) = null
)
AS
BEGIN
	DECLARE @datestr NVARCHAR(255)
	DECLARE @dbname NVARCHAR(255)
	DECLARE @cmd_backup_data NVARCHAR(255)
	SET @datestr = CONVERT(NVARCHAR, GETDATE(), 112)
 
	IF (not @db IS null) and (@db<>'')
		BEGIN
			SET @dbname = @db
			SET @cmd_backup_data = 'backup database '+@dbname+' to disk='''+@PATH + @dbname + @datestr + '_data.bak'''
			EXEC SP_EXECUTESQL @cmd_backup_data
		END
	ELSE
		BEGIN
			DECLARE db_cur CURSOR FOR
			SELECT name FROM master..sysdatabases WHERE dbid > 4 
 
			OPEN db_cur
			FETCH NEXT FROM db_cur INTO @dbname
				WHILE @@FETCH_STATUS = 0
					BEGIN
						SET @cmd_backup_data = 'backup database '+@dbname+' to disk='''+@PATH + @dbname + @datestr + '_data.bak'''
						EXEC SP_EXECUTESQL @cmd_backup_data
 
						FETCH NEXT FROM db_cur INTO @dbname
					END
			CLOSE db_cur
			DEALLOCATE db_cur
		END
END

用法

• 備份所有資料庫（備份所有的資料庫到 D:\BACKUP\MS-SQL\）

EXEC [MyDataBaseBackup] 'D:\BACKUP\MS-SQL\'

• 備份指定的資料庫（備份名為 MyDB 的資料庫到 D:\BACKUP\MS-SQL\）

EXEC [MyDataBaseBackup] 'D:\BACKUP\MS-SQL\','MyDB'