SQL Server BackupTutorial.docx

SQL Server BackupTutorial.docx

《SQL Server BackupTutorial.docx》由会员分享，可在线阅读，更多相关《SQL Server BackupTutorial.docx（26页珍藏版）》请在冰豆网上搜索。

SQLServerBackupTutorial

SQLServerBackupOptionsandCommands

（Introduction）

Overview

Oneofyourlastlinesofdefenseforjustaboutanysystemistohaveabackupinplaceincasethereisaneedtorecoversomeorallofyourdata. ThisisalsotrueforSQLServer. 

Inthistutorialwewilldiscuss

∙selectingthecorrectrecoverymodels

∙whatbackupoptionsareavailable

∙howtocreatebackupsusingT-SQLcommands and SQLServerManagementStudio

IfyouarenewtoSQLServeryoushouldrevieweachofthesetopics,soyouareawareoftheavailableoptionsandwhatstepsyouwillneedtotakeinordertorecoveryourdataifeverthereistheneed.

Youcaneitherusetheoutlineontheleftorclickonthearrowstotheright orbelowtoscrollthrougheachofthesetopics.

SQLServerRecoveryModels

（SETRECOVERY）

Overview

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）

Overview

The"Full"recoverymodeltellsSQLServertokeepalltransactiondatainthetransactionloguntileitheratransactionlogbackupoccursorthetransactionlogistruncated.ThewaythisworksisthatalltransactionsthatareissuedagainstSQLServerfirstgetenteredintothetransactionlogandthenthedataiswrittentotheappropriatedatafile. ThisallowsSQLServertorollbackeachstepoftheprocessincasetherewasanerrororthetransactionwascancelledforsomereason. Sowhenthedatabaseissettothe"Full"recoverymodelsincealltransactionshavebeensavedyouhavetheabilitytodopointintimerecoverywhichmeansyoucanrecovertoapointrightbeforeatransactionoccurredlikeanaccidentaldeletionofalldatafromatable.

Explanation

Thefullrecoverymodelisthemostcompleterecoverymodelandallowsyoutorecoverallofyourdatatoanypointintimeaslongasallbackupfilesareuseable.Withthismodelalloperationsarefullyloggedwhichmeansthatyoucanrecoveryourdatabasetoanypoint.Inaddition,ifthedatabaseissettothefullrecoverymodelyouneedtoalsoissuetransactionlogbackupsotherwiseyourdatabasetransactionlogwillcontinuetogrowforever.

Herearesomereasonswhyyoumaychoosethisrecoverymodel:

∙Dataiscriticalanddatacannotbelost.

∙Youalwaysneedtheabilitytodoapoint-in-timerecovery.

∙Youareusingdatabasemirroring

Typeofbackupsyoucanrunwhenthedataisinthe"Full"recoverymodel:

∙Completebackups

∙Differentialbackups

∙Fileand/orFilegroupbackups

∙Partialbackups

∙Copy-Onlybackups

∙Transactionlogbackups

HowtosetthefullrecoverymodelusingT-SQL.

ALTERDATABASEdbNameSETRECOVERYrecoveryOption

GO

Example:

changeAdventureWorksdatabaseto"Full"recoverymodel

ALTERDATABASEAdventureWorksSETRECOVERYFULL

GO

HowtosetusingSQLServerManagementStudio

∙RightclickondatabasenameandselectProperties

∙GototheOptionspage

∙UnderRecoverymodelselect"Full"

∙Click"OK"tosave

SQLServerSimpleRecoveryModel

（SETRECOVERYSIMPLE）

Overview

The"Simple"recoverymodeldoeswhatitimplies,itgivesyouasimplebackupthatcanbeusedtoreplaceyourentiredatabaseintheeventofafailureorifyouhavetheneedtorestoreyourdatabasetoanotherserver. Withthisrecoverymodelyouhavetheabilitytodocompletebackups（anentirecopy）ordifferentialbackups（anychangessincethelastcompletebackup）. Withthisrecoverymodelyouareexposedtoanyfailuressincethelastbackupcompleted.  

Explanation

The"Simple"recoverymodelisthemostbasicrecoverymodelforSQLServer. Everytransactionisstillwrittentothetransactionlog,butoncethetransactioniscompleteandthedatahasbeenwrittentothedatafilethespacethatwasusedinthetransactionlogfileisnowre-usablebynewtransactions. Sincethisspaceisreusedthereisnottheabilitytodoapointintimerecovery,thereforethemostrecentrestorepointwilleitherbethecompletebackuporthelatestdifferentialbackupthatwascompleted. Also,sincethespaceinthetransactionlogcanbereused,thetransactionlogwillnotgrowforeveraswasmentionedinthe"Full"recoverymodel.

Herearesomereasonswhyyoumaychoosethisrecoverymodel:

∙Yourdataisnotcriticalandcaneasilyberecreated

∙Thedatabaseisonlyusedfortestordevelopment

∙Dataisstaticanddoesnotchange

∙Losinganyoralltransactionssincethelastbackupisnotaproblem

∙Dataisderivedandcaneasilyberecreated

Typeofbackupsyoucanrunwhenthedataisinthe"Simple"recoverymodel:

∙Completebackups

∙Differentialbackups

∙Fileand/orFilegroupbackups

∙Partialbackups

∙Copy-Onlybackups

HowtosetthesimplerecoverymodelusingT-SQL.

ALTERDATABASEdbNameSETRECOVERYrecoveryOption

GO

Example:

changeAdventureWorksdatabaseto"Simple"recoverymodel

ALTERDATABASEAdventureWorksSETRECOVERYSIMPLE

GO

HowtosetusingSQLServerManagementStudio

∙RightclickondatabasenameandselectProperties

∙GototheOptionspage

∙UnderRecoverymodelselect"Simple"

∙Click"OK"tosave

SQLServerBulk-LoggedRecoveryModel

（SETRECOVERYBULK_LOGGED）

Overview

The"Bulk-logged"recoverymodelsortofdoeswhatitimplies. WiththismodeltherearecertainbulkoperationssuchasBULKINSERT,CREATEINDEX,SELECTINTO,etc...thatarenotfullyloggedinthetransactionlogandthereforedonottakeasmuchspaceinthetransactionlog. 

Explanation

Theadvantageofusingthe"Bulk-logged"recoverymodelisthatyourtransactionlogswillnotgetthatlargeifyouaredoingbulkoperationsanditstillallowsyoutodopointintimerecoveryaslongasyourlasttransactionlogbackupdoesnotincludeabulkoperationasmentionedabove. IfnobulkoperationsarerunthisrecoverymodelworksthesameastheFullrecoverymodel. Onethingtonoteisthatifyouusethisrecoverymodelyoualsoneedtoissuetransactionlogbackupsotherwiseyourdatabasetransactionlogwillcontinuetogrow.

Herearesomereasonswhyyoumaychoosethisrecoverymodel:

∙Dataiscritical,butyoudonotwanttologlargebulkoperations

∙Bulkoperationsaredoneatdifferenttimesversusnormalprocessing.

∙Youstillwanttobeabletorecovertoapointintime

Typeofbackupsyoucanrunwhenthedataisinthe"Simple"recoverymodel:

∙Completebackups

∙Differentialbackups

∙Fileand/orFilegroupbackups

∙Partialbackups

∙Copy-Onlybackups

∙Transactionlogbackups

Howtosetthebulk-loggedrecoverymodelusingT-SQL.

ALTERDATABASEdbNameSETRECOVERYrecoveryOption

GO

Example:

changeAdventureWorksdatabaseto"Bulk-logged"recoverymodel

ALTERDATABASEAdventureWorksSETRECOVERYBULK_LOGGED

GO

HowtosetusingSQLServerManagementStudio

∙RightclickondatabasenameandselectProperties

∙GototheOptionspage

∙UnderRecoverymodelselect"Bulk-logged"

∙Click"OK"tosave

TypesofSQLServerBackups

Overview

SQLServeroffersmanyoptionsfor creatingbackups. Inaprevioustopic,RecoveryModels,wediscussedwhattypesofbackupscanbeperformedbasedontherecoverymodelofthedatabase. InthissectionwewilltalkabouteachofthesebackupoptionsandhowtoperformthesebackupsusingSSMSandT-SQL.

Explanation

Thedifferenttypesofbackupsthatyoucancreateareasfollows:

∙Fullbackups

∙Differentialbackups

∙Filebackups

∙Filegroupbackups

∙Partialbackups

∙Copy-Onlybackups

∙Mirrorbackups

∙Transactionlogbackups

SQLServerFullBackups

Overview

ThemostcommontypesofSQLServerbackupsarecompleteorfullbackups,alsoknownasdatabasebackups. Thesebackupscreateacompletebackupofyourdatabaseaswellaspartofthetransactionlog,sothedatabasecanberecovered.Thisallowsforthesimplestformofdatabaserestoration,sinceallofthecontentsarecontainedinonebackup.

Explanation

AfullbackupcanbecompletedeitherusingT-SQLorbyusingSSMS. Thefollowingexamplesshowyouhowtocreateafullbackup.

CreateafullbackupoftheAdventureWorksdatabasetoonediskfile

T-SQL

BACKUPDATABASEAdventureWorksTODISK='C:

\AdventureWorks.BAK'

GO

SQLServerManagementStudio

∙Rightclickonthedatabasename

∙SelectTasks>