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

Database Backup Strategies

Database Backup Strategies

One of the most important responsibilities of SQL DBA is the planning, testing and deployment of the backup plan.  The planning of the backup strategy, ironically, starts with the restore strategy that has been determined by the organization.   Two leading questions direct the restore strategy: What is the maximum amount of data loss that can be tolerated, and what is the accepted time frame for the database to be restored in case of a disaster.  The first question involves taking multiple transaction logs backups and the second question involves the use of high availability solutions.  To answer these question, you must ask the why, what, when, where and who for the strategy.

Why we need back-up?

Backups are taken in order to be able to restore the database to its original state just before the disaster and protect against the following reasons:

•          hardware failure
•          malicious damage
•          user-error
•          database corruption

What is a backup?

A copy of data that is used to restore and recover the data after a system failure.

                                                                                 

There are several different kinds of backups:

Full backup - a complete, page-by-page copy of an entire database including all the data, all the structures and all other objects stored within the database.
Differential backups -  a backup of a database that have been modified in any way since the last full backup.
Transaction Log - a backup of all the committed transactions currently stored in the transaction log
Copy-only backup -  a special-use backup that is independent of the regular sequence of SQL Server backups.
File backup - a backup of one or more database files or file-groups

Partial backup - contains data from only some of the file-groups in a database.

When to do backups?

Again, this is a company decision that must be determined by asking the following questions: What is the maximum amount of data loss that can be tolerated, and what is the accepted time frame for the database to be restored in case of a disaster.  The first question will address the use of transaction logs and their frequency, and the second question will involve some type of high availability solution.

Where should backups be placed?

Ideally, the backups should be placed on their own drives onto separate servers from the production server, and then using a third party application, copies made to a remote server on a separate site. What should not be tolerated is having backups on the same drive and server as the data and log files.  If the server crashes, we lose all hopes or restoring critical data!

What needs backing up?

All user defined databases and system database should be backed up.

Who should perform backups?

The person in charge of the backup planning and executing will most likely be the Database Administrator (DBA).  He will coordinate with the upper management, direct and provide valuable advice on the best practices for restoring the database; however, note that on a production server, most of the backups will be automated by using the SQL Agent and jobs.

Backup Retention Period

The amount of backups retained is a question determined by the business needs. Most likely, it may involve a month of backups onsite and three months of backups offsite.  But again, this depends upon the organization needs.

Auto Growth Setting to Improve Performance

What is the auto growth feature?

     An auto-growth event is a part of SQL Server that expands the size of a database file when it runs out of space.  If there is a transaction (such as many inserts) that requires more log space than is available, the transaction log file of that database will need to adjust to the new space needed by increasing the log file size. 

What happens when auto growth is expanding?

This can cause a performance issue, as this is a blocking operation.  The transaction that that initiated the log growth will be held until more space is allocated to the log file, determined by the auto growth setting
Physical fragmented on the disk occurs as the pages required are not necessarily next to each other.  The more auto-growth events you have the more physical fragmentation you will have the files

Avoid auto growth by pro actively configuring the auto growth

Pre-size the data and log files

Manually manage the growth of data and log files

Auto growth should be used for safety reasons only

Don’t rely completely on auto growth

Maintain a level of at least 25 percent available space across disks to allow for growth and peak usage patterns 

Set the auto-grow settings to grow based on megabytes instead of a percentage, so as to have the auto growth consistent

Create A SQL Database

Can create SQL database in two ways either by GUI or script 

Create a database with proper configuration by taking into the following consideration:

• Pre size the data file and auto growth property of the data file
• Pre size the log file and auto growth property of the log file
• Have each data and transaction log file on its own physical drive
• Sizing based on the activity that your environment uses
• Base your size roughly on two or three years of growth patterns

--NOTE: CHANGED THE DEFAULT KB TO MG!

USE master

GO

CREATE DATABASE [DB1]

 CONTAINMENT = NONE

 ON  PRIMARY

( NAME = N'DB1',

FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DB1.mdf' , --<< this must be on its own physical drive

SIZE = 5MB , --<< pre size the data file 5000 mb

FILEGROWTH = 1MB )  --<< pre size the log file 100 mb

 LOG ON

( NAME = N'DB1_log',

FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DB1_log.ldf' , --<< this must be on its own physical drive

SIZE = 1MB , --<< pre size size of transaction log file 100 mb

FILEGROWTH = 1MB ) --<< pre size size auto growth to increase in increments of 5 mb

GO

  

Size the physical DRIVE size of both the data and the transaction log file based on activity and workload

Make sure to allocate data and log files are on a separate disks to get better performance.

To avoid transaction log file full issue  you must take transaction log backups to truncate the transaction log file so that it does not consume the space on disk!!!

SSL(Secure Socket Layer) Encryption

                                                                

                                        Encrypt communication channel between Application Server and Database Server

• ·         First we have to encrypt all databases in particular database server
• ·         Encrypt Connection between SQL Server and SharePoint require: SSL(Secure Socket Layer) method
• ·         Get the certificate from Application team which they have created and configured over Application Server.
• ·         Certificates provided by application team should be like below screenshot

• ·         Copy this certificate in destination database server
• ·         Now, open the Microsoft Management Console (MMC) by click Start -> Run, entering mmc and pressing Enter
• ·         Add the Certificates snap-in by clicking File -> Add/Remove Snap-in… and double clicking the Certificates item (Note: Select computer account and Local computer in the two pages on the wizard that appears.
• ·         Expand Certificates (Local Computer) -> Personal -> Certificates and find the SSL certificate you imported.
• ·         Right click on the imported certificate (the one you selected in the SQL Server Configuration Manager) and click All Tasks -> Manage Private Keys
• ·         Click the Add… button under the Group or user names list box
• ·         Enter the SQL service account name and click ok.
• ·         Next you have to enable ‘Force Encryption’ option to Yes in SQL Configuration Manager----> SQL Server Network Configuration-->Protocols for Servername-->Right click.

• ·         Now click on Certificate Tab and select the certificate from the drop down box as shown in below Screenshot :

• ·         Close and save the MMC and restart the SQL service.

NOTE:

To cross check if selected SSL certificate is actually encrypting the traffic to and from the database.

Open up the ERRORLOG file and look for below screenshot:

To verify the code, go back to the MMC with the Certificates snap-in loaded and your SSL certificate visible. Double click on the certificate and click the Details tab. Scroll to the bottom of the list and select the Thumbprint field. The value that appears should match that which is shown in the sql errorlog file.