SQL Backup

SQL Database Backup Plan

Database backup and restore can be achived by the following


1. First step is to do the back up

BACKUP DATABASE [ManufacturingChina] TO
DISK = N'Z:\DATABASE\ManufacturingChina_backup.bak' WITH NOFORMAT,
INIT, NAME = N'ManufacturingChina_backup', SKIP, REWIND, NOUNLOAD, STATS = 10

--NO FORMAT - specifies that the media header should not be written on all volumes used for this backup operation. This is the default behavior.

--INIT - Specifies that all backup sets should be overwritten

--SKIP - Disables the checking of backup set expiration and name that is usually performed by the BACKUP statement to prevent overwrites of backup sets.

--NOREWIND -Used only with TAPE devices to improve performance when performing multiple backup operations to a loaded tape

--SQL Server will retain ownership of the tape drive until a BACKUP or RESTORE command is used with either the REWIND or UNLOAD option or the server is shut down.

2. step 2 is to do the log back up with INIT that is to overwrite

BACKUP LOG [ManufacturingChina] TO DISK =
N'Z:\DATABASE\ManufacturingChina\ManufacturingChina.trn'
WITH NOFORMAT, INIT, NAME = N'ManufacturingChina-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

3. the above two scripts to be on one job scheduled at says 12:00 midnight

4. now we need to do the log back up with NOINIT that is with append

BACKUP LOG [ManufacturingChina] TO DISK = N'Z:\DATABASE\ManufacturingChina\ManufacturingChina.trn' WITH NOFORMAT, NOINIT, NAME = N'ManufacturingChina-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10GO

5. Finally to keep the size of the database in tact

DBCC SHRINKFILE (N'ManufacturingChina_Log' , 786)

786 is the default size given in this example, you can provide the size based on the database default log size.

6. Step 4 and 5 can be scheduled on a separate job