How to take Full Database Backup in SQL Server

Full Database backup backup the whole SQL Server database. We can use Full Database backup to recover the full database as it includes part of the transaction log. You can find the size of full database backup by using the sp_spaceused system stored procedure. If you are taking long size full database backup then it will take  more time to finish and require more storage space. In this case, you should supplement a full database backup with a series of differential database backups. You can create full database backup by using three options: SQL Server Management Studio, Transact-SQL, PowerShell.

Before taking full backup, first check out its Limitations and Restrictions: 

• The BACKUP statement is not allowed in an explicit or implicit transaction.
• Backups that are created in SQL Server recent version that cannot be restored in SQL Server earlier versions.

Taking full backup by using SQL Server Management Studio:

• In Object Explorer, first connect to appropriate instance of the Microsoft SQL Server Database Engine,
• Click server name to expand the server tree.
• Expand Databases 
• Right-click the database, point to Tasks, and then click Back Up. 

The Back Up Database dialog box appears: 

• You can see all available options on the window. Update it:
• In the Database list box, verify the database name.  
• Database backup can be perform for any recovery model (FULL, BULK_LOGGED, or SIMPLE).
• In the Backup type list box, select Full.
• Backup component, click Database.
• Accept the suggested default backup set name or enter a different name.
• Optionally, in the Description text box, enter a description of the backup set.
• Specify when the backup set will expire. 
• Choose the backup destination by clicking Disk or Tape.  

To view or select the advanced options, click Options in the Select a page pane.

• Select an Overwrite Media options available on the picture: 

1. Back up to the existing media set
2. Back up to a new media set, and erase all existing backup sets

• In the Reliability section, optionally check:

1. Verify backup when finished.
2. Perform checksum before writing to media. 
3. Continue on checksum error. This option is optional. 

• If you are using  tape drive to backing up the database then 'the Unload the tape after backup, option is active. Clicking this option activates the Rewind the tape before unloading option.

• The next option is backup compression. The SQL Server 2008 Enterprise and later supports this feature. You can compress a backup by using this option, and you can prevent it by checking 'Do not compress backup'.

You can restore your corrupted whole SQL Server database by restoring the database from a full database backup to any location.  

»»  Read More...

Top 10 things Stellar Phoenix SQL Recovery user needs to know

Recover your corrupt SQL Server Database with a dedicated software application is a SQL Recovery Software provided by Stellar. The application runs directly from your system after installing software on specified location. Here I am sharing useful information of SQL Recovery Software. 

There are two types of files, mdf and ndf which manage your data in proper format. The ndf file is a secondary file, if you have a large amount of data; you probably have a secondary file (ndf) to save your data. The new feature is to recover large ndf files. Now you have no need to worry about large file recovery, Stellar SQL Recovery can also recover large corrupt ndf files. The SQL Recovery Software should run on copy of the corrupt file.

In any version of SQL Database, if you find any problem to find the corrupt mdf file then you can easily search it by using the "Find Option". Just click on this button & the application find into your system that appears immediately in list view format. You can choose the file that you want to recover from this list. 

The new version of SQL Server Database launched in 2011. Another new feature is- supports to latest version of SQL Server Database that is SQL Server 2008 R2. 

Fast scanning algorithms — you can recover database quickly & accurately without completing the whole recovery. 

In addition to the whole process information, the new set includes separate log report after scanning database which shows all recovery process. I am eager to see how this apps sharing this process in the software. 

You can also recover XML data types and XML indexes with this software.

You cannot save preview of your database in demo version. For this you have to purchase full version of the software.

You can check the preview more than one objects at a time. Click on either one to multiple objects to recover it. Save it quickly and easily on location as you like.

You should perform software updates as soon as they become available. The easiest way for users to update your software is through update button. If you are using software, when an update is available, you can see it by clicking update button the top fifth right button on the software screen. Just click on this button to install the new updates.

If any of option isn’t used by you just yet, don’t worry use it now & give your precious comments about it.

»»  Read More...

Three Stellar Phoenix SQL Recovery secrets that I bet you didn’t know

Stellar Phoenix SQL Recovery Software used systematic process to recover corrupt SQL Database and most of the new users don’t know about it. They simply thought that software recovered corrupt database, restoring database objects, put recovered data in to a new folder and then expect that database get recovered. But this is not all about the recovery process & the process doesn’t work that way. Yes it is true that these are the main functions of this software. But this software also has many advanced features that user may not be familiar with. Here are a few Software Recovery tips that I’d like to share with you guys:-

Save Queries of Severely Corrupted Objects: When database repairable objects preview shows in demo version then the user are sure to purchase the software but when the software won't shows any data in preview section it does not mean that the software is not able to recover the database, this is only first point which we should keep in our mind. The other points are that database gets severely damage. In this case may be other software also won't work. This software has feature to handle severely corruption: It permits database administrator to retrieve the query of the objects & save it in a Text file. With this advanced feature users don’t have to write long queries again. 

Give your database recovery process a fast start and do not need to wait until finish scanning process: This software has another advanced feature which allow user to select any specific objects to be recover from the list of recoverable SQL Data  without create any interruption in scanning process. So users have no need to wait till the end of the scanning process.

Database Recovery means a lot:  Stellar Phoenix understand the value of your database & have all efficient features to recover your corrupt SQL Server database. It offers easy steps to recover database such as full information about the software in help file, find option to search the database file if you forgot the location, recover almost all SQL Server components, recover both files mdf & ndf etc. 

I am sure the share information on SQL Recovery is very useful for you. In case you have found anything which I have forgot to mention in my post please let me know I will surely add it in my next post. 

»»  Read More...

Why Stellar Phoenix SQL Recovery becomes a first choice of the users

The user conversation: Questions and Answers:

All the questions comes in users mind with these words “WHY What WHO WHERE WHEN, How?? To better understand about the software, how it works?? and why to choose it?? here are some answers to such  questions that many user may have in their mind to ask:

 

Why You prefer Stellar Phoenix SQL Recovery Software?
As per my opinion the best software is one that makes iTunes accessible to blind. It means I am recommending best software that is perfect for novice user as well for techi one. I have checked the software interface which is very easy to understand, each & every point mention in the software make it user friendly.. I highly appreciate this software is compatible with all the latest version of SQL Server & Windows. 

 

Have You find any pitfalls in it?
This is more a personal opinion: If my calculations are correct, for heavy database it performs slow recovery. I understand that it is a tough task because the recovery scanning process of SQL database in vast mode with various SQL queries to get maximum recovery of SQL database is a tough task. I wish in the coming version it become fast so that it become best software for SQL Recovery.

 

What narrowing your focus that makes it unique with other one?
I do not think any other software is “the new choice of mine”!! I know that Stellar is irreplaceable, but On the other hand I also want to say, It is better to add some other great features in new version to make it more effective with other one.

 

This is all we know right now, just over an hour of the client conversation.

 

Useful Note for every user: It is high recommendation to check user manual & demo version of the software before purchasing it. Demo version will show you that all your SQL data is recoverable by the software or not. User manual have full information about the software from A to Z.

»»  Read More...

Stuck with SQL Database Recovery Process? Sit Back and Keep reading this article

Image via Wikipedia

When I start SQL Server application, it starts to recover all three SQL Server database files (MDF, NDF, LDF). Due to this all transactions are either committed or rolled-back. I suffer with one another problem: The server get terminated in the middle of a long running update, Normally the recovery process takes a few minutes, but after server termination it takes plenty of time like we update SQL Server.

 

Steps that I follow:

• Give it enough time to recover SQL Server ,
• for checking what exactly happened, I  checked the current and previous errorlog files, and NT errorlogs,
• After that I check recovery progress for this I set trace flag 3412 which writes an entry to the errorlog when each transaction is rolled forward or back.

In very rare cases SQL Server is not recover the database correctly. After completing these steps until the recovery process is take time to complete. You can restore SQL server database with updated backup. If you do NOT have a backup then you can use the following trace flags to recover SQL Server database:

• If the database is still unavailable - marked as suspect - then run the following command to put the SQL database into emergency mode.

  UPDATE master..sysdatabases SET status=-32768 WHERE name='<dbname>'
         
       Then you can access into the database and extract out the data.

• After performing all steps if you are unsure what to do, then don't hesitate to use the SQL Recovery Software provided by Stellar Phoenix. They are available 24x5 to deal with the customer problems and prevent your data with data loss.

Small Overview About the software

• Software recovers MDF & NDF Files
• Recovers almost all objects of SQL Server Database
• Recover database in most possible cause of corruption
• Support all latest version of SQL Server & Windows OS

You can check the site for more detailed information about the software. If this article is useful for you & solve your SQL Recovery issue, write your valuable comment!!!

»»  Read More...

Consequences of Error 8962 in SQL Server 2000 and steps to perform SQL Recovery

MS SQL Server has been a cynosure of every database user because of its streamlined installation, high performance, and security features. You can easily reduce the size of your database using its highly advanced, transparent compressions algorithms. It also enables data encryption without having the need to change or modify programs. A range of built-in data mining, disk partitioning, and data management tools make data storage and retrieval a breeze for the end user. Despite these benefits, SQL databases are subject to corruption. Corruption may occur due to several reasons, such as virus infection, application errors, operating system malfunction, etc. Due to this, all our vital database objects become inaccessible. To overcome this problem, you need to go for SQL recovery through a reliable third-party utility.   

For instance, while using an SQL Server 2000 database, you may come across the following error message:

“Table error: The text, ntext, or image node at page P_ID, slot S_ID, text ID TEXT_ID has incorrect node type NODE_TYPE.”

Cause:

 

The above error occurred because a database page contains a text node having invalid or incorrect type. This usually happens when there is corruption in the database.

 

This might also result in complete database inaccessibility. A recent/updated backup would help you restore all your lost or inaccessible data. However, if the backup is unavailable, follow the below mentioned resolution steps.

 

Resolution:
 

The following steps may help you resolve the issue-

• You need to check the error logs to get information about any hardware related issues. If there is a hardware malfunction, run hardware diagnostics or try replacing the damaged hardware component.  
• Use DBCC CHECKDB to fix logical database corruption.

 

If the problem still persists, you should take help of competent SQL database recovery software. These third-party tools use various safe and comprehensive techniques to repair corrupt SQL databases and retrieve almost every piece of valuable information from your corrupt database. 

 

Stellar Phoenix SQL Recovery is a proficient utility that repairs and recovers corrupt MDF files of MS SQL Server 2008, 2005, 2000, 7.0. Furthermore, the software is compatible with Windows 7, Server 2008, Vista, Server 2003, XP, and 2000.

»»  Read More...

Discussing repercussions of database corruption in SQL Server 2005 and adequate measures of recovery

Image via Wikipedia

MS SQL Sever 2005 unleashes the most advanced database management features that effectively cater to all the needs of a normal or an advanced database user. It has a new XML data type to support XML integration. You can develop custom applications for your server using SQL Server Management Objects (SMO). The most important feature is its dynamic configuration. It allows you to change the configuration values and let the changes take affect as soon as they are implemented. Despite having such powerful features, an SQL Server 2005 database gets easily corrupt due to various reasons. The primary reasons responsible for database corruption are virus infection, hardware errors, application errors, abnormal system shutdown, etc. As a consequence, you lose access to all the valuable objects in the database. To overcome this problem, you need to go for SQL recovery through a reliable third-party utility.

 

For instance, you may receive the following error message while using an SQL Server 2005 database:   

 

“Cannot find a table or object with the name 'NAME'. Check the system catalog.”

 

Cause:

The above error may occur due to any of the following reasons:
 

• You have not specified the correct object name.
• You have dropped or deleted the object before issuing the query.
• You do not have appropriate permissions to access the specified object.

 

Another reason could be that the specified object is damaged or corrupt. This would possibly render the database object inaccessible and lead to loss of valuable information. In such circumstances, you may use a backup to restore the lost data. However, if the backup is unavailable, follow the below given resolution steps.   

Resolution:

The following steps may help you resolve the problem-

• Check whether you are using the correct database.
• Verify if the specified object exists in the database.
• Also check whether you have specified the correct schema name for the object.
• Check whether the system tables contain information about the object. For that matter, you can query the 'sys.objects' catalog view.

If the problems still persists, you should take help of proficient SQL database recovery tool. These third-party software are capable of safely restoring all vital SQL database objects in any event of database corruption or inaccessibility. They perform in-depth scan of the corrupt database and also allow you to save the desired objects at your specified location in the system. Moreover, they are compatible with Windows 7, Server 2008, Vista, Server 2003, XP, and 2000. 

»»  Read More...

How to Resolve Error-824 in SQL Server Database

Error 824 is a logical IO error. Logical IO error means that the page is read from the disk successfully, but there is something wrong with the page. The error-824 caused due to many reasons, some are:

• Problem with underlying storage system.
• The hardware or a driver that is in the path of the I/O request.
• There are inconsistencies in the file system.
• The database file is damaged.
• File system corruption.

Here is more explanation with a live example of error-824:

 

Msg 824, Level 24, State 2, Line 1.
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x7532c420; actual: 0x320e4240). It occurred during a read of page (1:342) in database ID 15 at offset 0x00000020e24000 in file ‘D:\Data\SomeDB.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

 

A logical consistency error is a clear indication of actual damage and frequently indicates database corruption caused by a faulty I/O subsystem component.
 

It can be fixed without data loss, you only need to refer to your backups and perform a restore.
 

Follow these simple steps, if you don't have updated backup:
 

• Stop SQL Service.
• Copy and Save your corrupted SQL Server database.
• Now Start SQL Service
• Create an empty SQL Server database with the same name.
• Detach the empty database and Replace it with the corrupted ones in the SQL instance folder or where you keep your databases.

After applying the above steps run the query given below:
 

EXEC sp_resetstatus 'yourDBname' ;
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname ')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC CheckDB ('yourDBname' , REPAIR_ALLOW_ DATA_LOSS) ALTER DATABASE yourDBname SET MULTI_USER

 

In case the following procedure won't work then it is recommended to go for third party SQL recovery software to resolve Error-824 in SQL Server Database.

»»  Read More...

5 Easy steps to fix SQL Database Corruption Error in MS SQL Server database

Are you facing sql database corruption error messages, when you work on MS SQL Server database? The SQL database corruption occurs due to several reasons such as virus infections, software corruption, improper system shutdown, Hardware malfunctioning etc. In such instances, user should be use in-built SQL repair inbuilt utilities.

Take a live example of an error message that occurs after SQL database corruption

“Table error: Page P_ID, slot S_ID, column C_ID is not a valid complex column.”

The error code of this error message is Error 8960 which occurs due to several reasons.

Some main causes of this issue are:

• SQL database corruption
• Table records get corrupt
• hardware corruption issues

5 main keys to resolve this error message:

1. Run DBCC CHECKDB: First run  DBCC CHECKDB command to check database errors. If DBCC CHECKDB reports database corruption error then correct it immediately otherwise you lost your data.

2. Use DBCC CHECKTABLE command to repair corrupt table records:
   DBCC CHECKTABLE ('tablename', REPAIR_ALLOW_DATA_LOSS)

3. Correct hardware-related corruption issues by performing the following actions:

• First check the error logs in MS SQL Server to verify if there is any hardware corruption or not.
• Swapping the hardware components to check the actual cause of sql database corruption.
• Reinstalling the OS after formatting the hard disks.

4. Restore corrupt SQL database from updated backup: If the problem is not hardware-related then you should restore your corrupt database from updated backup.

5. If you are still getting the same error message the you should try  Third-party SQL Database Recovery Software to repair corrupt SQL database. These software repairs corrupt mdf files as well as ndf files.

»»  Read More...

How to resolve error: “mdf is not a primary database file”

I want to share my personal experience on SQL Server Database. One day I was trying to set my SQL database online with command:

ALTER DATABASE mydb SET online

& suddenly I got an error message:

Msg 5171, Level 16, State 1, Line 1
E:\Data\mydb_log.ldf is not a primary database file.
Msg 5171, Level 16, State 2, Line 1
E:\Data\mydb.mdf is not a primary database file.
File activation failure. The physical file name "E:\Data\mydb.mdf" may be incorrect.
Msg 945, Level 14, State 2, Line 1
Database 'mydb' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

After that I do research on this error & I found this error occurs in many situations like :

1) When you are trying to attach your SQL database files with windows then also you get same error message. Mostly this error occurs in SQL Server 2005.

2) When you are trying to set your database online then also you get same error message.

Cause: 

The main cause of this error is that your mdf file get corrupt.

Solution: 

There is four solutions to resolve this error:

1. First you should check the database by this command “DBCC CheckDB”. It shows the details of the database then you can find the problem & easily resolve it.

2. You can tried to add the file using the command sp_attach_single_file_db. 

3. Try to restore database from a bak file.

4. It is possible that the database may have been corrupted when it was downloaded & “DBCC CheckDB” reflects damages in SQL server MDF file. You also don't have updated bak file. In this case you should try SQL Recovery Software. 

Note: If data get overwrite no one can repair it. 

• When you find that your mdf file has been corrupt, don't perform any activity on the file. If there is 80% chances to resolve this error it will becomes 10%. because may be your data get overwrite from your activities & you will never get back your data. 
• Before purchasing any SQL recovery software, first download its demo version & check the preview of your recovered database after that purchase it.

»»  Read More...