亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

批量備份數據庫

系統 1664 0

SQL Server 游標運用:批量備份數據庫

2014-02-10 14:59 by 聽風吹雨,? 590 ?閱讀,? 8 ?評論,? 收藏 ,? 編輯

一、 背景

  在公司的內網有臺數據庫的測試服務器,這臺服務器是提供給開發人員使用的,在上面有很多的數據庫,有些是臨時系統用到的數據庫,這些數據庫有一個共同點:數據庫表結構比較重要,數據庫只有一些測試數據,也就是說這些數據庫都很小,而整臺服務器的數據庫又非常多;

  現在有這樣一個需求,希望間隔一段時間就備份所有數據庫,所以這里寫了這篇文章,這也是另外一篇文章 SQL Server 批量備份數據庫(主分區) 的基礎;

?

二、 實現過程

下面是實現批量備份數據庫的3種方式,大家可以細細體會其中的差別:

1) 實現方式1:使用游標

2) 實現方式2:使用拼湊SQL的方式

3) 實現方式3:使用存儲過程sp_MSforeachdb_Filter(以sp_MSforeachdb為基礎)

?

(一)? 實現方式1:使用游標

執行下面的SQL腳本就可以備份當前數據庫實例的所有數據庫(除了系統數據庫);

          
            --
          
          
             =============================================
          
          
            

--
          
          
             Author:      <聽風吹雨>
          
          
            

--
          
          
             Blog:        <http://gaizai.cnblogs.com/>
          
          
            

--
          
          
             Create date: <2011/12/03>
          
          
            

--
          
          
             Description: <批量備份數據庫>
          
          
            

--
          
          
             =============================================
          
          
            DECLARE
          
          
            @FileName
          
          
            VARCHAR
          
          (
          
            200
          
          
            ),

      
          
          
            @CurrentTime
          
          
            VARCHAR
          
          (
          
            50
          
          
            ),

      
          
          
            @DBName
          
          
            VARCHAR
          
          (
          
            100
          
          
            ),

      
          
          
            @SQL
          
          
            VARCHAR
          
          (
          
            1000
          
          
            )




          
          
            SET
          
          
            @CurrentTime
          
          
            =
          
          
            CONVERT
          
          (
          
            CHAR
          
          (
          
            8
          
          ),
          
            GETDATE
          
          (),
          
            112
          
          ) 
          
            +
          
          
            CAST
          
          (
          
            DATEPART
          
          (hh, 
          
            GETDATE
          
          ()) 
          
            AS
          
          
            VARCHAR
          
          ) 
          
            +
          
          
            CAST
          
          (
          
            DATEPART
          
          (mi, 
          
            GETDATE
          
          ()) 
          
            AS
          
          
            VARCHAR
          
          
            )




          
          
            DECLARE
          
           CurDBName 
          
            CURSOR
          
          
            FOR
          
          
            SELECT
          
           NAME 
          
            FROM
          
           Master..SysDatabases 
          
            where
          
           dbid
          
            >
          
          
            4
          
          
            OPEN
          
          
             CurDBName


          
          
            FETCH
          
          
            NEXT
          
          
            FROM
          
           CurDBName 
          
            INTO
          
          
            @DBName
          
          
            WHILE
          
          
            @@FETCH_STATUS
          
          
            =
          
          
            0
          
          
            BEGIN
          
          
            --
          
          
            Execute Backup
          
          
            SET
          
          
            @FileName
          
          
            =
          
          
            '
          
          
            E:\DBBackup\
          
          
            '
          
          
            +
          
          
            @DBName
          
          
            +
          
          
            '
          
          
            _
          
          
            '
          
          
            +
          
          
            @CurrentTime
          
          
            SET
          
          
            @SQL
          
          
            =
          
          
            '
          
          
            BACKUP DATABASE [
          
          
            '
          
          
            +
          
          
            @DBName
          
          
            +
          
          
            '
          
          
            ] TO DISK = 
          
          
            '''
          
          
            +
          
          
            @FileName
          
          
            +
          
          
            '
          
          
            .bak
          
          
            '
          
          
            +
          
          
            '''
          
          
             WITH NOINIT, NOUNLOAD, NAME = N
          
          
            '''
          
          
            +
          
          
            @DBName
          
          
            +
          
          
            '
          
          
            _backup
          
          
            ''
          
          
            , NOSKIP, STATS = 10, NOFORMAT
          
          
            '
          
          
            EXEC
          
          (
          
            @SQL
          
          
            )



    
          
          
            --
          
          
            Get Next DataBase
          
          
            FETCH
          
          
            NEXT
          
          
            FROM
          
           CurDBName 
          
            INTO
          
          
            @DBName
          
          
            END
          
          
            CLOSE
          
          
             CurDBName


          
          
            DEALLOCATE
          
           CurDBName
        

執行完上面的SQL腳本,會在E:\DBBackup的目錄下生成類似下圖的備份文件:

clip_image002

(Figure1:數據庫備份文件)

?

(二)? 實現方式2:使用拼湊SQL的方式

          
            --
          
          
            使用拼湊SQL的方式
          
          
            DECLARE
          
          
            @SQL
          
          
            VARCHAR
          
          (
          
            MAX
          
          
            )




          
          
            SELECT
          
          
            @SQL
          
          
            =
          
          
            COALESCE
          
          (
          
            @SQL
          
          ,
          
            ''
          
          ) 
          
            +
          
          
            '
          
          
            

BACKUP DATABASE 
          
          
            '
          
          
            +
          
          
            QUOTENAME
          
          (name,
          
            '
          
          
            []
          
          
            '
          
          
            ) 


          
          
            +
          
          
            '
          
          
             TO DISK = 
          
          
            ''
          
          
            E:\DBBackup\
          
          
            '
          
          
            +
          
           name 
          
            +
          
          
            '
          
          
            _
          
          
            '
          
          
            +
          
          
            CONVERT
          
          (
          
            CHAR
          
          (
          
            8
          
          ),
          
            GETDATE
          
          (),
          
            112
          
          ) 
          
            +
          
          
            CAST
          
          (
          
            DATEPART
          
          (hh, 
          
            GETDATE
          
          ()) 
          
            AS
          
          
            VARCHAR
          
          ) 
          
            +
          
          
            CAST
          
          (
          
            DATEPART
          
          (mi, 
          
            GETDATE
          
          ()) 
          
            AS
          
          
            VARCHAR
          
          ) 
          
            +
          
          
            '
          
          
            .bak
          
          
            '
          
          
            +
          
          
            '''
          
          
             WITH NOINIT, NOUNLOAD, NAME = N
          
          
            '''
          
          
            +
          
           name 
          
            +
          
          
            '
          
          
            _backup
          
          
            ''
          
          
            , NOSKIP, STATS = 10, NOFORMAT
          
          
            '
          
          
            FROM
          
           sys.databases 
          
            WHERE
          
           database_id 
          
            >
          
          
            4
          
          
            AND
          
           name 
          
            like
          
          
            '
          
          
            %%
          
          
            '
          
          
            AND
          
           state 
          
            =
          
          
            0
          
          
            PRINT
          
          (
          
            @SQL
          
          
            )


          
          
            EXECUTE
          
          (
          
            @SQL
          
          )
        

生成的腳本如Figure2所示,如果想腳本更加美觀,可以加上GO語句,如Figure3所示:

clip_image004

(Figure2:生成的T-SQL腳本)

clip_image006

(Figure3:生成的T-SQL腳本)

?

(三)? 實現方式3:使用存儲過程sp_MSforeachdb_Filter(以sp_MSforeachdb為基礎)

通過查看系統存儲過程sp_MSforeachdb的T-SQL源代碼可以發現是沒有提供@whereand參數可以過濾數據庫的,參考系統存儲過程sp_MSforeachtable后,在sp_MSforeachdb的基礎上創建帶@whereand參數的存儲過程sp_MSforeachdb_Filter,這樣你就可以讓SQL在指定的數據庫上執行;

          
            --
          
          
             =============================================
          
          
            

--
          
          
             Author:      <聽風吹雨>
          
          
            

--
          
          
             Blog:        <http://gaizai.cnblogs.com/>
          
          
            

--
          
          
             Create date: <2013.05.06>
          
          
            

--
          
          
             Description: <擴展sp_MSforeachdb,增加@whereand參數>
          
          
            

--
          
          
             =============================================
          
          
            USE
          
          
            [
          
          
            master
          
          
            ]
          
          
            GO
          
          
            SET
          
           ANSI_NULLS 
          
            ON
          
          
            GO
          
          
            SET
          
           QUOTED_IDENTIFIER 
          
            OFF
          
          
            GO
          
          
            create
          
          
            proc
          
          
            [
          
          
            dbo
          
          
            ]
          
          .
          
            [
          
          
            sp_MSforeachdb_Filter
          
          
            ]
          
          
            @command1
          
          
            nvarchar
          
          (
          
            2000
          
          ), 
          
            @replacechar
          
          
            nchar
          
          (
          
            1
          
          ) 
          
            =
          
           N
          
            '
          
          
            ?
          
          
            '
          
          , 
          
            @command2
          
          
            nvarchar
          
          (
          
            2000
          
          ) 
          
            =
          
          
            null
          
          , 
          
            @command3
          
          
            nvarchar
          
          (
          
            2000
          
          ) 
          
            =
          
          
            null
          
          
            ,

    
          
          
            @whereand
          
          
            nvarchar
          
          (
          
            2000
          
          ) 
          
            =
          
          
            null
          
          ,
          
            @precommand
          
          
            nvarchar
          
          (
          
            2000
          
          ) 
          
            =
          
          
            null
          
          , 
          
            @postcommand
          
          
            nvarchar
          
          (
          
            2000
          
          ) 
          
            =
          
          
            null
          
          
            as
          
          
            set
          
          
             deadlock_priority low

    

    
          
          
            /*
          
          
             This proc returns one or more rows for each accessible db, with each db defaulting to its own result set 
          
          
            */
          
          
            /*
          
          
             @precommand and @postcommand may be used to force a single result set via a temp table. 
          
          
            */
          
          
            /*
          
          
             Preprocessor won't replace within quotes so have to use str(). 
          
          
            */
          
          
            declare
          
          
            @inaccessible
          
          
            nvarchar
          
          (
          
            12
          
          ), 
          
            @invalidlogin
          
          
            nvarchar
          
          (
          
            12
          
          ), 
          
            @dbinaccessible
          
          
            nvarchar
          
          (
          
            12
          
          
            )

    
          
          
            select
          
          
            @inaccessible
          
          
            =
          
          
            ltrim
          
          (
          
            str
          
          (
          
            convert
          
          (
          
            int
          
          , 
          
            0x03e0
          
          ), 
          
            11
          
          
            ))

    
          
          
            select
          
          
            @invalidlogin
          
          
            =
          
          
            ltrim
          
          (
          
            str
          
          (
          
            convert
          
          (
          
            int
          
          , 
          
            0x40000000
          
          ), 
          
            11
          
          
            ))

    
          
          
            select
          
          
            @dbinaccessible
          
          
            =
          
           N
          
            '
          
          
            0x80000000
          
          
            '
          
          
            /*
          
          
             SQLDMODbUserProf_InaccessibleDb; the negative number doesn't work in convert() 
          
          
            */
          
          
            if
          
           (
          
            @precommand
          
          
            is
          
          
            not
          
          
            null
          
          
            )

        
          
          
            exec
          
          (
          
            @precommand
          
          
            )

 

    
          
          
            declare
          
          
            @origdb
          
          
            nvarchar
          
          (
          
            128
          
          
            )

    
          
          
            select
          
          
            @origdb
          
          
            =
          
          
            db_name
          
          
            ()

 

    
          
          
            /*
          
          
             If it's a single user db and there's an entry for it in sysprocesses who isn't us, we can't use it. 
          
          
            */
          
          
            /*
          
          
             Create the select 
          
          
            */
          
          
            exec
          
          (N
          
            '
          
          
            declare hCForEachDatabase cursor global for select name from master.dbo.sysdatabases d 
          
          
            '
          
          
            +
          
          
            

            N
          
          
            '
          
          
             where (d.status & 
          
          
            '
          
          
            +
          
          
            @inaccessible
          
          
            +
          
           N
          
            '
          
          
             = 0)
          
          
            '
          
          
            +
          
          
            

            N
          
          
            '
          
          
             and (DATABASEPROPERTY(d.name, 
          
          
            ''
          
          
            issingleuser
          
          
            ''
          
          
            ) = 0 and (has_dbaccess(d.name) = 1))
          
          
            '
          
          
            +
          
          
            @whereand
          
          
            )

 

    
          
          
            declare
          
          
            @retval
          
          
            int
          
          
            select
          
          
            @retval
          
          
            =
          
          
            @@error
          
          
            if
          
           (
          
            @retval
          
          
            =
          
          
            0
          
          
            )

        
          
          
            exec
          
          
            @retval
          
          
            =
          
           sys.sp_MSforeach_worker 
          
            @command1
          
          , 
          
            @replacechar
          
          , 
          
            @command2
          
          , 
          
            @command3
          
          , 
          
            1
          
          
            if
          
           (
          
            @retval
          
          
            =
          
          
            0
          
          
            and
          
          
            @postcommand
          
          
            is
          
          
            not
          
          
            null
          
          
            )

        
          
          
            exec
          
          (
          
            @postcommand
          
          
            )

 

   
          
          
            declare
          
          
            @tempdb
          
          
            nvarchar
          
          (
          
            258
          
          
            )

   
          
          
            SELECT
          
          
            @tempdb
          
          
            =
          
          
            REPLACE
          
          (
          
            @origdb
          
          , N
          
            '
          
          
            ]
          
          
            '
          
          , N
          
            '
          
          
            ]]
          
          
            '
          
          
            )

   
          
          
            exec
          
           (N
          
            '
          
          
            use 
          
          
            '
          
          
            +
          
           N
          
            '
          
          
            [
          
          
            '
          
          
            +
          
          
            @tempdb
          
          
            +
          
           N
          
            '
          
          
            ]
          
          
            '
          
          
            )

 

    
          
          
            return
          
          
            @retval
          
        

上面的存儲過程sp_MSforeachdb_Filter與sp_MSforeachdb的區別有以下兩點:

clip_image008

(Figure4:添加內容1)

clip_image010

(Figure5:添加內容2)

而且需要注意在創建存儲過程的時候需要設置SET QUOTED_IDENTIFIER OFF,當 SET QUOTED_IDENTIFIER 為 ON 時,標識符可以由雙引號分隔,而文字必須由單引號分隔;當 SET QUOTED_IDENTIFIER 為 OFF 時,標識符不可加引號,且必須符合所有 Transact-SQL 標識符規則。具體可以參考: SET QUOTED_IDENTIFIER (Transact-SQL)

調用sp_MSforeachdb_Filter實現批量備份數據庫的T-SQL如下所示:

          
            --
          
          
            使用更新的存儲過程sp_MSforeachdb_Filter(以sp_MSforeachdb為基礎)
          
          
            USE
          
          
            [
          
          
            master
          
          
            ]
          
          
            GO
          
          
            DECLARE
          
          
            @SQL
          
          
            NVARCHAR
          
          (
          
            MAX
          
          
            )


          
          
            SELECT
          
          
            @SQL
          
          
            =
          
          
            COALESCE
          
          (
          
            @SQL
          
          ,
          
            ''
          
          ) 
          
            +
          
          
            '
          
          
            

BACKUP DATABASE [?] 

TO DISK = 
          
          
            ''
          
          
            E:\DBBackup\?_
          
          
            '
          
          
            +
          
          
            CONVERT
          
          (
          
            CHAR
          
          (
          
            8
          
          ),
          
            GETDATE
          
          (),
          
            112
          
          ) 
          
            +
          
          
            CAST
          
          (
          
            DATEPART
          
          (hh, 
          
            GETDATE
          
          ()) 
          
            AS
          
          
            VARCHAR
          
          ) 
          
            +
          
          
            CAST
          
          (
          
            DATEPART
          
          (mi, 
          
            GETDATE
          
          ()) 
          
            AS
          
          
            VARCHAR
          
          ) 
          
            +
          
          
            '
          
          
            .bak
          
          
            ''
          
          
             

WITH NOINIT, NOUNLOAD, NAME = N
          
          
            ''
          
          
            ?_backup
          
          
            ''
          
          
            , NOSKIP, STATS = 10, NOFORMAT
          
          
            '
          
          
            PRINT
          
          
            @SQL
          
          
            --
          
          
            過濾數據庫
          
          
            EXEC
          
          
            [
          
          
            sp_MSforeachdb_Filter
          
          
            ]
          
          
            @command1
          
          
            =
          
          
            @SQL
          
          
            ,


          
          
            @whereand
          
          
            =
          
          " 
          
            and
          
          
            [
          
          
            name
          
          
            ]
          
          
            not
          
          
            in
          
          (
          
            '
          
          
            tempdb
          
          
            '
          
          ,
          
            '
          
          
            master
          
          
            '
          
          ,
          
            '
          
          
            model
          
          
            '
          
          ,
          
            '
          
          
            msdb
          
          
            '
          
          ) "
        

執行上面的存儲過程就可以備份所有數據庫(系統數據庫除外,想要過濾數據庫可以填寫@whereand參數的條件),執行上面SQL的效果如下圖所示:

clip_image011

(Figure6:錯誤信息)

如果沒有設置SET QUOTED_IDENTIFIER 這個選項為 OFF ,那么在調用存儲過程sp_MSforeachdb_Filter的時候會出現下圖所示的錯誤信息:

clip_image013

(Figure7:錯誤信息)

如果想查看存儲過程sp_MSforeachdb的詳細代碼,可以在通過訪問路徑:數據庫-可編程性-存儲過程-系統存儲過程-sp_MSforeachdb找到,或者通過下面的腳本查看:

          
            --
          
          
            顯示規則、默認值、未加密的存儲過程、用戶定義函數、觸發器或視圖的文本
          
          
            EXEC
          
           sp_helptext N
          
            '
          
          
            sp_MSforeachdb
          
          
            '
          
          ;
        

更多批量備份數據庫的文章可以參考:

SQL Server 批量備份數據庫(主分區)

SQL Server批量創建作業(備份主分區)

?

一、 參考文獻

SET QUOTED_IDENTIFIER (Transact-SQL) (英文)

SET QUOTED_IDENTIFIER (Transact-SQL) (中文)

-------------------華麗分割線-------------------

批量備份數據庫


更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯系: 360901061

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。

【本文對您有幫助就好】

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會非常 感謝您的哦!!!

發表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 久久久久久久久久免费视频 | 离线枕边人在线观看 | 模特啪啪 | 欧洲成人爽视频在线观看 | 日本手机在线视频 | 色图一区| 99热国产这里只有精品9九 | 免费h片| 亚洲国产精品综合久久20 | 亚洲毛片视频 | 免费国产午夜高清在线视频 | 国产亚洲自在精品久久 | 久久五月天婷婷 | 四虎影院在线视频 | 夜夜躁狠狠躁日日躁视频 | 久久99国产精品视频 | 国产一级在线视频 | 欧美成人丝袜视频在线观看 | 天天射日日操 | 美国毛片一级e片黑人片 | 国产在线一区二区 | 亚洲黄色大片 | 国产精品尹人在线观看免费 | 伊人久久婷婷丁香六月综合基地 | 亚洲视频中文字幕在线 | a毛片免费全部在线播放毛 a毛片免费在线观看 | 国产成+人+综合+亚洲专 | 福利视频不卡 | 久操视频在线观看 | 日本激情啪啪 | 欧美日韩亚洲在线观看 | 午夜精品久久久久久久2023 | 国产手机在线视频放线视频 | 特级黄色| 99视频在线免费观看 | 亚洲精品在线播放视频 | 国产福利在线 | 欧美精品成人a多人在线观看 | 色综合天天综合网国产人 | www.九九| 久久久久久久综合 |