The Will Will Web

以下是一次備份所有資料庫的懶人指令碼(T-SQL)：

-- 取得資料庫本機的 Backup 資料目錄
DECLARE @backup_path nvarchar(256);
DECLARE @backup_file nvarchar(256);
SET @backup_path = (
    SELECT
        SUBSTRING(
            SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1),
            1,
            CHARINDEX(N'\DATA\', UPPER(physical_name)) - 1
        ) + '\Backup\'
    FROM master.sys.master_files
    WHERE database_id = 1 AND file_id = 1
);

-- 用來暫存備份指令的變數
DECLARE @backup_sql nvarchar(max)
SET @backup_sql = '';

-- 用來暫存資料庫名稱的變數
DECLARE @dbname nvarchar(256)

-- 宣告 cursor 以取得資料庫名稱
DECLARE icur CURSOR STATIC FOR select name from sys.databases where name != 'tempdb'

OPEN icur

FETCH NEXT FROM icur INTO @dbname
WHILE(@@FETCH_STATUS=0)
BEGIN
    set @backup_file = @backup_path + @dbname + '.bak'
    set @backup_sql  = 'BACKUP DATABASE ['+@dbname+']  TO DISK='''+@backup_file+''''

    PRINT (@backup_sql)
    EXEC (@backup_sql)

    FETCH NEXT FROM icur INTO @dbname
END

CLOSE icur
DEALLOCATE icur

備份完成的資料庫預設會擺在 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup 目錄下。