Backup Plan for huge databases


The following backup plan is recommended for very large Microsoft SQL Server databases with traditional business hours operation:

 

  Full backup Saturday morning

  Differential backup Mon-Thu nights

  Transaction backup hourly Mon-Fri 8am to 6pm

— SQL full database backup

BACKUP DATABASE [AdventureWorks2008]

TO  DISK = N’F:\data\AdventureWorks2008\backup\AW8.BAK’

WITH NOFORMAT, INIT, 

NAME = N’AdventureWorks2008-Full Database Backup’,

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

— SQL diffential database backup

BACKUP DATABASE [AdventureWorks2008]

TO  DISK = N’F:\data\AdventureWorks2008\backup\AW8D.BAK’

WITH  DIFFERENTIAL , NOFORMAT, NOINIT, 

NAME = N’AdventureWorks2008-Differential Database Backup’,

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

— SQL transaction log backup

BACKUP LOG [AdventureWorks2008]

TO  DISK = N’F:\data\AdventureWorks2008\backup\AW8T0900.BAK’

WITH NOFORMAT, NOINIT,  NAME = N’AdventureWorks2008-Transaction Log  Backup’,

SKIP, NOREWIND, NOUNLOAD,  STATS = 10

GO

————

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: