Sunday, November 22, 2015

Tips for Using Triggers in SQL Server Database

Triggers are most important part of the SQL Server Database. Triggers are the special stored procedures which are used to perform actions on the database. These actions have different categories like: DDL, DML and logon etc.

Tip 1: Find the list of triggers using sp_MSforeachtable

If you want to see the list of the triggers along with the schema then, you can use the sp_MSforeachtable to get it. Use this stored procedure like this:

USE AdventureWorks2012
EXEC sp_MSforeachtable
@command1 = 'sp_helptrigger ''?''' ,@whereand = 'And Object_id In (Select parent_id From sys.triggers)';

After executing it, you will get the list of the triggers as the output. See the below image which shows the out of the above query:

In the above output, you can see the different columns with the details like: trigger name, owner, operations and schema.  
Tip 2: Find the list of enabled and disabled triggers

Every database has tables and if the user creates any trigger then it comes up under table section. A User has options to enable and disable triggers by clicking on it or by the query. Just right click on the trigger which you want to enable or disable it and it will look like this:

To enabled or disabled the trigger using query, just execute the below queries.

Disable the trigger

USE Database_name
DISABLE TRIGGER Trigger_Name ON Table_Name

Enable the trigger:

USE Database_name
ENABLE TRIGGER Trigger_Name ON Table_Name

To see the status of all the triggers, simply run the following query:
Select * from sys.triggers

In this case, the user will get the status of all triggers presented in the database.

In the above image, the red rectangular box showing the status of the trigger. In this section, the output always presents in the binary form (0 or 1). In the above image the trigger “Trg_InsertSalePerson” is disabled because its binary value is 1 in the “is_disable” column. To check its result, now enable the same trigger and you will get this message on the screen:

Now run the same query again
Select * from sys.triggers
At this time, the output will show zeros for both the triggers:

We have one more option to see the status of the triggers. Execute this query 

Select AS TableName,
Schema_name( AS Table_SchemaName, AS TriggerName,
WHEN TRG.is_disabled = 0 THEN ‘Enable’
ELSE ‘Disable’
END AS TRG_Status 
From sys.triggers TRG
INNER JOIN sys.tables TBL
ON TBL.OBJECT_ID = TRG.parent_id
AND is_disabled = 0

The output will be:

Now, the user can see the difference TRG_Status and both are enabled. So these are the simple tips about to find the status of triggers. 
»» 

Thursday, October 29, 2015

Storage Engines in MySQL

MySQL is an open-source RDBMS (Relational Database Management System) which is widely used RDBMS after Oracle. It supports different storage engines and these storage engines have their own properties. In this article, we will discuss few storage engines of MySQL.

InnoDB Storage Engine: It is default storage engine for MySQL 5.5 and later versions. File formats of this storage engine are .idb, .frm and .ibdata. InnoDB provides the compliant transaction function of ACID, along with foreign key support. It has the ability to maintain its own buffer pool for indexing in the main memory and data caching. Main goals of InnoDB storage engine are Portability, data security, OLTP orientation, performance, reliability, and scalability etc.

Key functional characteristics:

  • Auto recovery from crash
  • Efficient IO
  • Full transactional support (COMMIT and ROLLBACK)
  • MVCC (Multiversion Concurrency Control)
  • Row-level locking
  • Row data stored in the primary key format
  • User can freely mix the InnoDB tables from other storage engines

Disadvantage of InnoDB Storage Engine

  • It Doesn’t support Hash Indexes and Full-text search indexes
  • No cluster database support
  • It is not available in classic edition
The Architecture of InnoDB Storage Engine: InnoDB architecture is well-suited to modern, online transactional applications as well as embedded applications. Architecture of the InnoDB storage engine is shown in the below:

MyISAM Storage Engine: It was the default storage engine for the prior 5.5 version for MySQL database. MySQL uses the .frm file to store the definition of the tables so this is also supported by MyISAM. The main file extensions for this storage engine are.MYD (MYData) and .MYI (MYIndex). MyISAM is the improved version of MySQL, ISAM. After MySQL 3.23, MyISAM replaced ISAM as the default storage engine.

Advantages of MyISAM Storage Engine:
  • Simplicity: It is very easy to use especially for a newbie. 
  • Speed: It is very fast under the normal condition.
  • Full-text Searching
Disadvantages of MyISAM Storage Engine: 
  • It does not support transaction or foreign key constraints.
  • It has a poor crash recovery which can be fixed using REPAIR TABLE, but it needs an additional administrative overhead. 
  • It uses table locking feature. In this feature, when a row is inserted or updated then the other modification on the table helps up until that particular request has been completed.
  • No order for the data storage
Falcon Storage Engine: Falcon storage engine was specially developed for MySQL RDBMS. When Oracle purchased MySQL then its development was stopped. The main feature of Falcon is transactions.

Advantages of Falcon Storage Engine:
  • It supports COMMIT/ROLLBACK
  • Row level concurrency by MVCC
  • Non-blocking read & crash recovery
  • Fine Grained multi-threading
  • Use threads and processor for the data migration
  • Support web application
Disadvantages of Falcon Storage Engine:
  • It does not use clustered index architecture 
  • It does not support “Dirty” reads isolation level
  • It does not support statement based replication
MySQL Federated Storage Engine: This storage engine gives the facility to create the table form representation of foreign (Remote) tables.

Advantages of MySQL Federated Storage Engine:
  • Useful for ad-hoc queries
  • Server side replication filtering
  • Useful for replicate the triggers actions from the other non-replicated database
Disadvantages of MySQL Federated Storage Engine:
  • The remote server must be a MySQL server
  • No support for transactions
  • Federated tables do not support indexes
  • It does not support HANDLER (It provides direct access to table storage engine interface)
»» 

Friday, October 23, 2015

How to Know the Recovery Model of Your Database

Recovery model plays an important role in SQL Server because they decide the nature of your transaction log file and the recovery strategy. SQL Server supports three types of recovery models: Simple, Bulk-Logged, and Full.These SQL Server recovery models have their own properties. User can also change the recovery model after creating the database.Let’s see brief information about them:

Simple Recovery model: If your database is in simple recovery model then following operations cannot be performed on database-

  • Database Mirroring
  • Log Shipping
  • AlwaysOn feature
  • Point-in-Time recovery

You can use simple recovery model when:

  • There is no need for point-in-time recovery
  • Data can be derived and easily recreated
  • Data is static

Full Recovery Model: A user can choose the full recovery model:

  • For point-in-time data recovery
  • When data became very important and user cannot lose the data
  • For the use of advanced features like log shipping, mirroring, AlwaysOn etc.

Bulk-Logged Model: A bulk-logged recoverymodel is used to preserve the transaction log records after full backup of the database.

  • Data is critical, but user doesn’t want to log large bulk operations
  • Bulk operations complete at different times versus normal processing

Different type of backups that you can run on the bulk-logged model:

  • Copy-Only Backup
  • Differential Backup
  • Complete Backup
  • Partial Backup
  • File/Filegroup backup
  • Transactional log backup

After the small overview on recovery model, here we will know about multiple ways for checking the recovery models of SQL Server database:

Step 1: Using SSMS (SQL Server Management Studio)
Go to Object Explorer and Right Click on your Database-> Go to Properties ->Options ->Recovery Model

Step2: Using catalog View
User can also check the recovery model type by the following query:

Select name, recovery_model_desc FROM sys.databases

The result has two columns, first is name of the database and second is recovery model type as shown in the figure:

Step3: Using Metadata Function:
DATABASEPROPERTYEX() is a metadata function to find the recovery model of the database. This function returns only one database at a time. Here is the query:


The output will be:

Step 4: Using sp_helpdb
The user can also use the stored procedure sp_helpdb to check the recovery model of the databases.

EXEC sp_helpdb

The output contains several fields and in the Statussection, a user can easily find the recovery model type.

Step 5: Using Object Explorer
In the object explorer, click on the Databasesand press F7. The output will be like this:

So these are the basic options to find the recovery model type of the database. 

Final Words: We have seen the different reasons to choose the recovery models and all the steps to find the recovery model of the database. When a user switches one recovery model to another recovery model then, he/she should be aware of its important points. Every recovery model has its own features and all are essential so choose your recovery model very carefully to save your database. 
»» 

Sunday, October 4, 2015

Repair Clustered and Non-Clustered Index in SQL Server

In a general way, if we define a cluster then we will define like this: A cluster is a group of some items which acts like a single system. From the figure 1, you can easily find your answer. We have 3 clusters on the image. They all have their own systems but, for each other they are only one system.

Clustered Index: Every database has rows and columns. Clustered index is used to determine the order in which the rows of the table are stored into disk.Let’s take an example, suppose we have table named Student which has a column named Roll_No. If we create a clustered index on the column Roll_No then all the rows inside the table Student will be sorted on the disk space according to the column Roll_No.

Point to remember: Index follows the tree data structure in which leaf nodes contains the actual data of the table. Leaf nodes are those nodes which have no child and they will always present on the bottom level of the tree.

Advantage: Searching and fetching of the data become faster with the cluster index because data will physically sorted right next to the each other.

Disadvantage: Clustered index is not useful or those columns that have repeated value.

How to crate clustered index: I will discuss a T-SQL method to create clustered index. Let’s take the same example of table Student that we have discussed above. With a default database, Clustered index automatically created with the primary key.

USE AdventureWorks2012;
CREATE TABLE dbo.Student
(Roll_Noint NOT NULL,
Name char(10) NULL,
Address varchar(70) NULL);

This query will create a table of three columns named Roll_No, Name and Address. Now we create a clustered index:

ON dbo.Student (Roll_No); 

This is the way to create clustered index in a table. You can choose the clustered index name according to your choice. Rather than this approach, you try it by SQL Server Management Studio.

Non-Clustered Index: Non-clustered index contain pointers that point to actual data. Pointer is a variable which contains the address of actual data.

From the above image it is clear that, the leaf nodes contain all the pointers. The main difference between clustered and non-clustered, in clustered type when we reach on the leaf nodes then we are on the actual data. In non-clustered type, when we reach on the leaf nodes then we are on the pointers.

Advantages: This approach is useful when data has redundancy.

Disadvantages: Data fetching is less easyas compared to clustered index. 

Note: A table can contain more than one non-clustered index because they do not affect the order of the row which contains data.

How to crate Non-clustered index: Using T-SQL, you can create a non-clustered index like this:

USE AdventureWorks2012;
ON dbo.Student (Name); 

Corruption in clustered and non-clustered index: When corruption occurs in the SQL server database then you should check the corrupt objects. Run the DBCC CHECKDB command:

DBCC CHECKDB(CorruptDB) With No_InfoMsgs, All_ErrorMsgs, TableResults;

It returns database ID, object ID and index ID. Here the term index ID shows the status of the index. Take a look on the index ID mapping:

ID 0 = heap
ID 1 = Clustered
ID 2 = Non-clustered 

Solution: If the index ID is greater than 1 that is non-clustered index then drop and recreate it. If the index Id is 0 or 1 then try these methods:

Restore from the backup:

If the data model is full or bulk logged then you can backup of the tail log. In simple recovery model, you will not get this option and you can only restore from the last backup.Run this command to restore from the backup:


If you want to repair a specific page then you can run this command:

RESTORE DATABASE YourDatabaseName PAGE = ‘1:85663’ FROM DISK = ‘C:\YourDatabaseName.bak’ WITH NORECOVERY

In section PAGE = ‘1:85663’, 1 is Index ID and 85663 is the page no.

More options:

You can also try these automatic repairing options. Take a look on these options:

REPAIR_REBUILD: Its syntax is:


Basically this is for minor corruption on the database.

REPAIR_ALLOW_DATA_LOSS: As it is clear from its name, it allows the data loss and tried to repair all errors. When corruption is very severe then this command deallocates the page from the memory and modifies the links. Its syntax is:


A Final Option: If you are unable to repair your database by these options then the final way is to use a secure third party tool. Before buying any tool just read all instructions very carefully.

Final words: We have seen the different methods to repair the corruption of index in SQL server database and I am sure that, you can use these options without any trouble and according to level of corruption. DBCC CHECKDB is very strong tool but it can turn into a dangerous tool when someone usesthis without knowledge.
»» 

Friday, September 25, 2015

How to Monitor Low Disk Space in SQL Server

As a SQL server user, you should be aware of disk space availability in SQL server because once SQL Server runs out of disk on the primary host, then everything comes to a crashing halt. In this case, the SQL server engine expands the size of database file. Every SQL database has the growth option setting and all the database files depend on it. This problem cannot create any trouble if you identify on time and solve it quickly. Every database has a default size and you can also increase the size of it. To increase the database size then, use this command:

USE master;
ALTER DATABASE AdventureWorks2012
   (NAME = databasename,
    SIZE = 20MB);

Many users set the database on auto-growth option. When the auto-growth event occurs then SQL server find the more space on the disk. This space can be anywhere on the disk rather than right next to the existing database space and become the reason of database fragmentation. We have an xp_fixeddrives extended procedure to check the available disk space. You can use this procedure like this:

EXEC MASTER..xp_fixeddrives

Above query will return two columns. First is drive name and second is MB free. You have another option to know about the free disk space. Use this query:

SELECT DISTINCT dovs.logical_volume_name AS LogicalName,
dovs.volume_mount_point AS Drive,
CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs

This query will return three columns; Logical name, drive name and free space in MB.

This is the way by which you can check the available disk space in SQL Server. If you are interested to know about the used space by indexes then you can use usp_SpaceUsedAnalyzer stored procedure. You can download usp_SpaceUsedAnalyzer as by here.

Note: This procedure won’t work with SQL Server 2000 and earlier versions because the SP uses system database table which is available in SQL Server 2005 and later versions. 

The above stored procedure is inspired by sp_spaceused stored procedure. Now we will see the working of sp_spaceused.

I have discussed with some DBAs about this SP and few of them are not aware about the terms “unallocated space” and “unused space”. 

USE [AdventureWorks2012]
EXEC sp_spaceused
This query will give you the following result:

This query will give you the following result:

Let’s understand the column names.

  1. database_size: It is sum of data files and log files. In this database it is 205.75 MB.
  2. unallocated space: A space which is not reserved for data files or log files. It is 14.95 MB.
  3. reserved: A space which is reserved for use by either data files or log files. It is 194608 KB and I will convert from KB to MB by this formula: 194608 KB/1024=190.05 MB.
  4. data: Space used by data = 97016 KB/1024= 94.74 MB.
  5. index_size: Space used by indexes = 88048 KB/1024 = 85.99 MB.
  6. unused: It is a part of reserved space which is not used by any data files or log files. 9544 KB/1024 = 9.32 MB.
  7. used: data+index_size = 94.74+85.99=180.73 MB.
You can remember the simple formula:

Final words:  So these are few simple methods by which we can check the disk space allocated by SQL Server database. If you are running the database without monitoring the disk space availability of your SQL Server database then you are running it on high risk. So check the disk space of your database to prevent your data from corruption.
»» 

Thursday, September 17, 2015

Restore SQL Database with .MDF File Only

Every new version of Microsoft SQL Server comes with interesting features and these features make it very easy and powerful. As a SQL server user I love to explore new things. I read about different types of database corruption and try to solve them. SQL Server database has a primary file called .MDF (Master Data File) file which contains schema and data. When corruption occurs then, experts suggest to restore the database from clean and updated backup and it should be the primary step that we can follow. In this post we will see the different ways to restore the SQL database with.MDF file only.

I had faced this problem when my friend sent me.MDF file and forgot to send .LDF file along with it. I used the first method to solve my problem.

First Method: Restore by GUI

It is very easy approach to attach .MDF to SQL Server. Simply follow these steps:
  • Open SSMS.
  • To attach the database, Right-Click on the database and select Attach option.
  • In Attach Databases dialog, click on Add option and navigate to the location of your .MDF file.
  • As we have only .MDF file then it will show the Not Found along with .LDF file.

  • When you will click on the OK button then, It will raise an error:

  • Now you can take the help of T-SQL to achieve this. This method will also work when more than 1 log file is missing.
        (FILENAME = 'C:\123\TestDB.mdf')

Note: After successful completion, you will get the below message:

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TestDB_1.ldf" may be incorrect.

New log file 'C:\123\TestDB_log.ldf' was created.

Second Method: This method is very useful when a single log file is missing:

EXEC sp_attach_single_file_db @dbname='TestDB',
@physname=N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL \MSSQL\DATA\ TestDB.mdf'

Third Method:

( FILENAME =N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQL\MSSQL\DATA\TestDB.mdf')

It is only to attach a single log file.

Conclusion: It is the very common problem for SQL Server users andI hope this post will help you to fix this issue. If you have any other option to fix this problem then share with me. 
»» 

Monday, September 7, 2015

How to Change the SQL Server Database Mode?

If required, SQL server database administrator can change his/her database’s mode by using SQL server enterprise manager, SQL server management studio and SQL commands like ALTER database or sp_dboption. SQL server enterprise manager is a Microsoft management console that provides user interface for DBAs to perform several tasks like create database, define groups, register server etc. DBA can use it to change the database mode in single user mode or in multi user mode for SQL server 2000. Similarly SQL server management studio can be used for changing the database mode for SQL server 2005 and all upper versions.

What is Database Mode?
Database mode specifies your database access permission like how many users can access your database at time. Suppose your database is in single user mode then it specifies that only one user can access the database at a time and generally it is used for the database maintenance purpose. Your database can be used by multiple users at a time when database is in multi user mode.    

Change the SQL database mode using ALTER command:

Change the SQL database mode using stored procedure:
EXEC sp_dboption 'Works', 'single user', 'false'
EXEC sp_dboption 'Works', 'single user', 'true'

Change the SQL database mode using SQL server management studio: To change the database mode using SQL server management studio, first open it and connect to the SQL server instance. Now open the databases belonging to the instance in object explorer windows and right click on your desired database and click on the properties menu. When you click on the properties menu, database properties will be displayed at the screen like below image.

As you see in the above screen, there are three options: single, multiple, & restricted. I have already discussed these modes in the above section. When you choose single from the list to change the database mode either you will successfully change the mode or will get fail due to existence of active connection to the desired database. SQL server prompts you to close all the active connection from the database before changing the database mode. After closing all the connection, you will be able to change the database mode in single_user mode. Similarly you will change the database mode in multi_user mode of your desired database.
»» 

Thursday, September 3, 2015

Fix Page Level Corruption of SQL Server Database

Database corruption can occur anytime and most often due to faulty hardware or operational mistakes like deleting the transactional log file etc. SQL server database has a RESTORE DATABASE command, which has an optional parameter. It provides the facility to restore one or more pages instead of the entire database. This option is very useful when corruption occurs in few database pages by IO subsystem problem.To solve this problem you can try these options:

  • First of all, change the database into single user mode because end users can create the restrictions in the restore operation. To set the database into SINGLE_USER mode, execute the following command:

Here we use the ROLLBACK AFTER n SECONDS option, which will be able to kill the running operations and connection.
  • Now you can start the recovery process by taking the tail-end backup of the log file. It can be a single portion that has not already backed up. Example :
BACKUP LOG MyDatabaseName
TO DISK = N'D:\SQLBackups\ MyDatabaseName _TailEnd.trn'
  • Now, execute the RESTORE DATABASE command with PAGE switch option which is loaded as many pages as needed.
PAGE = 'fileid:pageid,fileid:pageid,etc'  -- e.g. 1:5220,1:5221,etc
FROM DISK = 'D:\SQLBackups\ MyDatabaseName _lastFull.BAK'
Here we are using NORECOVERY option so; you do not recover your database with this command.
  • You can apply the transactional log with NORECOVERY either you have full backup or differential backup.  Make sure, you do not recover the database. See example:
RESTORE LOG MyDatabaseName
FROM DISK = 'D:\SQLBackups\ MyDatabaseName_LogFileFrom3PM.TRN'
RESTORE LOG MyDatabaseName
FROM DISK = 'D:\SQLBackups\ MyDatabaseName_LogFileFrom315PM.TRN'

There are similar log backups in the sequence. This sequence runs up to the point of disaster. 

RESTORE LOG MyDatabaseName
FROM DISK = 'D:\SQLBackups\ MyDatabaseName_LogFileFrom630PM.TRN'

Now again we are using NORECOVERY option.
  • Now we will apply the tail-end backup that we have got in step 2 and recover the database.
RESTORE LOG MyDatabaseName
FROM DISK = ‘D:\SQLBackups\ MyDatabaseName _TailEndBackupOfYourLogFileFromStep2.TRN’

Note: Here we are using the recovery option to recover the database.
  • Now use the following command to clear out the msdb..suspect_pages
DELETE FROM msdb..suspect_pages
  • Now, again run the DBCC CHECKDB() with ALL_ERRORMSGS (and NO_INFOMSGS) options.
  • Switch the database into the multi-user mode:

Final Words: We have seen the steps of page level restore options. This is more feasible for few page corruptions. If you have a large number of corrupt pages then you have to manually handle all t-log applications. A third party tool is also available to fix the page level corruption
»» 

Tuesday, August 25, 2015

Backup and Recovery Options of SQL Server

Database corruption is the part of many database user lives and it gives the chances to explore new things every day. This blog is about the advanced backup and restore options of SQL server database. All DBA’s understand the importance of backup and restore command but sometimes a lesser-known option can be very useful. Let’s see the different options:

Verifying the Backup Integrity: Most of the time; I found that experts easily neglect backup integrity while it plays an important role in database. SQL server 2005 introduced page checksums feature and it works as the page corruption detector of the data file. This function is by default enable in the SQL server 2005 and if you need to enable them then upgrade the database from previous version.

You can use the BACKUP command with CHECKSUM option to confirm that page checksums are checked. This option is very important because it notifies you when your backup was corrupted. If it finds page corruption then backup will fail by default. At this time user get the following error message on the screen:

Msg 3043, Level 16, State 1, Line 1
Backup ‘broken detected an error on page (1:143)
in file ‘C:\.....\broken.mdf’.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Some DBA’s use the RESTORE command with VERIFYONLY option to check the integrity of the database but, it only checks BACKUP header not the data inside BACKUP. You have to use RESTORE command with CHECKSUM and VERIFYONLY options to restore the process and recheck the all pages and entire BACKUP checksums.   

Minimize the Data Loss: When a corruption occurs in the database then, many users start restoring the database from backup. At this situation it is a possibility of the last portion of the transactional log disappears forever. This kind of backup is called tail-log backup. This backup is most useful when you are using synchronous database mirroring.

If you are using tail-log backup and the data files are missing then, you will get the following error message on your screen:

Msg 945, Level 14, State 2, Line 1
Database ‘DBMine2008’ cannot be opened due to
inaccessible files or insufficient memory or disk space.
See the SQL Server errorlog for details.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.

In this case, you can use NO_TRUNCATE option with BACKUP statement because it allows the backup to proceed. 

Note: In SQL Server 2005 and later versions, a database that has tail-log backup cannot be overwritten by the restore option unless you have to use REPLACE option.

Point in Time Restoring: Let’s assume a scenario, you deleted the data and you need to restore the data from the log backup created right before the deletion. If you know the time of data deletion then simply you will choose the STOPAT option to restore it. If you don’t know about the timing of data deletion then, you have to use STOPAT and STANDBY options to progress through the log and check by small amount of time to reach on the correct point.

You can also use its alternative which is marked transaction. It creates log mark in transactional log. For example:


You can use this known point during database restoration. STOPATMARK can be used if you want to include marked transaction and STOPBEFOREMARK option to exclude marked transaction. To use these options you should have the information about the name of log mark. If you haven’t then you can find in the logmarkhistory table in msdb. If you lost this information then, it is difficult to find the log mark names manually. In this case you have to use a third party tool.

Restart Interrupted Restore Option: RESTORE command has many options and WITH RESTART is one of them. It is less-known option which uses to restart an interrupted restore option. This restores operation works on the checkpoint file which has the information of point to which restore has progressed. You can check this file on \InstanceName\MSSQL\Backup folder location.

A checkpoint file is updated by the following reasons:
  1. After the processing of each and every backup set.
  2. When the redo part of the recovery is finished.
  3. At the time of database file creation after completion of zeroing.

When you use this WITH RESTART option on the absence of checkpoint file then, you can get this error message:

The restart-checkpoint file ‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\RestoreCheckpointDB8.CKP’ was not found. The RESTORE command will continue from the beginning as if RESTART had not been specified. 

This option is best for the large backup which has many taps because you can restart the restore option without going back to the first tape. It is also suitable to restore disk-based backups.

Other Options: There are many options which are lesser-known option which I said at the starting of blog so, you should try these options. By using BUFFERCOUNT and MAXTRANSFERSIZE options you can get the great performance to modify the I/O buffer. PAGE and PARTIAL options reduce the downtime when disaster occurs and these are used to single-page and piecemeal.

Conclusion: I hope you like these options and curious to know more about them. Database backup and restore is the very vast topic and used by many database users. Few corruptions are very hard to solve and at this point I can only suggest any trustworthy SQL database repair tool to repair your valuable database.
»» 

Wednesday, June 17, 2015

Get the Solution of Error 3343 in MS Access Database

MS-Access is mostly used in the small organizations to manage the valuable data. A database user faces different database errors during run-time and error 3343 is one of them. Error 3343 occurs when the database has corrupted and a user tries to access the same database. Possibly following error message is display on the user’s screen:

Let’s walk through the different reasons behind this error:

Reason 1: Splitting of the database
As we all know that a database can be small or large in the size. In the most cases; user stores data in a single file of the database and this single database file grows day by day and can create trouble. Spitting of database can fix the problem, but it is not the concern here. When you try to split this database into two parts and corruption on a single part of the database may be the main reason of whole database corruption. This is very serious issue and complete database repairing is a very tough job when it large in size.

How to fix: MS-Access has inbuilt repair tool to repair the database and it is called Compact and Repair utility. With this utility, you can easily repair the corrupt database.

Note: If the user is unable to repair the database using above utility then can download other tool which can repair the database.

Reason 2: Opening a database in previous version
It is the second most possible reason behind the error 3343 in MS Access database. Default file format supported by the Access 2007 and 2010 is .accdband if you want to open the Access 2010 file into Access 2003 then, you have to convert the file of 2010 to 2003 by MS- Access. Mostly you convert the database into the earlier version for sharing with others.

How to fix: Split the database into parts and open the database file in the previous version are the main reasons of this error. To fix this error you can take help from reliable commercial software
»»