sqlcmd 來完成資料庫的備份

http://www.dotblogs.com.tw/rainmaker/archive/2013/08/07/113631.aspx

SET QUOTED_IDENTIFIER OFF

SET NOCOUNT ON

PRINT 'Process Name : Full SQL Server System & Application DBs Backup to BAK file'

DECLARE

@FIRST_BACKUP INTEGER,

@DB_NAME VARCHAR(128),

@CMD_LINE VARCHAR(512),

@BACKUP_PATH VARCHAR(64),

@DB_REOCVERY_MODE NVARCHAR(128),

@DB_STATUS NVARCHAR(128)

-- Must change @BACKUP_PATH

-- 指定存放備份檔的路徑

SET @BACKUP_PATH = 'E:\DB_Backup\'

--取得要備份的資料庫資料，一些練習用的資料庫可以不用備份

DECLARE SERVER_DB_CURSOR CURSOR

FOR

SELECT

name

,convert(nvarchar(128),(DATABASEPROPERTYEX ( name , 'Recovery' )))

,convert(nvarchar(128),(DATABASEPROPERTYEX ( name , 'Status' )))

FROM master..sysdatabases (nolock)

WHERE name != 'tempdb' and name != 'pubs' and name != 'Northwind' and name != 'model'

ORDER BY dbid

-- 開始依CURSOR 執行備份

OPEN SERVER_DB_CURSOR

FETCH NEXT FROM SERVER_DB_CURSOR INTO @DB_NAME, @DB_REOCVERY_MODE, @DB_STATUS

WHILE ( @@FETCH_STATUS != -1 )

BEGIN

SELECT "Backup Process Begin At" = GETDATE(), "Backup DB Is " = @DB_NAME

IF ( @DB_STATUS = 'ONLINE' )

BEGIN

IF (@DB_REOCVERY_MODE = 'SIMPLE')

BEGIN

SELECT @CMD_LINE = 'BACKUP DATABASE ' + @DB_NAME + ' TO DISK = ' + '''' + @BACKUP_PATH + @DB_NAME + '.BAK' + '''' + ' WITH INIT, SKIP'

PRINT (@CMD_LINE)

EXEC (@CMD_LINE)

END

ELSE

BEGIN

SELECT @CMD_LINE = 'BACKUP DATABASE ' + @DB_NAME + ' TO DISK = ' + '''' + @BACKUP_PATH + @DB_NAME + '.BAK' + '''' + ' WITH INIT, SKIP'

PRINT (@CMD_LINE)

EXEC (@CMD_LINE)

SELECT @CMD_LINE = 'BACKUP LOG ' + @DB_NAME + ' TO DISK = ' + '''' + @BACKUP_PATH + @DB_NAME + '.TRN' + '''' + ' WITH INIT, SKIP'

PRINT (@CMD_LINE)

EXEC (@CMD_LINE)

END

SELECT "Process End at" = GETDATE()

FETCH NEXT FROM SERVER_DB_CURSOR INTO @DB_NAME, @DB_REOCVERY_MODE, @DB_STATUS

END

CLOSE SERVER_DB_CURSOR

DEALLOCATE SERVER_DB_CURSOR

SET QUOTED_IDENTIFIER ON

SET NOCOUNT OFF

========================================================

2.建立sqlcmd(sqlcmd用法請參考:使用 sqlcmd 執行 Transact-SQL 指令碼檔案 )的command檔(比如我存成RunDBBackup.cmd，設定要執行的script file 及輸出的檔名)，如下，

sqlcmd -i d:\backup.sql -o E:\DB_Backup\result.txt