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
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
GO
========================================================
2.建立sqlcmd(sqlcmd用法請參考:使用 sqlcmd 執行 Transact-SQL 指令碼檔案 )的command檔(比如我存成RunDBBackup.cmd，設定要執行的script file 及 輸出的檔名)，如下，
sqlcmd -i d:\backup.sql -o E:\DB_Backup\result.txt