SQL 實作定期備份資料庫

出自:http://www.cnblogs.com/jx270/archive/2013/03/10/2952568.html

--------------------------------------------------------------------------
--Subject:資料庫批量備份存儲過程(判斷盤符、路徑，錯誤盤符傳回，不存在的路徑自動建立)
--适用環境：SQL Server 2005及以上版本(主要用于作業中調用以及程式開發中調用)
--------------------------------------------------------------------------
---通過使用sp_configure啟用‘xp_cmdshell’
USE [master]
GO
sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'xp_cmdshell',1
reconfigure
go
---下面開始備份資料庫操作
 
USE master
GO
--1.周期性備份資料庫代碼(保留原來備份的):
--備份檔案名為：原資料庫名稱+'_'+備份日期.bak
IF OBJECT_ID('sp_backupdatabase') IS NOT NULL
    DROP PROC sp_backupdatabase
GO
CREATE PROC sp_backupdatabase 
@path NVARCHAR(100)--路徑
AS
--路徑名格式标準化
IF RIGHT(@path,1)<>'\' SET @[email protected]+'\'
--擷取檔案夾資訊
DECLARE @t TABLE(id INT IDENTITY,a INT,b INT,c INT)
DECLARE @fpath NVARCHAR(3)
SET @fpath=LEFT(@path,3)
INSERT @t EXEC master..xp_fileexist @fpath
INSERT @t EXEC master..xp_fileexist @path
--如果指定盤符有誤不存在，則傳回錯誤提示：
IF EXISTS(SELECT 1 FROM @t WHERE id=1 AND c=0)
BEGIN
    RAISERROR(N'輸入的盤符不存在，請重新輸入!',16,1)
    RETURN
END
--如果不存在指定的檔案夾，則建立:
ELSE IF EXISTS(SELECT 1 FROM @t WHERE b=0 AND id=2)
BEGIN
    DECLARE @mddir NVARCHAR(100)
    SET @mddir='md '[email protected]
    EXEC MASTER..xp_cmdshell @mddir
END
--開始備份資料庫到指定的目錄
DECLARE @s nvarchar(4000)
SELECT @s=ISNULL(@s+';','')
        +N'BACKUP database ['+name+'] TO DISK = '''
        [email protected]+name+'_'+CONVERT(NVARCHAR(8),getdate(),112)+N'.bak'''
FROM master..sysdatabases 
WHERE name NOT IN('master','tempdb','model','msdb','pubs')--這裡篩選不參加備份的資料庫
AND NAME ='book09'--此處可以指定任意某個資料庫，也可以屏蔽該行則全部備份
EXEC(@S)
GO
--調用方法：
EXEC sp_backupdatabase 'e:\DB\ByDate\testBackupDB'
/*
--傳回資訊：
已為資料庫 'book09'，檔案 'book09_Data' (位于檔案 1 上)處理了 224 頁。
已為資料庫 'book09'，檔案 'book09_Log' (位于檔案 1 上)處理了 1 頁。
BACKUP DATABASE 成功處理了 225 頁，花費 0.358 秒(4.910 MB/秒)。
--備份後的檔案清單：
book09_20130310.bak 
*/
 
 
--2.周期性備份資料庫代碼(自動删除原備份檔案):
--備份檔案名為：原資料庫名稱.bak
IF OBJECT_ID('sp_backupdatabase') IS NOT NULL
    DROP PROC sp_backupdatabase
GO
CREATE PROC sp_backupdatabase 
@path NVARCHAR(100)--路徑
AS
--路徑名格式标準化
IF RIGHT(@path,1)<>'\' SET @[email protected]+'\'
--擷取檔案夾資訊
DECLARE @t TABLE(id INT IDENTITY,a INT,b INT,c INT)
DECLARE @fpath NVARCHAR(3)
SET @fpath=LEFT(@path,3)
INSERT @t EXEC master..xp_fileexist @fpath
INSERT @t EXEC master..xp_fileexist @path
--如果指定盤符有誤不存在，則傳回錯誤提示：
IF EXISTS(SELECT 1 FROM @t WHERE id=1 AND c=0)
BEGIN
    RAISERROR(N'輸入的盤符不存在，請重新輸入!',16,1)
    RETURN
END
--如果不存在指定的檔案夾，則建立:
ELSE IF EXISTS(SELECT 1 FROM @t WHERE b=0 AND id=2)
BEGIN
    DECLARE @mddir NVARCHAR(100)
    SET @mddir='md '[email protected]
    EXEC master..xp_cmdshell @mddir
END
--開始備份資料庫到指定的目錄
DECLARE @s nvarchar(4000)
SELECT @s=ISNULL(@s+';','')
        +N'BACKUP database ['+name+'] TO DISK = '''
        [email protected]+name+N'.bak''  WITH INIT'
FROM master..sysdatabases 
WHERE name NOT IN('master','tempdb','model','msdb','pubs')--這裡篩選不參加備份的資料庫
AND NAME ='book09'--此處可以指定任意某個資料庫，也可以屏蔽該行則全部備份
EXEC(@S)
GO
--調用方法：
EXEC sp_backupdatabase 'e:\DB\ByDate\testBackupDB'
/*
--傳回資訊：
已為資料庫 'book09'，檔案 'book09_Data' (位于檔案 1 上)處理了 224 頁。
已為資料庫 'book09'，檔案 'book09_Log' (位于檔案 1 上)處理了 1 頁。
BACKUP DATABASE 成功處理了 225 頁，花費 0.456 秒(3.854 MB/秒)。
--備份後的檔案清單：
book09.bak 
*/
           

關于連接配接遠端資料庫的方法：

本人試過了，跨ip通路遠端資料庫的情況，是可以實作的,下面是sql語句：

--假設遠端機器IP為61.188.*.* (ip及*号請自己換成對應的ip)

--建立連接配接伺服器
EXEC sp_addlinkedserver '61.188.*.*','SQL Server'
 
--建立連結伺服器上遠端登入之間的映射
EXEC sp_addlinkedsrvlogin '61.188.*.*','false','sa','賬号','密碼'
 
--查詢資料
select top 10 * from [61.188.*.*].資料庫名.dbo.表名
           

前面2行執行一次就可以了，

然後直接寫下面的其他查詢語句，就可以查詢到對應的伺服器上面的資料了

 下面附一篇關于遠端連接配接伺服器的示例SQL:

SQL Server 遠端連結伺服器詳細配置

遠端連結伺服器詳細配置
--建立連接配接伺服器
EXEC sp_addlinkedserver  '遠端伺服器IP','SQL Server'
--标注存儲
EXEC sp_addlinkedserver
@server = 'server', --連結伺服器的本地名稱。也允許使用執行個體名稱，例如MYSERVER\SQL1
@srvproduct = 'product_name' --OLE DB資料源的産品名。對于SQL Server執行個體來說，product_name是'SQL Server'
, @provider = 'provider_name' --這是OLE DB通路接口的唯一可程式設計辨別。當沒有指定它時，通路接口名稱是 SQL Server資料源。SQL Server顯式的provider_name是 SQLNCLI（Microsoft SQL Native Client OLE DB Provider）。Oracler的是 MSDAORA，Oracle 8或更高版本的是OraOLEDB.Oracle。MS Access和MS Excel的是 Microsoft.Jet.OLEDB.4.0。IBM DB2的是DB2OLEDB，以及ODBC資料源的是MSDASQL
, @datasrc = 'data_source' --這是特定OLE DB通路接口解釋的資料源。對于SQL Server，這是 SQL Server（servername或servername\instancename）的網絡名稱。對于Oracle，這是SQL*Net别名。對于 MS Access和MSExcel，這是檔案的完整路徑和名稱。對于ODBC資料源，這是系統DSN名稱
, @location = 'location' --由特定OLE DB通路接口解釋的位置
, @provstr = 'provider_string' --OLE DB 通路接口特定的連接配接字元串。對于ODBC連接配接，這是ODBC連接配接字元串。對于MS Excel，這是Excel 5.0
, @catalog = 'catalog' --catalog的定義變化基于OLE DB通路接口的實作。對于SQL Server，這是可選的資料庫名稱，對于DB2，這個目錄是資料庫的名稱
 
 
--建立連結伺服器上遠端登入之間的映射
EXEC sp_addlinkedsrvlogin '遠端伺服器IP','false','sa','架構名','通路密碼'
--标注存儲
EXEC sp_addlinkedsrvlogin 
@rmtsrvname = '遠端伺服器IP', --要添加登入名映射的本地連結伺服器
@useself = false, --當使用true值時，使用本地SQL或Windows登入名連接配接到遠端伺服器名。如果設為false，存儲過程 sp_addlinkedsrvlogin的locallogin、rmtuser和rmtpassword參數将應用到新的映射中
@locallogin = NULL, --這是映射到遠端登入名的SQL Server登入或Windows使用者的名稱。如果這個參數置為NULL，映射将應用SQL Server執行個體中的所有本地登入名
@rmtuser = '架構名', --用來連接配接到連結伺服器的使用者／登入名的名稱
@rmtpassword = '通路密碼' --用來連接配接到連結伺服器的使用者／登入名的密碼
 
--查詢資料
select top 10 * from [222].pwms.pwms.bas_tb_sku
 
--檢視連結伺服器資訊
select name,product,provider,data_source,query_timeout,lazy_schema_validation,is_remote_login_enabled,is_rpc_out_enabled
from sys.servers
where is_linked=1
 
--配置連結伺服器屬性
exec sp_serveroption '别名','name','遠端伺服器IP'
--标注存儲
EXEC sp_serveroption 
@server = 'server' --配置屬性的連結伺服器的名稱
,@optname = 'option_name' --要配置的選項
,@optvalue = 'option_value' --選項的新值
--附參數
參    數 描    述
collation compatible 如果你确認SQL Server執行個體與遠端SQL Server擁有相同的排序規則就啟用這個設定。由于SQL Server不再需要對資料源之間的字元列執行比較操作，把它們假定為相同的排序規則，這樣做可以提升性能
collation name 如果啟用了use remote collation并且是非SQL Server的資料源，則collation name指定遠端伺服器排序規則的名稱。這個排序規則名稱必須是SQL Server所支援的
connect timeout 指定在逾時發生之前到連結伺服器的連接配接會嘗試多少秒。如果數值為“0”，sp_ configure的remote query timeout的伺服器值用來當作預設值
data access 如果啟用，就允許分布式查詢通路
lazy schema validation 如果設為true，則架構不會在查詢開始時去檢測遠端表。盡管這樣會減少遠端查詢的負載，但是如果架構發生了變化并且你沒有進行架構檢測，比如說查詢中引用的對象不能與查詢指令進行通信，就會生成錯誤
query timeout 指定查詢等待的逾時值（秒數）。如果這個值為0，則query wait選項使用sp_configure值
rpc 啟用從伺服器進行遠端過程調用
rpc out 啟用遠端過程調用到伺服器
use remote collation 指定是使用遠端伺服器排序規則（true）還是本地伺服器排序規則（false）
 
--删除連結伺服器屬性
exec sp_dropserver '4'
--标注存儲
EXEC sp_dropserver
@server = 'server' --從SQL Server執行個體中删除的連結伺服器的名稱
,@droplogins = 'droplogins' --如果指定droplogins，則在删除連結伺服器之前要删除登入名映射
 
 
--檢視連結登入名
select s.name linkedServerName,s.data_source linkedserver_source,s.is_linked,s.modify_date,
ll.remote_name,ll.local_principal_id,ll.uses_self_credential,
p.name localLoginName
from sys.Linked_logins ll
inner join sys.servers s on s.server_id=ll.server_id
left join sys.server_principals p on p.principal_id=ll.local_principal_id
where s.is_linked = 1
 
--删除連結伺服器登入名映射
exec sp_droplinkedsrvlogin '遠端伺服器IP',NULL
--标注存儲
EXEC sp_droplinkedsrvlogin 
@rmtsrvname = '遠端伺服器IP' --登入名映射的連結伺服器名稱
@locallogin = NULL --這是從連結伺服器删除的SQL Server登入或Windows使用者映射的名稱
 
 
 
 
 
 
 
SQL code
--簡單點為遠端機器設定遠端連結伺服器。
--假設遠端機器IP為127.0.0.1
--建立連接配接伺服器
EXEC sp_addlinkedserver  '127.0.0.1','SQL Server'
 
--建立連結伺服器上遠端登入之間的映射
EXEC sp_addlinkedsrvlogin '10.0.0.222','false','sa','架構名','密碼'
 
--查詢資料
select top 10 * from [127.0.0.1].資料庫名.架構名.表名
 
--檢視連結伺服器資訊
select name,product,provider,data_source,query_timeout,lazy_schema_validation,is_remote_login_enabled,is_rpc_out_enabled
from sys.servers
where is_linked=1
 
--配置連結伺服器屬性設定别名
exec sp_serveroption 'remotecmp','name','127.0.0.1'
 
--查詢資料
select top 10 * from remotecmp.資料庫名.架構名.表名