SQL SERVER 2012 – Auto Recovery Feature Overview

Have you ever face the SQL Server Management Studio crashes issue without saving the SQL scripts. In this case, SQL SERVER Auto Recovery Scripts is one of the helpful features introduced in SQL Server 2012. It automatically saves open SQL scripts according to user definable period of time. By default, this time period is set for 5 minutes. User can change it according to their needs. By default, this feature is turned on with default settings in SQL SERVER 2012 (SSMS). 

Location of Auto Recovery Option in SSMS:

This option is available in Tools >> Options >> Environment >> AutoRecover

Settings available in Auto Recovery:

Given below are the settings available in Auto Recovery.

1. Save AutoRecover Information every: By this setting, you can set automatic saving of your script; it means after how many minutes your script should be saved automatically. By default, this setting is after 5 minutes. 
2. Keep AutoRecover information for : By this setting, you can set for how many days SSMS should keep those files. By default, this setting is 7 days.

Here is full overview picture of Auto Recovery setting:

You can find the recovery files here: C:\Users\windows user\Documents\SQL Server Management Studio\Backup Files\Solution1

NOTE: SSMS window might get hang for some time due to “Saving Auto Recovery information“.

If you want to disable this feature then you can also do this by using two methods:

First Method:

Open SSMS 2012 & click on Tools >> Options

Uncheck "Save AutoRecover information every" checkbox to disable Auto Recovery:





Second Method:

You can also do it by make adjustment in registry setting.

1.  Open Registry

Start -> Run -> RegEdit

2. Find and modify registry value

The location of this registry key in SSMS 2012:
HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\11.0\General\AutoRecover

When you double click on “AutoRecover Enabled" then its value will be "1" (ON), it means that Auto Recovery setting is enable. To disable Auto Recovery setting, set it to "0" (OFF) then press OK to save. 

So you can easily recover your unsaved SQL script from SQL Server Management Studio by using SQL Server 2012 AutoRecovery Feature.

»»  Read More...

Overview of Access Database 2013 Apps

Access app is an advanced feature of Access Database 2013 that is used in web browser for sharing and collaboration within your organization. To use an Access app, you need Access 2013 and SharePoint Server 2013. You can create an app using two options: 

From a template:

• Click on Access startup screen>> featured templates>>click on globe icon to create Access apps:

• Click on template and enter a name in the App Name box.
• Select a web location for the app 
• click Create.

From a scratch: 

• Open Access>>click on Custom web app

• Enter a name
• Select Web location for your app 
• click Create.

What NEXT?

Now you will need to add some tables in your new created app. You can either add template tables or just start a new blank table. 


To add a template table:  As shown in figure just type in search box what kind of information you want to store like tasks, employees, projects etc. Now click on Search button and select a suitable table from the list: 






To add new blank table:  

• Click on Add a new blank table.
• Now You will the newly created tables in the left pane.

Now WHAT?

Enter Data in the Tables: 

• Home tab>>click Open in Browser
• Select the table and view you want to use
• use the action bar buttons to add, edit, and delete items.

Import data from an external source:

As shown in figure, You can also import data like Access desktop database, a Microsoft Excel file, an ODBC Data, a text/CSV file, or a SharePoint list. This data will be added as a new table. 

• Start App>>open it in Access (in the browser, click Settings > Customize in Access)
• click Home > Table.
• Now you can see “Add Tables page” as clearly shown in picture. 
• At the bottom of this page, click the type of data source you want to import, and follow the instructions in the Get External Data wizard.

VERY VERY IMPORTANT:

Microsoft also add backup feature in Access web apps so that you can take a backup of your apps that can be restored to a different location. You can create web apps backup copies in App Package files and restore them by uploading them to any SharePoint 2013 site. 

Saving your App


To backup your app, save it as a SharePoint app package by clicking File > Save As > Save as Package. 






Now App Package file is saved to your system.

»»  Read More...

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 reduce the mdf file size

When we shrink a data file, SQL Server first move pages at the beginning the file. This process frees up the space at the end of the file & the file can be shrunk.  There are two commands to shrink a database file, first is DBCC SHRINKDATABASE which targets all files for the database & the other one is DBCC SHRINKFILE which targets a particular database file. You should always preferred DBCC SHRINKFILE if you want to shrink a single file. You can also shrink the database via the Management Studio. First you should check the free space that you have in the database by running "sp_spaceused" in the database that you need to shrink. Here is full syntax:

sp_spaceused [[ @objname = ] 'objname' ] 
[,[ @updateusage = ] 'updateusage' ]
        
If there is free space in the database than you can use following methods to shrink you MDF file:

1.Use Object Explorer in SQL Server Management Studio to shrink the mdf file:

• Object Explorer>>connect to an SQL Server Database Engine instance>>expand that instance.
• Expand Databases>>right-click the database that you want to shrink.
• Tasks>>Shrink>>Files

• Select File Type>> File name
• Optional: Click on 'Release unused space' check box.
• Optional: enter the maximum percentage of free space to be left in the database file after the database has been shrunk. Permissible values are between 0 and 99. This option is only available when Reorganize files before releasing unused space is enabled.
• Optional: Click on the 'Empty file by migrating the data to other files in the same filegroup' check box. You can use this option to move all your data from the specified file to other files in the same filegroup.
• Click OK.

2. Run a shrink file using the DBCC shrinkfile command: 

DBCC SHRINKFILE 
(
{ file_name | file_id } 
{ [ , EMPTYFILE ] 
| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
[ WITH NO_INFOMSGS ]

NOTE: The database cannot be made smaller than the minimum size of the database that is specified when the database was originally created. 

Some Remarking Points:

• You can apply DBCC SHRINKFILE to the current database files.
• You can stop DBCC SHRINKFILE operations at any point in the process.
• The DBCC SHRINKFILE operation shows an error message when it get failed.
• The database should not be in single-user mode when it being shrunk.
• Other users can be work on the database when the file is shrunk. 

Shrinking a database should be a rare operation and should not be part of your regular database maintenance. Heavy shrinking and growing of database files will fragment your file system, which will further hurt SQL Server performance.

»»  Read More...

How to create a desktop database in Access 2013

Access database used to store data & track information about the data. Information can be of any type like inventory, contacts etc. There are many steps to create & add data in access desktop 2013.

Let's have a look of all the steps:

To create desktop database in Access 2013, first you have to start Access Database 2013:

First Start Access:

1. In Windows 7, Start button>>All Programs>>Microsoft Office 2013>>Access 2013
2. Click on Access 2013

After that you will see the Start screen of Access 2013:

Create Blank Desktop Database: In start screen, you will see the option of 'Blank Desktop Database', Click on that button.

After that enter the file name in text box & Click on 'Create' button:

Now you will see this picture:

In this screen, there are main 5 sections that are used in most of the time:

FILE: We can use this option to open, save & close file just like in other access database versions.

HOME: This option is just like other access database versions.

CREATE: Through this option, you can create table, forms, reports etc. First time when you open access database 2013, you see a blank table in Datasheet view where you can add data. To add another table, use following steps:

• Create tab > Table.
• Now You can easily enter data in the empty field.

External Data: You can use this option to Import or link external data. To link & import data, use following steps:

Click on External Data tab, click on the data format that you want to import from or linking to. Or if the format is not visible in this option then click on More to find out more format options

Now Follow the instructions in the Get External Data dialog box.

Database Tools: You can use database tools to manage your data. There is one tool that is Table Analyzer Wizard which you can use to organize the data into separate tables. Here are all steps to use this tool:

• Open the Access database table that you want to analyze.
• Click Database Tools > Analyze Table.

Are you using Access desktop database? If so, let us know your thoughts in the comments below!

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

Learn All About SQL Server Database Recovery Advisor 2012

Database Recovery Advisor is the new feature of SQL Server 2012. It is the best feature of SQL Server 2012 that provides a better way to restore databases with the help of SQL Server Management Studio. Database Recovery Advisor restores databases to a point in time recovery. As we all knows that SQL Server Database provides a various types of backup & for the user, it is very tough to create the right recovery sequence. That’s why Microsoft introduces Database Recovery Advisor in SQL Server 2012 to make recovery process easy & also help users to create a right recovery sequence.  

For using this option, First Right-click on your database that you want to restore and then click on tasks>>Restore>>Database 

After that, you will see this picture: 

Click on Timeline option as shown in picture. After that, you will see this picture:

As you can see in this picture, by default, Database Recovery Advisor restores the database from the 'last backup taken'. You can also see an option in this picture that is 'Specific Date & Time'. It is called visual timeline feature which you can use to restore the database to an earlier point than last full backup. Now Choose “Specific date and time” option and after that use the arrow to specify the appropriate restore point & then click OK button.

It will navigate you to Database Restore dialog box as you can see in below picture: 

After that Click on OK button to start restore process. After completing the restore process, you will see this window: 

Congratulation you have successfully restores your database!!

»»  Read More...

How to maintain MySQL MyISAM Table

You should maintain your MySQL database table to prevent from corruption. MyISAM Table maintenance can be done by using these four commands:  

1.CHECK TABLE: use to check MyISAM tables 
2.REPAIR TABLE: use to repair MyISAM tables, 
3.OPTIMIZE TABLE: use to optimize MyISAM tables
4.ANALYZE TABLE: use to analyze MyISAM tables

First Check your table: CHECK TABLE: This command checks your tables for errors.

CHECK TABLE tbl_name [, tbl_name] ... [option] …

option = {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

Type	Meaning
QUICK	This option don't check for incorrect links when scan the row.
FAST	This option only checks tables that are not properly closed.
CHANGED	This option only checks tables that are changed since the last check.
MEDIUM	This option scans the rows to verify that deleted links are valid.
EXTENDED	This option check that the table is 100% consistent, but takes a long time.

If your table is already gets corrupted then you should use REPAIR TABLE command to successfully repair your database table. If your tables get corrupt then maybe you get an error message just like this 'incorrect key file for table: '...'. Try to repair it' or when you run Queries then it returns incomplete results or don't find rows in the table. 

There is various cause of MyISAM Table Corruption: 

• Sudden computer shutdown 
• Hardware failures.
• mysqld process get stop in the middle of a write.
• Software bug in the MySQL or MyISAM code. 
• & many more..

REPAIR TABLE: This command repairs a possibly corrupted table.

REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE
    tbl_name [, tbl_name]...
    [QUICK] [EXTENDED] [USE_FRM]

Type	Meaning
QUICK	This option repairs only the index file, and not the data file.
EXTENDED	This option creates the index row by row .
USE_FRM	This option used when .MYI index file is missing or if its header is corrupted.

OPTIMIZE TABLE: This command used to reduce storage space and improve I/O efficiency when accessing the table.

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE
    tbl_name [, tbl_name]...

When you delete the large part of a MyISAM table then these Deleted rows are stored in a linked list. In this case, You can use OPTIMIZE TABLE to reclaim the unused space. So we can say you should use this command when:

1.If the table has large part deleted rows
2.If the index pages are not sorted
3.If the table's statistics are not up to date

ANALYZE TABLE: This command used to analyzes the table and stores the key distribution for a table. Key distribution used to decide the order in which tables should be joined & when you perform a join on the table.

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE
    tbl_name [, tbl_name]...

So this is all about MyISAM Table maintenance. You can also use mysqlcheck & myisamchk to check & repair your MyISAM Table. 

»»  Read More...

How to resolve Access Database Error: 'An error occurred while loading Form_FormName'

Have you ever suffer with this error message: 'An error occurred while loading Form_FormName'. I had faced this issue last night. Here is full scenario: 

Real Life Scenario: When I was trying to open the database, the message pop up:

"An error occurred while opening 'form'. 

After that it asked me 'Do you want to continue loading the project?, Then I clicked on 'Yes'. After that I got this error message: 

"The form name 'form_old' is misspelled or refers to a form that doesn't exist." 

When I clicked OK, I got the same message total of 3 times & Then finally the database got open. If you are also facing this problem then use following steps that I used to fix the issue:

Solution: This error means that your database is partially got corrupted. This error occurs when you tried to convert your database from one version to another. To resolve this issue, Decompile the copy of your database: 

1.First make a copy of the mdb file when the Access database is not running.

2.Compact your database:

In Access 2010, Compact and Repair Database on the Database Tools ribbon.
In Access 2007, click Office Button (top left) | Manage | Compact.
In Access 95 - 2003, Tools | Database Utilities | Compact.

3.After that Close your database & Open command prompt, and type this in command prompt and include the quotes:

 "c:\Program Files\Microsoft office\office\msaccess.exe" /decompile "c:\My Documents\Database.mdb"

4.Compact again your database.

5.Try the conversion again.

This is the solution which I used to fix my issue. But one of my friends told me that this step got failed to resolve this error message. He suggested me to try Third Party Access Recovery Tool. Then Again I tried Third party tool in the same scenario. The tool successfully repaired my database which was partially got corrupted. Both solutions worked well in my scenario. Good Luck for you to fix your issue by above methods!! 

If you have any other solutions, please share in comment. 

»»  Read More...

How to resolve 'database is in use' error while restoring SQL Server database

Have your SQL Database Restoration get failed with a message saying the 'database is in use'.  This error occurs when any other users are connected to the database then the restore get failed & shows following error message: 

Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

To resolve this issue, you need to drop all other connections or the database that user are using should to be changed so that they will not use that database that you are going to restore.  You can use sp_who2 or SSMS to see what connections are using the database. 

To resolve this issue:

1. Put the database in single-user mode 

In SQL Server 2005 you do this by right clicking on the database - > properties and changing the relative attribute from multiuser to single user. After performing restore operation set again it to multiuser.  

Or you can also use this TSQL command:

use master
alter database xyz set single_user with rollback immediate
restore database xyz ...
alter database xyz set multi_user

2. Set your database offline:

If it is Still giving same error message after keeping database in single_user mode then you should just set it offline before running the restore option. This is the easiest way to kill all connections to the SQL Server database: 

use master
alter database MyDatabase set offline with rollback immediate

After that you can successfully restore your SQL Server database & all authenticated SQL Server database users can connect to the database again. 

»»  Read More...