SQL Server Database Backup and Restore

Creating a backup plan

You can choose one of the following combinations of backup types.

Full backups with differentials.
Full backups with transaction logs.
Full backups with differentials and transaction logs.

The frequency of backups would depend on how frequently the data changes, the criticality of data and the bandwidth available to upload the backup data to the backup server.

The best way to create a backup plan is to perform full backups frequently along with regular differential and transaction log backups (if the database supports transaction log backup) depending on the size of the databases.

The databases of Simple Recovery Model should be configured with full database backup along with frequent differential backups. Simple Recovery Model databases don’t support transaction log backup.
Full Recovery Model and Bulk Logged Model databases should be configured with Full database backup along with frequent differential and transactional Log backups. You could configure monthly/weekly full database backup, weekly/daily differential backup and daily/hourly transaction log backups. The SQL Server differential backups will minimize the number of transaction log restores needed.

If a database is included in a backup schedule, then that particular database cannot be selected in another backup schedule as the data might get inconsistent when backed up across two different backup schedules. For the same reason, database backup should not be configured with more than one backup software.

can backup SQL Server Databases while the server is running and is been is being utilized by its clients.

Storing database dumps locally before uploading to backup server

's SQL Server Plugin uses VDI API to fetch the changed data (since last backup) from the database and then dumps this data into a file in a local location. then backs up the entire file and deletes the dumped file.

Decide the location/directory path where the SQL Server database dumps are to be stored. Please ensure that there is enough disk space in the specified location to the save the backup dump. The temporary dump location can also configured while configuring the backup schedule.

What databases to backup

It is highly recommended that you include all the system and user databases in the SQL Server database backup schedules. In case of a complete system failure, it will be necessary to restore the system databases along with user databases in order to recreate the SQL Server environment.

System database(s) backup

System database backup enables rebuilding the system in case of a system or database failure. The master databases should be backed up separately from other system databases as only full backups of master databases are allowed.

The SQL Server system database(s) are: master, msdb, model and tempdb. It is important to have regular backups of these system databases. Backup operations cannot be done on the 'tempdb' database; Hence it is not necessary to back up the tempdb. tempdb' database is rebuilt each time the SQL Server is started. When SQL Server is shut down, any data in tempdb will be deleted permanently.

Model database needs to be backed up only if it is customized.

Master

The master database contains system information and high-level information about all the databases on a SQL Server. If the master database is damaged, SQL Server may fail to start and user databases may become unavailable. Schedule the master database to be backed up on a regular basis, once every week/day/month depending on the frequency of changes made to the database. This will backup the changes made to the user databases and SQL Server, which can then be recovered in case of a master database corruption.

Note: Only Full backups can be configured for a master database. Transactional log, differential backups of master database are not allowed. If you had configured a transaction log or differential log backup for the master database then it will fail with the following error message "Backup can not be performed on this database."

You need to restore the master database if you are:

Rebuilding all your databases from scratch.
Changing any server or database configuration options.
Adding logins or other login security-related operations.
Creating or removing logical backup devices.
Configuring the server for distributed queries and remote procedure calls such as adding linked servers or remote logins.

Model Database

The model database is a template used by SQL Server while creating other databases, such as tempdb and user databases. When a new database is created, the entire contents of the model database are copied to the new database. Backup the model database if you modify it, to include the default configuration for all new user databases. 'tempdb' requires 'model' database to be available for the tempdb to created at SQL Server startup. Restore the model database if you have changed the database template of SQL Server.

Msdb

The msdb database is used by SQL Server for storing data like scheduled job information, backup and restore history information. It generally remains small in size. Full database backup will be fast and optionally differential backup can be configured with this Msdb database. Restore the msdb database if you have changed the scheduling information or want to restore the backup and restore history of the databases.

User database(s) backup

SQL Server User databases should be configured to take back up on a regular basis. The database size and frequency of data modification determine the time and resources involved in implementing a database backup strategy. provides three types of backup for SQL Server database: Full, Differential and Transaction Log. Full backup type backs up the entire database. Differential backup backs up only the modified part since the previous complete backup and Transaction log backup backs up the active portion and truncates the inactive portion of the transaction log.

Full backups only

Use full database backups for the SQL Server database(s), only if it is small. The database has few data modifications and the time required to backup a small database is reasonable.
Full backups with differentials

SQL Server Differential backup contains only the changed portion of the database since the last full database backup. Full backups take longer to complete as compared to the differential backups, but they are the fastest to restore!

It is recommended that you configure to perform Full backups during periods of low database access as the backups are very resource intensive.
Full backups with transaction logs

Use Full database backups and transaction log backups for the SQL Server database(s) in the following conditions:
1. If the entire database is backed up less frequently and the transaction logs are backed up frequently in between full database backups.
2. If the SQL Server database is considerably large and it continues to grow.
3. If there are fairly large updates or data modifications taking place in the SQL Server database.
Full backups with differentials and transaction logs

Transaction logs are logs of all the transactions that happened since the last backup - full or differential. Transaction log backup creates a copy of the active transaction log which lists the transactions that have occurred since the last backup. You could configure a full database backup less frequently and schedule a transaction log backup frequently. And configure to schedule a differential backup after the day's transaction log backups. The differential backup combined with the transaction log backup, reduces the number of transaction log backups that need to be restored while recovering a database.

Restoring a SQL Server database(s) using after a complete server crash

Install the OS and various other applications that existed before.
Install (client) in the SQL Server with the same ID as before. The backup server identifies the backups based on the ID of the client.
If a password was earlier configured in , then go to the 'Administration -> Authenticate Client' page in the web console. Now authenticate the client with the server by providing the same password. If you have forgotten the client's password, go to the backup server and configure a password for the client. To do this, go to 'Server Admin > Reseller Management > Reseller [reseller name] > Customer [customer name] > Edit Configuration (Action for the particular Client)' in the backup server’s web console. Then go to client and authenticate the client by providing the same password.

NOTE: If a password has never been used for the client, i.e. if Auto Authorization is used, then please ignore the above step.
In client, go to 'Restore->Disaster Recovery' page. Choose the backup server name (or) enter the backup server name and click 'Restore' button. This step will restore all your backup configurations from the server. This can be verified by going to the "Backup->List Backup Schedules" page.
After successfully retrieving the backup configurations, go to 'Restore->List Backups for Restore' menu and restore the backup data from the Backup Server.
Restore the System State Backup from the backup server. From the restored SystemState.bkf file, restore the system state using NTBackup utility in Windows. This step is applicable only if you had configured in the SQL server to backup the system state.

You can restore the SQL Server databases by following the below steps:

The login information of the SQL Server instances will not be available in the newly installed and you will be required to provide SQL Server instance login details in "SQL Server Connection Properties" page of the webconsole. You can go to this page through the menu "Backup -> Plugin Backups -> SQL Server".

Once SQL Server Instance login details are entered then you can restore the SQL Server databases by following the steps given below:

Step 1: Master database should be restored before restoring other system and user databases. The model, msdb databases may need to be restored when the master database has been rebuilt or if the model, msdb databases are damaged. You cannot restore a database that is being accessed by users. Please follow the steps below to restore the master database,

Stop all the SQL Server services.
Start SQL Server in single user mode by running the following commands
1. 'sqlservr.exe -m' for local instance.
2. 'sqlservr.exe -m -s <Instance Name>' for named instance.
Restore Master database to local hard disk using the option 'Restore data from backup server only (I will separately run through the 'locally restored files' to restore the SQL Server database later)' in step 4 of 'Restore SQL Server Database' page from webconsole.
Now restore the database backup set to SQL Server using SQL Server Management Studio(SQL Server 2005 / SQL Server 2008 / SQL Server 2012) or Enterprise Manager(SQL Server 2000). Please refer to the Step 2 in the following URL to know more about restoring the databases to the SQL Server from the restored file restore-sql-server.html
Once the master database is restored successfully, the sqlservr.exe running in the command prompt will be stopped automatically.

Step 2 : Now start the required services of SQL Server.

Step 3 : Restore msdb and model databases.

Step 4 : Proceed to restore other user databases.

Technical support-