Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Which tool is better for backup plans, sqlmaint or backup?

I'm setting up a somewhat complex backup plan and am wondering whether you like sqlmaint or backup better? Looks to me like you can do pretty much the same thing with both tools except you can't append to backup sets with sqlmaint.

The use of sqlmaint is for more than making backups; it can be used for running DBCC checks, updating statistics, rebuilding indexes, and making backups to the database(s) and their transaction logs. The sqlmaint utility is an SQL-DMO application. Before using it, make sure to register it by typing the following text at the command prompt:

regsvr32 C:mssql7binnsqldmo.enu

If you have SQL Server or the Enterprise Manager then you don't need to worry about the registration of the utility, as it is already done.

The main idea of using this utility is to thoroughly examine the status of your database(s) and generate a report. (Note that if you use a PlanName or PlanID, the options that you specify will affect all the databases listed in the plan.) There are lot of options in sqlmaint, but let's just talk about some of them:

  • Check for removed and unused space, and set a percent free for future use.
  • Check the entire database (with CkDB).
  • Update the statistics of each table included in your database (with UpdSts).
  • Run backups for the database and the log file.

However, concerning that last option, sqlmaint backs up the entire databases and you don't have all the options like in BACKUP (T-SQL). Also, don't forget about BackUp, which enables you to do complex backup operations--even online backups--of the databases, files, filegroups and logs files. However, with BackUp, you cannot check the integrity of the database as well as you can with sqlmaint. To make your backup plan more effective, you might need to use a bit of both options, as each one has its own advantages and disadvantages.


For More Information

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.