[SQL]SQL Server Express透過工作排程 + sqlcmd 來完成資料庫的備份

2013-08-07

19186
0
SQL

當您的SQL Server是Express版本，要如何做資料庫備份呢?
我們可以透過工作排程 + sqlcmd 來完成哦!

最近有台SQL Server Express需要備份資料庫，但因為Express版不能使用Agent(其實可透過其他非Express版本的SQL Server Agent來備份它也可以)。

所以可以透過Windows的工作排程器 + sqlcmd 來完成資料庫的備份。

1.準備要備份的Script，如下，

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