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...

How to handle corruption in Oracle Database using DBVERIFY

If you have encountered Oracle database corruption problems then you can use 'DBVERIFY' command-line utility to fix corruption. DBVERIFY performs a physical data structure integrity check to find corruption in oracle database. It is an external command-line utility that can be use on offline or online databases. We can only DBVERIFY with datafiles, it is not work against control files. Here are some examples of common Oracle Database Corruption Error messages & Solutions:  

• The file does not exist or was incorrectly specified. In this case you should first check that the specified file exists.
• The datafile to be verified must end with ‘.dbf’. In this case, you should try renaming the file  
• The file is in use. In this case, you should shutdown Oracle and try again.
• If the database gets sudden shutdown then in this case you should try to stop the Oracle service ‘OracleService<SID>.

These are some general solutions which may work or not. Through DBVERIFY utility, you can fix most of the corruption error messages & can also prevent and manage block corruption.

How it works: DBVERIFY works in to two command-line interfaces: 

1. In the first interface, you specify disk blocks of a single datafile for checking: In this interface, DBVERIFY scans single datafile disk blocks & then performs page checks.

Syntax: 

The mean of all Parameters in this diagram are: 

USERID	As you can also see in the diagram that USERID=username+password. It is only necessary if the files to verify are Oracle ASM files.
FILE	The database file name to verify.
START	The starting block address to verify.
END	The ending blocks address to verify.
BLOCKSIZE	It is only required only if the file block size to be verified is not have 2 KB.
HIGH_SCN	This parameter is optional.
LOGFILE	Specifies the name of file to which logging information should be written.
FEEDBACK	Causes DBVERIFY to send a progress display to the terminal.
HELP	It provides online help.
PARFILE	Specifies the parameter file name to use.

Sample use of DBVERIFY Command-Line Interface for this mode: 

% dbv FILE=t_db1.dbf FEEDBACK=100

2. In the second interface, you specify a segment for checking: In this interface, DBVERIFY specify a table or index segment for verification. Through this check, it make sure that a row chain pointer is within the segment being verified.

Syntax:

 All parameters are same meaning except SEGMENT_ID. 

SEGMENT_ID

Specifies the segment to verify.

Sample use of DBVERIFY Command-Line Interface for this mode: 

dbv USERID=username/password SEGMENT_ID=tsn.segfile.segblock

Sample DBVERIFY Output for both mode: 

•Total Pages Examined– The number of blocks.

•Total Pages Processed (Data)–The number of blocks that contains table data.

•Total Pages Failing (Data) – The number of table blocks that have corruption.

•Total Pages Processed (Index)-The number of blocks inspected that contains index data.

•Total Pages Failing (Index) – The number of index blocks that are corrupted.

•Total Pages Processed (Seg) – The command to specify a segment that spans multiple files.

•Total Pages Failing (Seg) – The number of segment data blocks that are corrupted.

•Total Pages Empty– Number of unused blocks discovered in the file.

•Total Pages Marked Corrupt– It shows the number of corrupt blocks discovered during the scan.

•Total Pages Influx– The number of pages that were re-read due to the page being in use.  

If the corruption is not fixed by this utility then, the file gets severely corrupted. For severely corrupted database, you should follow steps in this given article.

»»  Read More...