SQL Server BackupTutorialWord文档下载推荐.docx

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>