When is a SQL server differential backup required?

  • A relatively small portion of the data in the database has changed since the last database backup. Differential database backups are most efficient if the same data is modified frequently.
  • You are using the Simple Recovery model, which does not permit Transaction log backups, and you want to perform backups more frequently than is practical for full database backups.
  • You are using the Full or Bulk-Logged Recovery model and want to minimize the time it takes to roll forward Transaction log backups when restoring a database.

Creating MS SQL Server transaction log backup.

  • Transaction log backup is a backup for log files of a database .
  • Backing up the transaction log periodically to create a sequence of transaction log backups offers users the most flexibility in restoring databases.
  • By creating transaction log backups, a database can be restored to any point in time contained within the sequence of transaction logs, right up to the point of failure , because it is possible to back up the currently active transaction log even if the database is inaccessible.

Can I mix use SQL Server backup software Microsoft SQL Server Agent in backing up a database?

No .

If you define backup jobs for the same database in both backup utilities, Microsoft SQL Server does not record the program who create backups. When you plan to restore this database, you will find you are in a complicated situation, such as you may need a full backup created by SQL Server Agent and a differential backup created by SQL Server Backup software. It is strongly recommended that you just select one SQL Server Backup utility to back up your database.

I got errors while doing database consistency check , what is suggestion?

SQL Server Backup has an option to do the sql server database consistency check , see following:

You got errors indicated the potential problems in your database and should be corrected immediately , the problem may be caused by many reasons , this product can only let you know the DBCC check is failed , to get more detail information , you can run the DBCC command once again in Query Analyzer or Management Studio, command as following :

DBCC CHECKDB ('database name')