Wednesday, March 27, 2013

How to Restore SQL server Database with Corrupt Media Backup?

Backup is a thing that can be used if necessary. In term of SQL server, Backup is a copied database that can be used to restore the database after a disaster occurs. The most popular SQL server database backups are full, differential, transaction log, file, partial, copy-only & database backup. SQL server database backups are essential to avoid the data loss from database. SQL server database administrator can restore the database from backups with the help of T-SQL command or SQL server management studio (SSMS). In respect to the SQL database, database backups are also prone to corruption and generates error message in the event of restoring the database.

Why Restore Operation Fails?
SQL server generates restore error messages when backup media is corrupt. These error messages either reported by the operating system or database checksum.

What to do now?
SQL server database administrators have three options to overcome on above problems.
  1. Cure the error message & restart the restore operation.
  2. Continue with the error message.
  3. Close the restore operation & choose any other recovery mechanism.
1. Cure the error message & restart the restore operation: Database administrator can be cured the error messages by following ways.
  • If the error occurred on the tape drive then clean or replace the tape drive.
  • If error occurred due to disk device then resolve the device error & replace the damaged file.
2. Continue with the Error Messages: DBA can specify continue_after_error option in restore statement to restore the database. It will allow the restore operation with past error message and roll forward occurs. Now DBA can apply transaction log backups to bring the database online. Below is the restore syntax with continue_after_error option.


After the specifying continue_after_error option, roll forward encounters the error message then recovery cannot be completed and database is left offline.

How to Repair Offline Database?
DBA can repair SQL database with the help of DBCC CHECKDB. DBCC CHECKDB has three repair options to repair the database, repair_rebuild, repair_fast & repair_allow_data_loss. There is no any data loss in repair_rebuild & repair_fast options but minimum amount of data may be lost in case of repair_allow_data_loss option.

Note: To learn about minimum repair level needed, first run DBCC CHECKDB with any repair option. It will suggest you the repair level and re-run the DBCC CHECKDB with suggested repair level to repair the database.

3. Close the restore operation & choose any other recovery mechanism: It is the easiest method to resolve the above problem. If you have other recovery disaster plan then it is the right time to use it.

Monday, February 25, 2013

Help Me! I am unable to open my SQL server database

At times, when SQL server database administrator tries to open the database, he gets below error message on the screen.

Database cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details

Cause: As the result of above error message, database administrator is unable to open the database. There are various reasons for the above error message like less disk space, permission unavailability and many more.

It is one of the most generated error message during mounting the SQL sever database.

WorkAround: Database administrator can resolve above error message by following steps:
  1. Database administrator needs to add more hard drive space and it can be done by following methods:
    1. Remove the unnecessary files from the hard drive
    2. Add new hard drive.
  2. Check the Autogrow feature of the database, if it is ON then disable it.
  3. Have you enough permission to open the database? Check for the permission setting.
  4. Check for .mdf & .ldf files. If these files are mark as read only then database administrator is unable to open the file.

Recommendations for Changing the SQL server Recovery Models

A database administrator can change the recovery model of their database at any time as per the requirement. If you have planned to change the recovery model from the simple recovery model to the full recovery model during the bulk operation then be aware that logging of bulk operations will be change from minimal logging to full logging. Here I will tell you some recommendations that are very necessary before or after changing to Simple recovery model.

Recommendations for changing from Simple to Full recovery model: If you are planning to change the recovery model for your database from the simple recovery model to the full recovery model then take care of the followings:
  • Take the full or differential database backup of your database immediately after changing the recovery model from the simple to the full recovery model. The log chain will not start until the full or differential database backup.
  • Take regular backup of your transaction log and make a proper plan for backing up the log backup according to your database need.
Note: If you don't backup the transaction log on the regular interval then it can expand until it runs out of your disk.

Recommendations for changing from Full to Simple recovery model: If you are planning to change the recovery model for your database from the full recovery model to the simple recovery model then please attention for the following:
  • Changing the recovery model from full to simple will break the log backup chain so make a backup of your log backup before changing the recovery model. Log backup will allow you to recover the database up to that point.
  • After changing the recovery model, you will need to take the data backup on regular interval to avoid any data loss.
How to change the recovery model: After understanding the impact of backup and restore strategy that will happen after the changing the recovery model to simple, now you can change the recovery model of your database with the help of SSMS. Perform below steps to change the recovery model for your database:
  • Connect to the SQL server instance and expand the server tree.
  • Now expand the database tree, select user database, or system database that suits your requirement.
  • Press right clicks on the database and go-to the database properties.
  • Now go-to the select a page pane and click on the options.
  • Now go-to the recovery model section and see the current recovery model of your database.
  • To change the recovery model, click on the arrow next to the recovery model options and select your desired recovery model: simple, bulk-logged or full.

Wednesday, February 13, 2013

Choose the Best Method for SQL server Database Moving

At times, it may require to moving SQL server data or log files from one to other location on the same SQL server. There are two methods that help you in performing this task; Attach/Detach & ALTER Database. Both methods perform the same task but there are numerous reasons why prefer Alter Database over Attach/detach method.

Attach/Detach Method: This method is used to move SQL server data or log files from one location to another location as mentioned above. Now have a look on the syntax of both the operations.

EXEC sp_detach_db 'your-database-name

After the successful detach of the database from SQL server instance, you can move the data & log file to a new location then after attach the files to new SQL server instance by below syntax.

EXEC sp_attach_db 'your-database-name
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\your-database-name_Data.mdf',
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\your-database-name_Log.LDF'

Microsoft’s online book topic sp_attach_db command has mentioned the below warning message:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use CREATE DATABASE database_name FOR ATTACH instead. For more information, see CREATE DATABASE (Transact-SQL).”

Alter Database Method: You cannot use this method without identifying the filenames associated with the database. So first identify the filenames by below query:

Select name, physical name from sys.master_files where database_id = ('your-database-name')

Now you will be able to use Alter database with Modify command for moving SQL server database files from one SQL server instance to another.

Aleter Database your-database-name Modify File (Name = your-database-name_Data, Filename = 'D:\SQLData\your-database-name_Data.mdf');
Aleter Database your-database-name Modify File (Name = your-database-name_Log, Filename = 'D:\SQLData\your-database-name_Log.ldf');

Now set your database as offline to complete the moving process by below syntax.
Alter Database your-database-name Set Offline
Move the files to a new location & bring your database online by below syntax.
Alter database your-database-name Set online

It will disable the service broker on the database.
It will not disable the service broker in the database.
It will disable the Trustworthy setting of the database.
It will not disable the Trustworthy setting for the database, if enableed.

Thursday, February 7, 2013

Need to attach an mdf file without transaction log file in SQL server

If someone has deleted your transaction log file intentionally and you have only .mdf file of your database then you can still attach your mdf file with the database. No matter your database was cleanly shutdown or not. In this article I will thoroughly guide you methods & steps which are required to recover your database when you have only .mdf file of the database.

Database was cleanly shutdown: If your SQL server database was cleanly shutdown then you can attach your database with the help of SQL server management studio (SSMS).

Let’s discuss all the steps in details:

  • Connect to the SQL server instance using SSMS
  • From the Object Explorer, right click the Databases node and a drop-down menu will be appeared.
  • Now click on the Attach tab.

  • Click on the Add button from appeared dialog box.

  • Now a Locate Database Files dialog box will be appeared.

  • Click on the browse button to select your database MDF file after choosing .mdf file click on the OK button.
  • Now you will see your mdf file is selected but SQL server is unable to select your transaction log file because transaction log file was deleted. To attach .mdf file without transaction log file; select the log file and click on the remove button to remove it. Now click on the OK button to attach the database file. SQL server will create a transaction log file for you while attaching the database.
Database was not cleanly shutdown: Try below steps if your database was not cleanly shutdown.
  • Create a same size database
  • Now shutdown the SQL server
  • Change the old mdf file with new one
  • Start the SQL server, your database may go in suspect mode
  • Now change your database status from suspect mode to emergency mode by ALTER Database command
  • Run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS. It will rebuild the log and run full repair
Note: You may loss some amount of data but your database will be available again.

Wednesday, January 23, 2013

Importance of DBCC CHECKDB Command

Quite often, it has been seen that some of the DBAs do not run DBCC CHECKDB command on his/her database. I don't understand why DBAs not perform this on their database regularly. It is very simple to run, and it saves you from big trouble later on.

What is DBCC CHECKDB Command?
This command checks logical & physical integrity of all the objects in the desired database. It includes all three command checks (DBCC CHECKALLOC, DBCC CHECKTABLE & DBCC CHECLCATALOG) in itself. If DBA has run DBCC CHECKDB command on the database then he/she should not three command on the database as DBCC CHECKDB includes all these commands. If corruption has occurred in the database due to some reason, DBCC CHECKDB command will check it and report you about which object have corrupted. It has three optional arguments that help you to fix the reported corruption, and the arguments are
  1. Repair_Rebuild: When you use this argument with DBCC CHECKDB, there is no possibility of data loss. It will try to repair & rebuild corrupt database without data loss.
  2. Repair_Fast: It does not perform any repair operation on the specified database but maintains the syntax for backward compatibility.
  3. Repair_Allow_Data_Loss: As name suggests, it will try to repair corrupt database with minimum data loss.
Note: Before running above repair arguments, make sure your database is in single-user mode. If database is in multi-user mode then ALTER it, and set it in single-user mode.

How do you use DBCC CHECKDB?
You can use it on your database by performing T-SQL

DBCC CHECKDB AdventureWorks
Replace AdventureWorks with the name of your database.

What is the best time interval to Run DBCC CHECKDB?
It depends upon your database importance, use, and so many other factors also. Running DBCC CHECKDB on the database regularly will not avoid the corruption to happen but it will help in determining the corruption as soon as it happens.

How to check last DBCC CHECKDB?
If you want to know, when had the last DBCC CHECKDB command performed on the database then run bellow command