實戰演練 SQL Server 2008 筆記輸送(Log Shipping)

不適用SQL Server2008-->SQL Server 2022



數據庫學習 【2023-04-06 20:41】


一、什么是日志傳送?

caozuoguochenghenrongyi,yixiasangezitaiwugezi:beifenhuanyuan->fuzhidao->zhaohui。

ruoguoyourenlailaitijiaozheyigezhejigededongzuo,jinnengjiaogongzuojiluzhuangxiegong;eryousql server jobzhongxinlaitijiao,jiujiaogongzuojiluyunshu。doudezhejianshi,buxiangtongdeshangdangci,yichengfadoubuxiangtong。

二、日志傳送能解決什么問題?

kefuziliaokudeduobaozhangpeixunguanliqirecunchuxianxiang。shutaibaozhangpeixunguanliqidingshikaiguancunchu,jishinenggou zuoyouliyuziliaokubaozhangpeixunguanliqidetibu。

三、日志傳送的優點是什么?

jianjie!bisql serverdedeorcalefuzhidao、yingshejianjieduole。

四、日志傳送的缺點是什么?

gongzuobijixinxideshusongzhidalizhijiwindowsxinxigongxiangappdebanfa,budalizhijiftp,jiuyouruyouzhengbaoguogongsidezhisongbentu,busongkuasheng。yongyouzidongbeifenfuwupeixunqidouxiangzaitonggegeneiwangnei,yinci gengzhundemingdagaijiao“gongzuobijibentuchuandao”。

五、日志如何傳送?

1. [zhutongjishujufenxikugongzuoqi]yaochuandigongzuorizhidetongjishujufenxikudekangfudemoshikeyishijieran(full)

1.png

2.  [zhudashujuziliaoxitongkufuwuxiangmuqi]duiyaochuansongshujuziliaoyunxingrizhidedashujuziliaoxitongkushiyong1ciqiejinci1cirengranbeifenhuanyuan

BACKUP DATABASE [CNBlogsJob] TO  DISK = N'F:\FullBackup\CNBlogsJob.bak' WITH NOFORMAT, INIT,  
NAME = N'CNBlogsJob-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO

3.  [diannaokeyihuifuzidongbeifenshujutongjifenxikudefuwubaozhangqi]jiangyijingdiannaokeyihuifuzidongbeifenwenjianmingniantiedaodiannaokeyihuifuzidongbeifenshujutongjifenxikudefuwubaozhangqishang,jinyistandbydemoshishixiankeyihuifu

RESTORE DATABASE [CNBlogsJob] 
FROM  DISK = N'F:\FullBackup\CNBlogsJob.bak' WITH FILE = 1,  
MOVE N'CNBlogsJob' TO N'D:\Data\CNBlogsJob.mdf',  
MOVE N'CNBlogsJob_log' TO N'E:\Logs\CNBlogsJob.LDF',  
STANDBY = N'F:\Standby\ROLLBACK_UNDO_CNBlogsJob.BAK', NOUNLOAD, STATS = 10
GO



huidaoqudeshenglihou,sql serverguankongpingtaizhonghuibiaomingxiamiandetusuishidemoshi:

2.png

4. [備份數據庫服務器]創建一個普通的Windows用戶帳戶(比如logshipper),將該用戶加入到以SQLServerSQLAgentUser開頭的組(未驗證是否可以不加,就必須加盟以至于會配資不可功),并將該用戶設置為SQL Server Agent的運行帳戶。

3.png

5. [shoujibeifendeoraclechanpinqi]yougeyikuanwenbenjia(jiuxiangf:\logbackup),shiyongyufangzhicongzhudeoraclecaozuohuilaidexitongrizhiwenben。

6. [zhuxinxikutiexinwulibaozhangqi]chuangzaoyukaobeixinxikutiexinwulibaozhangqitongxingtongpengyouminghemimadewindowspengyougerenzhanghu(dagebifangzhelideyingwenshilogshipper)。

7. [zhushujuxianshibaogaoziliaokujingzhunfuwupeixunzhiliangqi]zujianchuzanggongzuorizhidiannaoshoujibeifenyasuoxitongwenjiangeshijiagongsimingzideyasuoxitongwenjiangeshijiagongsimingziming,bingjianggaiyasuoxitongwenjiangeshijiagongsimingzimingyizhiduquanxianshedingxuchanggeilogshipper。jiaruyasuoxitongwenjiangeshijiagongsimingzigongsimingzishilogbackup,zhushujuxianshibaogaoziliaokujingzhunfuwupeixunzhiliangqigongsimingzishidbserver,shiyongshedingxuchangrangdiannaoshoujibeifenshujuxianshibaogaoziliaokujingzhunfuwupeixunzhiliangqiyekeyifangwenliulanxuchangluxian \\dbserver\logbackup 。

8. [zhushujubaogaoxinxikufuwuqi]xuanyaoshishijiluchuanshushujubaogaodeshujubaogaoxinxiku,shiyong fushuxingduihuajihuiqi,jinxingtransaction log shippingduihuajihuiqi,gouxuanenable this as a primary database in a log shipping configuration,ranhounichaolianjiebackup settingsshiyong jiluchuanshushujubaogaodingqibeifenshiyongduihuajihuiqi,fangshitu:

4.png

\\dbserver\logbackup bianshigeishoujibeifentongjisqlyewuqifangwendeshujufuwujueduilujing。

f:\logbackup shizhushujuyuankujingzhunyunfuwuqibaoguanxitongrizhihuifubeifenwenjianjiadexianlu。

9. [zhucanshuyuanziliaokuchanpinqi]mashangganhuidaotransaction log shippingshikou,zaisecondary databasesbumendankuangaddxuanniu,lianruzhicanshuyuanhuifushoujibeifencanshuyuanziliaokuchanpinqi,kaolvxiangpipeidecanshuyuanziliaoku;zaiinitialize secondary databaseshikouzhong,kaolv"no, the secondary database is initialized";zaicopy filesshikouzhong,duqucanshuyuanhuifushoujibeifencanshuyuanziliaokuchanpinqizhongzaijiazhongcunchucanshuyuanhuifushoujibeifenbijiwenbendeluxian(haobif:\logbackup);zairestore transaction logshikouzhong,kaolvstandby mode。

5.png

6.png

7.png

10. 成就 更改后來,日記傳到就逐漸開始操作了。主大統計數據檔案格式文本夾服務性器上的Job選擇更改的時接連要定期地電腦備份還原當前工作大統計數據檔案格式文本夾庫的日記,將日記文本夾保存文檔至遠程服務器的文本夾夾(詞有F:\LogBackup);電腦備份還原大統計數據檔案格式文本夾服務性器上的Job連續地根據數據檔案格式產品文本夾夾(詞有\\dbserver\LogBackup)從主大統計數據檔案格式文本夾庫服務性器抄襲日記文本夾到遠程服務器的文本夾夾(詞有F:\LogBackup),然后呢將日記文本夾灰復至地處Standby情形的數據庫表中。


散文由來:


友情鏈接:

  • 二維碼
  • 掃一掃加關注
——信陽市速全網咯科技創新有現工司—— 著作權人幾乎所有:信陽市速全互聯網技術較少機構
五月婷婷影视_国产乱子伦露脸在线_免费香蕉视频国产在线看_亚洲综合一区国产精品