FAQ: SQL Server Disaster Recovery

Q: Can I restore a SQL server database if I have data & log files?

A: Yes, you can restore a SQL server database if your data file & log file is not corrupt. You can do this by attaching data & log files to the SQL server.

Q: Can I restore a SQL server database if the data file is corrupt?

A: No, you cannot restore a SQL server database with a corrupt data file. You need to restore your database from a good known backup. If you don't have a good backup of your database, there is less change that you can do. There are some data recovery companies that claim they can recover data from the corrupt file by repairing the file. So you can go to the Internet and search the best SQL server mdf file recovery software and see what they can do for you.

Q: Can I attach a SQL server database without transaction log file if database was cleanly shutdown?

A: Yes, you can attach a SQL server database without transaction log file (.ldf) of the database. You can attach your database with the help of SQL Server Management Studio (SSMS). MS SQL server will create a transaction log file while attaching the database.

Q: Can I attach a SQL server database without transaction log file if database was not cleanly shutdown?

A: Yes, you can but method will be different. To do this, you have to perform below steps:

• Create a same size database as your previous database size
• Shutdown SQL server
• Change the old mdf file with new created mdf file
• Start SQL server, database may go in suspect mode
• Change the database mode from suspect to emergency by ALTER command
• Run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS.

Q: Is there any In-built utility in SQL server to repair corrupt file like Compact & Repair in MS Access?

A: Yes, MS SQL server has DBCC CHECKDB that performs almost similar activities as Compact & Repair does. DBCC CHECKDB has three options; repair_fast, repair_rebuild & repair_allow_data_loss.

Q: Can I loss data after running DBCC CHECKDB with repair_allow_data_loss?

A: Yes, you may. As the name of command suggests you may loss the data from your database. In some cases it has been observed that database administrator has not lost the data after running DBCC CHECKDN with repair_allow_data_loss command against the database.

Q: Can I backup transaction log if my database is using Simple recovery model?

A: No, you can't backup transaction log file when you database is in simple recovery model.

Q: What can I do to avoid data loss from corruption?

A: Backup, Backup & Backup. It is the best solution to avoid any data loss from corruption. Additionally, running DBCC CHECKDB command against the database on regular interval is a good habit. You may know the corruption as soon as it occurs.

Q: What is the best time interval to run DBCC CHECKDB?

A: It depends upon your business need. If you can afford 2-3 days database downtime then run it on monthly basis but I don't think so a company can afford 2-3 days database downtime.

Read More

Methods to Resolve SQL server Backup Error 3007

After upgrading SQL server database from SQL server 2000 to SQL server 2005. When you perform a full database backup of a SQL server 2005 database, you may got following error message on your system screen.

Executing the query "Backup Database [ZZZ] TO [ZZZ]

WITH NOFORMAT, NOINIT, NAME = N'ZZZ_backup_20060819040020', SKIP, REWIND, NOUNLOAD, STATS = 10

"failed with the following error:

"The backup of the file or filegroup "sysft_ZZZ_FT" is not permitted because it is not online.

BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. BACKUP DATABASE is terminating abnormally.".

Cause: SQL server error 3007 may occur when fulltext catalog is offline for the database ZZZ.

Resolution: You can resolve this error message with the help of two methods: SQL server management studio and drop & rebuild catalog .

With SQL server management studio: It is the simplest action to resolve error 3007. Follow below steps to resolve it.

• Open SQL server management studio (SSMS).
• Now go-to the storage container.
• Check the state of fulltext catalog.
• If there is a problem then resolve it.
• Now you will able to backup your database.

With Drop & Rebuild Catalog: If you are still unable to backup your database then drop & rebuild the catalog if catalog is needed otherwise drop it.

Note: You can take backup of your database when rebuild start. There is no need to wait for rebuild catalog.

Read More

Available Methods for Sending a Mail from SQL Server

-->

Are you looking for a method that enables you to send a mail from your SQL server? For you Microsoft SQL server offers a special feature that will enable you to send a mail from it. Earlier to SQL server 2000, it is known as SQL mail but with release of SQL Server 2005 it has been changed & the new name is database mail. Database mail is very useful for sending a mail from SQL server to end users as well as to database administrator. It can contain data from files, queries, or many other resources available on the network.

What is SQL Mail? It is available in earlier version of SQL server 2005 & uses Message application programming interface (MAPI) to send mail. You can't use it without installation of outlook.

What is Database Mail? It is an enhanced version of SQL mail that is available in SQL server 2005 & upper versions. The best thing is that there is no any requirement to install outlook first to use it. It uses simple mail transfer protocol (SMTP) for sending a mail. In this article I would discuss about how to configure database mail in SQL server 2005 because SQL mail is a very old fashion.

How to Configure Database mail? You can configure database mail with the help of SQL server management studio. For exercise, we will learn how to setup a database mail using SQL Server Management Studio.

• To setup database mail in SQL server 2005, connect to the instances of SQL server database.
• Browse the management.
• Now right click from your mouse on database mail.
• Select configure database mail section.
• Choose one option from list for a particular task.

How to Check Database is enabled or not? If you want to check database mail is enabled or not on your database then connect to an instance of SQL server database by a query editor and execute bellow: 

 

sp_configure 'show advanced', 1; GO RECONFIGURE; GO sp_configure; GO

Result: Go to the result pane and check “run_value” of the database. If run_value is 1 then database mail is enabled on your database otherwise not.

Read More

Best solution for you when your database is suspect mode due to insufficient space in filegroup?

SQL server uses filegroup for grouping all the database objects & files, there are two types of SQL server filegroup, one is primary & another is user-defined. Primary filegroup contains the primary data file and all the pages for system table while any other filegroups that are specified with Create or Alter statements are known as user-defined filegroups. No any file can be member of more than one filegroup and log file is not a part of any filegroup. It is managed from data space.

Note: One filegroup in each database is specified as a default filegroup. When you create a table or indexes in your database without specifying with any filegroup then it is assumed that all the pages are allocated from the default filegroup.

Consider a scenario wherein you are working on your SQL server 2000 database and suddenly unable to perform any operation on the database due to your database goes in suspect mode and got below error message:

Could not allocate space for object '%.*ls' in database '%.*ls' because the '%.*ls' filegroup is full

Cause: As specified by the above error message, your specified filegroup has insufficient space.

Possible Solution: There are two possible solutions for the above error message and the solutions are:

1. Free Disk Space
2. Add a Data File

1. Free Disk Space: You can gain free disk space by increasing the size of any disk drive that contains files for filegroup and allow filegriup to grow or can gain space by adding a data file with the specified database. If you cannot increase the size of disk drive that contains filegroup then move the filegroup to another disk drive. Follow the steps to free disk space on another drive:

• Move the data file in filegroup with insufficient free disk space to another drive.
• Now detach the database by executing stored procedure sp_detach_db.

Systax for detach stored procedure

sp_detach_db [ @dbname= ] 'database_name'

[ , [ @skipchecks= ] 'skipchecks' ]

[ , [ @keepfulltextindexfile = ] 'KeepFulltextIndexFile' ]

• And finally attach the database by stored procedure sp_attach_db

Systax for attach stored procedure

sp_attach_db [ @dbname= ] 'dbname'

, [ @filename1= ] 'filename_n' [ ,...16 ]

2. Add a Data File: You can add a file to specified database by using ADD file clause of ALTER database statement or enlarge the size of data file by using Modify clause of Alter database statement. 

Read More

Unraveled: Possible Resolution Methods to Work Around Database Corruption in SQL Server 2008

SQL Server 2008 has been specifically designed to work with large data sets and to handle multiple users. It facilitates ease of use and maintenance through its feature-laden application interface. It can easily interpret the bandwidth required by the users and considerably help to improve performance. However, it has a few downsides. Any unexpected server crash may leave your SQL database in a suspect state. This database corruption would thrive if no timely action is taken to counter the problem. In these circumstances, all your valuable database components become inaccessible. To overcome this problem, you should go for SQL server recovery.

Let us consider a real-world scenario. Suppose you use MS SQL Server 2008 on your Windows XP based computer. You may occasionally notice the below error message in your application log:

'Table error: Object ID O_ID, index ID I_ID, partition ID PN_ID, alloc unit ID A_ID (type TYPE). Duplicate keys on page P_ID1 slot SLOT1 and page P_ID2 slot SLOT2.'

The above error is generated by 'SQLengine' because there are two slots having the same or identical keys. In addition, the error may pop-up as a result of corruption in your SQL database.

To work around this problem, you need to isolate the cause of the error and accordingly, work out a solution. You can try the given steps in order to resolve the issue:

• Hardware failures and defects cause corruption in SQL databases more frequently than other sources. First off, you should view the SQL Server error log and the Windows system and application logs to check for hardware failures. If you encounter any hardware problem, run hardware diagnostics.
• If you continue to face problems, you should try swapping different hardware parts to examine the root cause. Additionally, ensure that the disk controller does not perform write caching.
• If the issue is not hardware-specific, you can try restoring the database from a recent clean backup.
• If you do not have a backup, run DBCC CHECKDB with an appropriate repair clause to fix database corruption.

If you receive the same error again, then take help of advanced third-party SQL database recovery software. These competent tools incorporate advanced, proprietary mechanisms to repair your damaged SQL database easily and recover all lost or inaccessible objects, such as tables, views, keys, indexes, constraints, and stored procedures. Furthermore, they are compatible with Windows 7, Server 2008, Vista, Server 2003 and XP.

Read More