SQL Server Backup Topics

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')

How to rebuild master database?

Different edition of SQL Servers have different ways to rebuild the master database. For SQL Server 2000, in the installation directory, there is a program named rebuildm.exe,(If you can't find it, you have to find it on MS website) you start this program and follow it's instructions to rebuild the master database .

What should I do when the log shipping fails?

The log shipping job actually works as following :

  1. Create log or other type of backup from source database .
  2. Select backup , include backups created in backup job or previous log shipping job that not shipped.
  3. Restore backups to target database.

That is , even this time it fails (in backup or restore stage ), when it next runs , it will continue from last failure time point, and it can select backup or logs necessary automatically to restore to target database.

I get message "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE ...".

If a database's recovery model is simple, such as master , model .etc system databases , it has no transaction log file , you can only do full and differential backup on this type of database.

But if you change database's recovery model to full or Bulk-logged , you can create transaction log backup from this database.

Following step show how the change a database's recovery mode :

How to restore SQL server database to the point of failure?

To restore a SQL Server database to point of failure, the database's recovery model should not be "Simple", because it needs a transaction log backup to do that, "Simple" recovery model does not have a transaction log.

If your database's recovery modal is "Simple", you can not restore to the point of failure, in this case, please select the latest differential and full backup do the restore. If your database's recovery modal is not "Simple", to restore the database to the point of failure :

1). Back up the tail of the transaction log after SQL Server fails.