How SQLCMD queries help user in recovery of corrupt SQL databases?

Storing MDF and LDF databases in a backup is a part of routine SQL Server maintenance task. Database backups help the user in recovering severely damaged SQL Server databases. MDF or Master database file is crucial for MS SQL Server because if MDF databases get damaged, all valuable user information on the server will be lost. You can create backup of MDF databases using SQLCMD utility.

SQLCMD utility allows administrator or user to enter Transact SQL commands either from SQL Server management studio (SSMS) or DOS command prompt. However SQLCMD command utility is included only in SQL Server 2005, 2008, 2008 r2 and 2012 versions. If you are using Express editions of MS SQL Server, then you’ll not find Maintenance plan inside the SQL Server Management Studio Express (SSMSE). Therefore, you should use Transact SQL commands from the DOS command prompt. Here is a way to use Transact SQL query lines of sqlcmd.exe from the DOS prompt.

The command line will appear like this:

  • C:\Documents and Settings\xyz\SQLCMD –E –S Studio – Q Backup Monthly Tyres Sales To Disk = “E:MonthlyTyres Sales.bak”

Here, xyz is the username; Studio is the name of SQL Server and Monthly Tyres Sales is name of my SQL Server database. If I need this database backup for recovery, then I can use SQLCMD command again to restore the database:

C:\Documents and Settings\nirajs\SQLCMD –E –S Studio –Q “Restore Monthly Tyres Sales From Disk = “E:\Monthly Tyres Sales.bak”

However, SQLCMD.exe works with certain limitations at the command prompt such as:

  • It fails to copy MDF files from different SQL Servers
  • It does not support bricks level SQL database backup
  • You need to login as Windows user on the local machine where SQL Server is installed

To tackle the issues, you should use SQLCMD from SSMS or SSMSE instead. You can create backup of transactional logs, database groups or individual files by running SQL Server Management Studio or Studio Express in SQLCMD mode. Moreover, you can also choose appropriate recovery model such as Simple, Full or Bulk logged of SQL Server Management Studio or Studio Express. You need backups of all SQL Server databases i.e. primary, master and Transactional logs for Full database recovery. But SQL Server Backup operation has following restrictions:

  • It does not work on offline SQL databases (Primary, Master or Log)
  • You cannot execute full, differential or partial backups if one or more SQL databases are in offline folder
  • Backup operation for Transactional logs fails if one or more log databases are in offline mode and changes are made in the log files

Therefore, one must choose third party software such as SQL database repair that can recover both online and offline SQL databases. It support almost all the versions of MS SQL Server such as SQL Server 2000, 2005, 2008 and 2008 R2. SQL database repair software can recover database objects such as triggers, indexes, stored procedures, views, primary keys, secondary keys and others even if database backup is unavailable. SQL database repair software keeps the formatting and structure of recovered items completely unaltered in the new MDF databases.

Summary:

You can backup or restore SQL databases (Primary, Master and Log) using SQLCMD tool of MS SQL Server. You can run SQLCMD queries either from DOS prompt or SQL Server Management Studio or Studio Express. However, SQL Server does not support backup of offline SQL databases i.e. LDF and MDF. Therefore, one must use third party software such as SQL database repair for recovery of MDF and LDF databases in SQL Server disaster situation.

Search
Related Links