Performing Backups

The following method illustrates the use of  T-SQL to backup database because it provides a greater and granular control of the process.  However, you can use the SSMS console.

Full Database backup

Backup the whole database, which includes parts of transaction log which is needed to recover the database using full backup.

BACKUP DATABASE [DBNAME] TO  DISK = N'c:\fullbackups\DBNAME.bak' WITH NOINIT,

NAME = N'sales-Full Database Backup',COMPRESSION,STATS = 10

GO

If you want to verify the backup is Valid one or not . Please do below :

RESTORE VERIFYONLY FROM DISK = N'c:\fullbackups\DBNAME.bak'


GO

                                                              Differential backup


The database must have a full back up in order to take a differential backup; it only backups the changes since last full backup.

BACKUP DATABASE [DBNAME] TO  DISK = N'c:\Diffbackups\DBANME.bak' WITH  DIFFERENTIAL ,NOINIT,NAME = N'sales-Differential Database Backup',COMPRESSION,

STATS = 10

GO

If you want to verify the backup is Valid one or not . Please do below :

RESTORE VERIFYONLY FROM  DISK = N'c:\fullbackups\DBNAME.bak'


GO


                                                          Transaction Log backup

You must backup the transaction log, if SQL Server database uses either FULL or BULK-LOGGED recovery model . Otherwise transaction log is going to full. Backing up the transaction log truncates the log and user should be able to restore the database to a specific point in time.

BACKUP LOG [DBNAME] TO  DISK = N'c:\fullbackups\DBNAME.bak' WITH

NAME = N'sales-Transaction Log  Backup', COMPRESSION,STATS = 10


GO