Although people knows whats best for them when creating a backup plan and using the scripts in jobs to schedule every night , they sometimes forget about the restoring plan .

A good DBA should always understand the motive behind all his approaches . If a backup plan is not proper , the restoring would never be perfect .

BEST BACKUP PLANS SHOULD ALWAYS BE CREATED SO THAT WHEN WE RESTORE , THERE IS MINIMUM DATA LOSS IN MINIMUM TIME.

BEST PRACTICE :

1. A FULL backup(complete database backup) every SUNDAY.
2. A DIFFERENTIAL backup(incremental backup since last full backup) every NIGHT.
3. TRANSACTIONAL backup(logs backup in the form of sequence chain) every 15 minutes .

But the main thing is when you restore these backups , you should not do it manually as its gonna be hell complicated and error prone. So, the backups should always have DATES as suffix or prefix so that a loop can be created when restoring , keeping in mind the log chain.

------------------------------------------------------------------------------------------------------------
FULL BACKUP (Copy and paste the script in a job scheduled every Sunday Night) ::::---

USE <DATABASE NAME>
GO
DECLARE @BackupFileName varchar(20)
SELECT @BackupFileName = 'E:\sql_backups\db_backup_' + CONVERT (VarChar, GetDate(), 120) + '.bak'
BACKUP DATABASE <DATABASE NAME> TO  DISK = @BackupFileName WITH RETAINDAYS = 14, NOFORMAT, INIT,  NAME = N'myDB', SKIP, REWIND, NOUNLOAD,  STATS = 10;


DIFRENTIAL BACKUP  (Copy and paste the script in a job scheduled every Night) ::::---

USE <DATABASE NAME>
GO
DECLARE @FileName VARCHAR(200)
SET @FileName='E:\\sql_backups\db_Diffrential_backup_'+ CONVERT(CHAR(8),GETDATE(),112)+'.bak'
BACKUP DATABASE <DATABASE NAME> TO DISK=@FileName WITH DIFFERENTIAL,CHECKSUM, CONTINUE_AFTER_ERROR;

TRANSACTIONAL BACKUP (Copy and paste the script in a job scheduled every 15 minutes)::--

USE <DATABASE NAME>
GO
DECLARE @Baksql VARCHAR(8000)
DECLARE @BackupFolder VARCHAR(100)
DECLARE @BackupFile VARCHAR(100)
DECLARE @BAK_PATH VARCHAR(4000)
DECLARE @BackupDate varchar(100)

-- Setting value of  backup date and folder of the backup

SET @BackupDate =  REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR,GETDATE(),120),'-',''),':',''),' ','_') -- 20110517_182551
SET @BackupFolder = 'E:\sql_backups\'
SET @Baksql = ''

-- Declaring cursor

DECLARE c_bakup CURSOR FAST_FORWARD READ_ONLY FOR
SELECT NAME FROM SYS.DATABASES
WHERE state_desc = 'ONLINE' -- Consider databases which are online
AND name='<DATABASE NAME>'  -- Exluding system databases
AND recovery_model_desc = 'FULL'  -- Including database which are only have FULL recovery mode

-- Opening and fetching next values from sursor

OPEN c_bakup
FETCH NEXT FROM c_bakup INTO @BackupFile
WHILE @@FETCH_STATUS = 0
BEGIN
SET @BAK_PATH = @BackupFolder + @BackupFile

-- Creating dynamic script for every databases backup

SET @Baksql = 'BACKUP Log ['+@BackupFile+'] TO DISK = '''+@BAK_PATH+'_TrnBackup_'+@BackupDate+'.trn'' ;'

-- Executing dynamic query

PRINT (@Baksql)
EXEC(@Baksql)

-- Opening and fetching next values from sursor

FETCH NEXT FROM c_bakup INTO @BackupFile

END

-- Closing and Deallocating cursor

CLOSE c_bakup
DEALLOCATE c_bakup


---------------------------------------------------------------------------------------------------------

You can mention your desired path for backups and use your database name for <DATABASE NAME>.


But this is not over , corresponding to this check out my next blog for "one click restoring "