SQL Server BackupTutorialWord文档下载推荐.docx
- 文档编号:19846211
- 上传时间:2023-01-10
- 格式:DOCX
- 页数:26
- 大小:388KB
SQL Server BackupTutorialWord文档下载推荐.docx
《SQL Server BackupTutorialWord文档下载推荐.docx》由会员分享,可在线阅读,更多相关《SQL Server BackupTutorialWord文档下载推荐.docx(26页珍藏版)》请在冰豆网上搜索。
SQLServerManagementStudio
IfyouarenewtoSQLServeryoushouldrevieweachofthesetopics,soyouareawareoftheavailableoptionsandwhatstepsyouwillneedtotakeinordertorecoveryourdataifeverthereistheneed.
Youcaneitherusetheoutlineontheleftorclickonthearrowstotheright
orbelowtoscrollthrougheachofthesetopics.
SQLServerRecoveryModels
(SETRECOVERY)
Oneofthefirstthingsthatneedstobesetinordertocreatethecorrectbackupsistosettheproperrecoverymodelforeachdatabase.
TherecoverymodelbasicallytellsSQLServerwhatdatatokeepinthetransactionlogfileandforhowlong.
Basedontherecoverymodelthatisselected,thiswillalsodeterminewhattypesofbackupsyoucanperformandalsowhattypesofdatabaserestorescanbeperformed.
Explanation
Thethreetypesofrecoverymodelsthatyoucanchoosefromare:
∙Full
∙Simple
∙Bulk-Logged
Eachdatabasecanhaveonlyonerecoverymodel,buteachofyourdatabasescanuseadifferentrecoverymodel,sodependingontheprocessingandthebackupneedsyoucanselecttheappropriaterecoverymodelperdatabase.
TheonlyexceptiontothisistheTempDBdatabasewhichhastousethe"
Simple"
recoverymodel.
Also,thedatabaserecoverymodelcanbechangedatanytime,butthiswillimpactyourbackupchain,soitisagoodpracticetoissueafullbackupafteryouchangeyourrecoverymodel.
TherecoverymodelcanbechangedbyeitherusingT-SQLorSQLServerManagementStudio.
Followingareexamplesonhowtodothis.
UsingT-SQLtochangetothe"
Full"
recoveryfortheAdventureWorksdatabase.
ALTERDATABASEAdventureWorksSETRECOVERYFULL
GO
UsingtheSSMStochangetherecoverymodelfortheAdventureWorksdatabase.
AdditionalInformation
∙SelectingtheSQLServerdatabaserecoverymodeltoensureproperbackups
SQLServerFullRecoveryModel
(SETRECOVERYFULL)
The"
recoverymodeltellsSQLServertokeepalltransactiondatainthetransactionloguntileitheratransactionlogbackupoccursorthetransactionlogistruncated.ThewaythisworksisthatalltransactionsthatareissuedagainstSQLServerfirstgetenteredintothetransactionlogandthenthedataiswrittentotheappropriatedatafile.
ThisallowsSQLServertorollbackeachstepoftheprocessincasetherewasanerrororthetransactionwascancelledforsomereason.
Sowhenthedatabaseissettothe"
recoverymodelsincealltransactionshavebeensavedyouhavetheabilitytodopointintimerecoverywhichmeansyoucanrecovertoapointrightbeforeatransactionoccurredlikeanaccidentaldeletionofalldatafromatable.
Thefullrecoverymodelisthemostcompleterecoverymodelandallowsyoutorecoverallofyourdatatoanypointintimeaslongasallbackupfilesareuseable.Withthismodelalloperationsarefullyloggedwhichmeansthatyoucanrecoveryourdatabasetoanypoint.Inaddition,ifthedatabaseissettothefullrecoverymodelyouneedtoalsoissuetransactionlogbackupsotherwiseyourdatabasetransactionlogwillcontinuetogrowforever.
Herearesomereasonswhyyoumaychoosethisrecoverymodel:
∙Dataiscriticalanddatacannotbelost.
∙Youalwaysneedtheabilitytodoapoint-in-timerecovery.
∙Youareusingdatabasemirroring
Typeofbackupsyoucanrunwhenthedataisinthe"
recoverymodel:
∙Completebackups
∙Differentialbackups
∙Fileand/orFilegroupbackups
∙Partialbackups
∙Copy-Onlybackups
∙Transactionlogbackups
HowtosetthefullrecoverymodelusingT-SQL.
ALTERDATABASEdbNameSETRECOVERYrecoveryOption
Example:
changeAdventureWorksdatabaseto"
recoverymodel
HowtosetusingSQLServerManagementStudio
∙RightclickondatabasenameandselectProperties
∙GototheOptionspage
∙UnderRecoverymodelselect"
∙Click"
OK"
tosave
SQLServerSimpleRecoveryModel
(SETRECOVERYSIMPLE)
recoverymodeldoeswhatitimplies,itgivesyouasimplebackupthatcanbeusedtoreplaceyourentiredatabaseintheeventofafailureorifyouhavetheneedtorestoreyourdatabasetoanotherserver.
Withthisrecoverymodelyouhavetheabilitytodocompletebackups(anentirecopy)ordifferentialbackups(anychangessincethelastcompletebackup).
Withthisrecoverymodelyouareexposedtoanyfailuressincethelastbackupcompleted.
recoverymodelisthemostbasicrecoverymodelforSQLServer.
Everytransactionisstillwrittentothetransactionlog,butoncethetransactioniscompleteandthedatahasbeenwrittentothedatafilethespacethatwasusedinthetransactionlogfileisnowre-usablebynewtransactions.
Sincethisspaceisreusedthereisnottheabilitytodoapointintimerecovery,thereforethemostrecentrestorepointwilleitherbethecompletebackuporthelatestdifferentialbackupthatwascompleted.
Also,sincethespaceinthetransactionlogcanbereused,thetransactionlogwillnotgrowforeveraswasmentionedinthe"
∙Yourdataisnotcriticalandcaneasilyberecreated
∙Thedatabaseisonlyusedfortestordevelopment
∙Dataisstaticanddoesnotchange
∙Losinganyoralltransactionssincethelastbackupisnotaproblem
∙Dataisderivedandcaneasilyberecreated
HowtosetthesimplerecoverymodelusingT-SQL.
ALTERDATABASEAdventureWorksSETRECOVERYSIMPLE
SQLServerBulk-LoggedRecoveryModel
(SETRECOVERYBULK_LOGGED)
Bulk-logged"
recoverymodelsortofdoeswhatitimplies.
WiththismodeltherearecertainbulkoperationssuchasBULKINSERT,CREATEINDEX,SELECTINTO,etc...thatarenotfullyloggedinthetransactionlogandthereforedonottakeasmuchspaceinthetransactionlog.
Theadvantageofusingthe"
recoverymodelisthatyourtransactionlogswillnotgetthatlargeifyouaredoingbulkoperationsanditstillallowsyoutodopointintimerecoveryaslongasyourlasttransactionlogbackupdoesnotincludeabulkoperationasmentionedabove.
IfnobulkoperationsarerunthisrecoverymodelworksthesameastheFullrecoverymodel.
Onethingtonoteisthatifyouusethisrecoverymodelyoualsoneedtoissuetransactionlogbackupsotherwiseyourdatabasetransactionlogwillcontinuetogrow.
∙Dataiscritical,butyoudonotwanttologlargebulkoperations
∙Bulkoperationsaredoneatdifferenttimesversusnormalprocessing.
∙Youstillwanttobeabletorecovertoapointintime
Howtosetthebulk-loggedrecoverymodelusingT-SQL.
ALTERDATABASEAdventureWorksSETRECOVERYBULK_LOGGED
TypesofSQLServerBackups
SQLServeroffersmanyoptionsfor
creatingbackups.
Inaprevioustopic,RecoveryModels,wediscussedwhattypesofbackupscanbeperformedbasedontherecoverymodelofthedatabase.
InthissectionwewilltalkabouteachofthesebackupoptionsandhowtoperformthesebackupsusingSSMSandT-SQL.
Thedifferenttypesofbackupsthatyoucancreateareasfollows:
∙Fullbackups
∙Filebackups
∙Filegroupbackups
∙Mirrorbackups
SQLServerFullBackups
ThemostcommontypesofSQLServerbackupsarecompleteorfullbackups,alsoknownasdatabasebackups.
Thesebackupscreateacompletebackupofyourdatabaseaswellaspartofthetransactionlog,sothedatabasecanberecovered.Thisallowsforthesimplestformofdatabaserestoration,sinceallofthecontentsarecontainedinonebackup.
AfullbackupcanbecompletedeitherusingT-SQLorbyusingSSMS.
Thefollowingexamplesshowyouhowtocreateafullbackup.
CreateafullbackupoftheAdventureWorksdatabasetoonediskfile
T-SQL
BACKUPDATABASEAdventureWorksTODISK='
C:
\AdventureWorks.BAK'
∙Rightclickonthedatabasename
∙SelectTasks>
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL Server BackupTutorial