A Comprehensive Manual to Manage SQL Server Database

SQL Server is a uniquely designed Relational Database Management System developed by Microsoft. Its basic functionalities include storing, manipulating and retrieving data requested by multiple software applications running on a single computer or on any other machine across a network.

Details of SQL Server Database:

For storing SQL Server data individual files are created for storing data and log information.

·         Primary data file:

This file contains the information required for startup of the database and points to the other files in the database. Each database contains a single primary file that stores tables, indices and procedures. The default file extension for the primary file is .mdf.

·         Secondary data files:

These files are user defined and store the complete user data. Once the database exceeds the maximum size limit for a single Windows file, the secondary files are employed to accommodate the increasing database. The SQL server can have more than one secondary file, each with .ndf default file extension.

·         Transaction Log Files:

These files contain the log information that includes the complete modification history of the database. The Log files effectively analyze the damage causing actions and further recover the database. Each database compulsorily contains at least one log file. The default file extension for the transaction log files .ldf. Read more

Backup SQL Server Database:

Providing a backup for the complete SQL Server database to an off - site location is mandatory to protect the server data from potential catastrophe. This will help you to recover data from the following issues:

ü  Media failures.

ü  User errors, such as dropping a table.

ü  Hardware failures, such as hard disk damage or disconnection from server etc.

Let us discuss the various backup types:

·         Copy only backup:

This is a special purpose backup which is independent of regular sequence of SQL Server backups.

·         Full data backup:

This type includes backing up the entire data of a specific database, the file groups or files and the logs.

·         Differential data backup:

This backup is based on the latest full backup of a complete or partial database, files or file groups and contains the data extents changed since the previous partial backup, known as differential base.

·         Log backup:

Using this type you can create backup of the transaction logs that includes the log records.

·         File backup:

A single database file or file groups can be backed up using this type. 

·         Partial backup:

This backup contains data from a limited file groups in the database, including data in primary file group.

The backed up data can be saved on to backup devices, such as, tapes or disk files. For more details visit here

SQL Server Database Encryption:

The database is encrypted using an encryption key so as to convert the data into a non comprehensive format, referred to as cipher text. This is practiced in order to prevent unintended users from accessing the data. However, the data can be read or manipulated only by the one possessing the decryption key to convert the cipher text back into its original understandable form. SQL Server uses encryption key to protect the database, credentials, and information stored in the server. There are two types of keys- symmetric and asymmetric.

The symmetric key is generated during the initialization of SQL Server. These are the encryption keys using the same password to encrypt and decrypt data. Whereas, the asymmetric keys use different passwords for both encryption and decryption. The public key is used to encrypt data while the private key is used for decrypting. The public and private keys are created by the operating system. For more details about encryption visit here

Password Policy for SQL Server Database:

SQL Server makes use of the Windows password policy mechanisms. This policy applies to a login that is using SQL Server authentication. It also applies to a contained database user with the password. SQL Server can apply the complexity and expiration policies of Windows to its passwords. However, there are a certain guidelines to be followed while setting the password.

·         The password can be 128 characters long.

·         It should not contain the user’s account name.

·         The password must be at least eight characters long.

·         It must contain characters from three of the four categories mentioned as follows:

- Latin upper case letters (A to Z)

- Latin Lower case letters (a to z)

- Base 10 digits (0 to 9)

- Non alphanumeric characters (such as !, $, %, #).

SQL Server Transaction Log File Analysis:

Every database contains at least one transaction log file with .ldf default file extension. These transaction log files are the store for the complete modification history of the server database. It contains the transaction details of all the actions including update, insert, delete, etc. In case of any damage caused to the SQL Server database due to any modification action, the SQL transaction log file proves useful in analyzing as to which action lead to the damage, further successfully brings back the database. For more details visit blog

SQL Server Database Recovery:

Though SQL server is known to be extremely reliable, it cannot be concluded that it is immune to corruption owing to the frequent retrieving, managing or storing of data.

SQL Server facilitates a utility to backup and restore operations, provided they occur within the domain of Recovery Model for database. The database Recovery Model is a property that is responsible for controlling the transaction log maintenance. There are three recovery models available, namely, simple, full and bulk – logged. The server usually employs the full or simple recovery model to recover the sql master database. However, at any point of time, the database can be switched to another recovery model.