How To Restore Database in SQL Server 2014 From .bak - Step By Step

Due to threats of data loss, human errors, scam attempts, hardware damage, or natural disasters, it is imperative for IT administrators to ensure the security of data. In order to minimize the risk of data loss, creating a regular backup of the database is such a good practice. These backups are stored in BAK format and can be utilized to restore the database contents if the original data files get deleted or corrupted. Well, opening BAK files and restore SQL database from these files is a simple process and can be done via different techniques. In this article, we will discuss all the possible and simple workarounds to restore database in SQL Server 2014 from .bak file in a step-by-step manner.

Methods To Restore Database in SQL Server 2014 From BAK File

Restoring database is a procedure of replicating backup files from any secondary location such as zip disk, tape etc. If your database gets damaged or corrupted then, you can use backup to get the database back in its original condition. If you want to manually restore your SQL Database then, this can be done via two different techniques.

Method-1 Restore Full Database Backup via SQL Server Management Studio

• First of all, launch SQL Server Management Studio and connect to a proper instance of Microsoft SQL Server Database Engine
• Next, click on Object Explorer next to Databases node and choose Restore Database option from contextual tab
• Select General menu and navigate to the Source section. Click on Device option to get the location and hit add button to locate the backup file. Highlight the database with .bak extension and hit the OK button
• In Options, specify the Restore Options as Overwrite the existing database via checking the options you need
• In the General tab, hit Verify Backup Media button and click OK to check if all the desired database is attached properly or not
• If it is attached correctly then, you will get a pop-up that says “Database ‘Name’ restored successfully”
• Navigate to the object explorer and refresh the master database Go to the Databases and expand the modes to review its objects like views, tables, stored procedures etc. Now, your database is ready to use and you can start working with this.

Method-2 Restore Full Database Backup via Query

One can also restore the SQL database from .bak file using query. In order to do this, open a new query window and execute the following RESTORE statement:

The RESTORE query accepts multiple options same as GUI however, users also can run a simple restore with the minimum of code. After successful restoration, you can use this database for different operations.

Method-3 Restore Full Database Backup via Expert Solution

If none of the above-discussed methods are working or you are finding them complex then, SQL Bak File Repair is one of the most prominent solution. The software allows users to recover the entire data from various SQL backup files along with all components. These include Tables, rules, triggers, stored procedures, view, columns, indexes, keys, etc. It has the capability to recover both MDF and NDF files that are saved within the SQL BAK file. The tool offers two export options to recover the database, one is SQL Server Database and other is SQL Server Compatible Script. Using this, one can recover any size of the database as there is no file size limitation imposed by the software and it supports .bak file of SQL Server 2014, 2012, and all below versions.

The End Note

With the backup of database in SQL Server, one can restore the data in any data loss circumstances. Here, we have demonstrated how to restore database in SQL Server 2014 from .bak file using SQL Server Management Studio and PowerShell. In case, if someone finds manual solutions complicated and time-taking then, a reliable third-party tool is also recommended here.

Read More

How to Find SQL Server Performance Bottlenecks and Fix These Issues

SQL Server is undoubtedly a popular way to manage database. Due to its efficiency, business organizations largely depend on this for data management. This dependency often causes performance bottlenecks and ultimately hampers the productivity of the organization itself. So, it is important to find the bottlenecks and avoid them at any cost. Read on to know everything you need to know about SQL Server bottlenecks, including how to find SQL Server performance bottlenecks. First, we will see the definition of SQL Server bottlenecks.

What is Bottleneck in SQL Server

The term bottleneck means the neck of a bottle that reduces the flow from the bottle. Similarly, SQL Server bottleneck means reduction in the performance of SQL Server. This situation usually occurs when any shared resources like SQL database is concurrently accessed by too many people. Though bottlenecks are inevitable in every system, it should be addressed to save users from loss of time and effort.

Symptoms of SQL Server Bottleneck

If you have this question in mind, how to find out if SQL having bottlenecks or not, here is the answer. First, you have set a certain standard for the performance and then consider the symptoms according to that baseline. This baseline will help you to determine the bottlenecks and low activity period, as well as to compare the effects of the alteration made. That is why, setting up a good baseline is important. Here are the symptoms you need to consider:

• Disk Bottleneck
  If the SQL Server is having slower response time or the disk counters are operating close to maximum values for a longer period of time, it is having bottlenecks. In such cases, users will also get relevant error messages in SQL application log and hear noises coming from the disks.
• Memory Bottleneck
  If the application log contains certain messages like out of memory and memory resource timeout, the memory bottleneck is to blame. Some of the other symptoms of Memory bottleneck are increased query execution time, decreased active queries, low buffer cache hit ratio, higher I/O usage, slow system, and low page life expectancy.
• CPU Bottlenecks
  This type of the bottleneck is easiest to find out. In this case, CPU will be highly utilized by the SQL Server all the time but have low overall throughput

How to Identify SQL Server Bottleneck Using Tools

Some bottleneck detection tools are available that you can use. Here is the list of those tools:
• SQL Profiler
  SQL Profiler is the tool with the ability to fetch and log the complete T-SQL activity of SQL Server. The default template can be used to capture the execution of SQL statement. In SQL 2005, SQL Profiler also allows users to add the blocked process report, usually found in the Errors and Warnings Events.
• Dynamic Management Views (DMVs)
  A useful set of dynamic management views and functions are offered by SQL Server 2005. These views display the current SQL Server internal metadata and other events like server restarts and memory pressure. To avoid getting transitory events and have a meaningful result, use tables to save these views.

How to Fix SQL Server Performance Bottleneck

According to the SQL experts, certain steps can be taken to avoid bottleneck situations.

• Keep Memory Under Supervision - If the available memory percentage drops below 20%, users will start encountering issues in performance. So it is necessary to always keep an eye on the memory usage. Review queries and identify the memory leaks for optimized performance of the SQL Server memory. Add more RAM to increase SQL Server scalability.
• Find and Fix CPU Bottlenecks - To fix CPU bottleneck, find out the worst-performing queries that are giving your CPU a hard time. Then manage these queries and their underlying indexes. Using all available CPUs and adding faster CPUs can also resolve this problem.
• Strict Monitoring of Disk I/O Usage - Storage I/O is the slowest of all SQL resources it should be always under observation for bottlenecks. If bottleneck symptoms are found, see if it is possible to attain better performance by improvising storage system design. The best techniques to solve I/O bottleneck situations is to introduce faster disks, distribute the load across different spindles and defragment the disk.
• Well Structured Database Design - Any highly normalized database often causes long-running queries that squander resources like CPU and memory. Therefore, poor database design has to be avoided. For example, if any operation needs five or more table joins, the database should be redesigned.
• Proper Index Management - Too many indexes make SQL Server work slowly by making it take more time to update. Duplicate and unnecessary indexes only drain system resources like CPU and I/O. Use SQL Server DMV or Database Engine Tuning Advisor (DTA) to determine the unused indexes.

Final Words

The above discussion answers how to find SQL Server performance bottlenecks. We have also focused on the symptoms and method to avoid SQL performance bottlenecks. Users will get definitely get benefited from this blog if they follow the instructions.

Read More

Best Trick to Extract Data From SQL Server MDF File Instantly

SQL Server is a relational database management system in technology field it supports more than one transaction processing. MDF is utilized to save information like views, stored information and so on and secondary is .ldf file, that stores transactional information of its related primary database file MDF. Moreover, .mdf is a foremost database file of SQL Server, then, it's necessary to keep all the crucial data secure in case of mishap.

Often times, user try to extract data from MDF file as .mdf file sometimes become inaccessible, if in case it's associated with .ldf file get damaged or lost due to any cause. Also I saw bundled queries on how to extract data from SQL File. Thus, we are discussing some workarounds to resolve and fix the issue of the user to extract data from corrupt mdf file.

Extract Data from MDF File - Manual Workarounds

Mainly, there are two various tricks for extracting data from .mdf file. User can opt any one of them as per their comfort. First one is, by utilizing “T-SQL Script” and another one is “SQL Server Management Studio”.

The techniques with their steps are listed below, you have to follow all the given steps in sequencially to get positive outcome.

Workaround 1: Using T-SQL

Step 1: Firstly, to extract data from .mdf file in SQL server by using T-SQL script follow this below listed command.

Step 2: Lastly, after using the listed command will attach the database in SQL Server, whether the LDF file is lacking to the related .mdf file. Moreover, the “ATTACH_REBUILD_LOG” will create a fresh transaction log file or generate a new database.

Workaround 2: Utilizing SQL Server Management Studio(SSMS)

Step 1: Start SQL Server Management Studio (SSMS) application into your machine.

Step 2: Now, connect the application with SQL Server instance of Database Engine.

Step 3: Here, in an option of “Object Explorer” you have to choose, the “Databases” option and then Right-click on it. Furthermore, you will have to hit on “Attach” to move forward.

Step 4: Afterwards, in “Attach Database” wizard, select “Add” button to spot the desired MDF file that you need to attach.

Step 5: Simply, go to the location where you have stored your .mdf file and choose the .mdf file then select “OK” button.

Step 6: Once again, in “Attach Database” wizard opt “Ok” button to extract MDF file in SQL Server effectively.

Step 7: Finally, after applying the above mentioned steps on your SQL Server, you can view the attached database on your databases node.

Drawbacks of Perform Extraction Manually

The above-mentioned manual method to extract data from MDF File include some constraints that affect the procedure effectively. Some of them are listed below:

1. The MDF file that you need to attach wants full access control and full permission.
2. Check that you are trying to attach it in correct version not in higher version or in lower version as well.
3. It is difficult to execute by a novice user because, it includes some technical knowledge.
4. This procedure might be loss your crucial data while, extracting data from MDF file to SQL server.

Extract Data from SQL Server MDF File - Professionally

As-per the above-mentioned limitations of manual approach, a user can go with an alternate method that, will help to perform the extraction task positively. This is an appropriate way to resolve the issue this is a third-party application such as SQL Recovery software. With the help of this utility user can easily and simply repair corrupted .mdf & .ndf SQL Server database and extract data from corrupt mdf file.

This software furnishes an easy-to-use and understandable interface even, a novice user can also execute the task without taking an expertise help. This utility, extract data from MDF file without losing a bit of users data. SQL recovery contains an outstanding features, some of them are listed below:

1. Recover SQL Database primary as well as Secondary
2. Retrieve deleted SQL server database table's Data
3. Quick & advanced option for scanning
4. Software supports ASCII and Unicode XML datatype
5. Repair all versions of SQL Server 2017, 2016 & below version.
6. Option to Auto-fetch SQL server version while extracting

Working Steps of SQL Recovery Application

Step 1: Download and install the SQL recovery software into your local machine. The interface of the utility as shown in the below picture:

Step 2: Now, choose an option “Advance Scan” and then hit on “Auto detect” option if you don’t know the version of SQL Server or opt manually to detect MDF file version properly.

Step 3: Here, you have to review the components of MDF and NDF Files

Step 4: Finally, click on “Export” to successfully export the MDF file with only Schema and with Schema or Data.

Final Verdict

We explained two different ways to extract data from MDF file to SQL server effectively. As you can see that the manual techniques have some constraints that affects the procedure positively. So, we describe an alternate approach also to open and view the data of SQL MDF File. With the help of the software, user can easily and simply execute the extraction procedure effortlessly. Afterwards, it all depend on the user that which, one method they want to opt. User can go with any one of them as per their requirement.

Read More

Steps to Restore Database from Backup File in SQL Server

Overview

In most of the cases, users create a backup file of their database on their machine. This help the users to perform recovery at the time of any disaster, or manage large amount of database onto the SQL server. However, users generally knows the way to create a backup of a database on their machine, but are unaware from the way to restore database from .bak file on SQL Server. Therefore, this blog will help such users to restore database in SQL server either by using query or SSMS.

Steps to Recover SQL Server Database From .bak File Using SSMS

In order to restore SQL Server database from .bak file, it is mandatory to have a backup file on computer system. In addition, go through following set of procedure for recovering SQL server database from a backup file:

• Launch the SQL Server Management Studio on your machine. Then, from the left-hand side menu of the management screen, right click on Databases option and then select Restore Database option from it.
• A Restore Database Window will appear. From this Window, check the checkbox of Device option
• Click on the Browse button from the restoration window
• Now a backup device window will appear in front of you. In this window, you have to choose File as backup media type and then click on Add button
• Now navigate to the location where the backup file is stored. Click on the file having .bak file extension for selecting it and then click on OK button
• Analyze the selected database file from middle pane area of backup device window and then click on OK to continue further
• Click on OK button of the Restore Database window. This will start the procedure of recovering the server database from a backup file
• After the completion of restoration procedure, a message window will appear that indicates that the complete data is now recovered from the BAK file. Click on OK button to terminate the current settings window
• Now either press F5 from your keyboard or click on Refresh button of SQL server Management Studio. This will display the database restored from the backup file

Finally, you will be able to see database of your backup file, on left-hand side panel of management studio. You can click on expand button to see further tables of the restored database.

Steps To Restore Database from Backup In SQL Server Using T-SQL

In this case, if the database already exist, then data of existing database will overwrite the files or tables with new one. If database exist, then follow the following commands:

• Launch a SQL query window on your server
• Set the database of the server in Single User Mode by executing following command:
  ALTER DATABASE < Database Name > SET SINGLE_USER with Rollback IMMEDIATE
• Run the following command for restoring the database from backup file:
RESTORE DATABASE < Database Name > FROM DISK = ‘< Location where BAK file is stored >’ WITH REPLACE, STATS = 5

If the database does not exist, then execute following command in query window of the server:

RESTORE DATABASE AdventureWorks FROM DISK = ‘< Location of BAK file with file name >’

Observational Verdict

In this blog, users can find strategy to restore database in SQL server from backup file using query or SSMS. In addition, it is also applicable to all other versions of the SQL server. The most easiest way is to recover SQL server database from .bak file using SQL server management studio .It is quite easy to access, in comparison to query procedure. This step guide is sufficient to recover SQL server database from backup file and thus no need of any technical expert to perform the same. However, both the procedures are foolproof and could be accessible by a novice users also and it is recommended to validate your backup before going to restore it. Because, Sometimes, it may not be possible to restore a backup due to a corrupt .bak file. In this worst scenario one can try page checksums or use third party SQL Backup Recovery Tool to overcome with this situation.

Read More

Different Ways to Handle Deadlock in SQL Server

Introduction To Deadlock

Deadlock in SQL server is a condition in which two or more system server processes IDS (SPIDs) are waiting for a resource. No process can get the resource as the other processes are preventing it from getting the resource. It is a very common issue in SQL Server, whenever this situation occurs in the system, the server will choose one of the process as a victim and rollback that process. After it rollbacks the victim process, it allows other processes to execute as there is no other way to resolve the conflict.

Lock Manager

The thread of lock manager checks the deadlock condition. When the lock manager algorithm finds a deadlock, it will select one process as a victim and send error message 1205 to the client. Lock manager then kills that selected SPID. Killing the victim SPID will free up the resource and will allow other processes to continue with the resource.

How To Identify Deadlock in SQL Server

First, to identify the deadlock, one should gather information about suspected processes and resources. To get this information one needs to add the -T1204 and -T3605 startup parameters or we can call Trace Flags to SQL server.

Steps for adding startup parameters:

1. Start SQL Server Enterprise Manager.
2. Select and then right-click the server.
3. Click on Properties.
4. Go to the Startup Parameters.
5. In the Startup Parameters, enter-T1204 in the Parameters text box.
6. Then click on Add.
7. Again in Parameters text box, type -T3605.
8. Then click on Add.
9. Click on OK.

The startup parameter -T1204 will gather information about processes and the resource when the lock manager encounters a deadlock. Whereas -T3605 startup parameter write this collected information on a log file.

Three Events to capture Deadlock in SQL Server

User can also go for the profiler to capture the deadlock, there are three events that can capture the deadlocks. These events are present in Lock event class.

• Deadlock graph – Deadlock graph provides XML description of the deadlock.
• Lock: Deadlock – It indicates the two processes that has deadlocked each other in order to acquire the resource.
• Lock: Deadlock Chain – It generate all the events that causes the deadlock condition.

How to Handle Deadlock in SQL Server

To handle deadlock in SQL Server, user can go for following techniques given below:

The Lock Manager

The SQL server detects the deadlock victim by two ways:

• Lock Manager will assign the priority to the processes competing for the resources using Deadlock_Priority. The process with lowest priority will always be considered as deadlock victim.
• If two or more processes have same priority then, the process that has lowest cost will be chosen as victim.

Trace Flag 1204

It captures the details of deadlock in SQL Server and provides information about the node involved in deadlock condition. User can use DBCC TRACEON(1204, -1) format to capture the details of involved processes and resource.

Trace Flag 1222

It returns the deadlock information using XML format, the Flag 1222 captures the graph, which is in more comprehensive format as compared to Output information of Flag 1204. It captures the deadlock events in the form of graph.

WMI Provider

The WMI (Windows Management Instrumentation) Event Provider is used for creating SQL Server Agent alerts that respond to specific events. For later analysis, it generates an alert that saves XML deadlock graph events in a table. SQL Server Agent submits a WQL (WMI query language) request, receives the WMI events, and runs a job in response to the event.

By Capturing The Graph

In this, user will get the information about processes and resource involved in deadlock using graph. It offers the XML deadlock graph event in profiler, which captures more information about deadlock events than trace flag 1204.

Conclusion:

In this article, we have captured the different ways of handling deadlock conditions in SQL Server and the reasons that causes the deadlock in the server. By using techniques mentioned above, one can easily find the events or different processes and resource that are involved in deadlock condition.

Read More

Best Way to Fix Corrupted SQLite Database

Corruption is one of the common scenarios nowadays. It can occur in any platform or file format, for that matter. Similarly, Android SQLite database can be corrupted due to various reasons. Therefore, in this blog, we will introduce a best to for the recovery of damaged Android SQLite database. Before that, let learn some crucial details about Android SQLite database.

Android save the data via SQLite database engine and it is a stand-alone transactional engine, which allows users to access and manage database. It does not need any dedicated server process. Many application, as well as platforms beyond mobile OS Android, use SQLite. Dissimilar to desktop-based databases, it (SQLite) removes the features, which are not essentially useful and as an outcome attains minute footprint. DB systems use static typing; on the other hand, SQLite prefers the manifest typing. It drives the data information responsibility to compute languages such as Java. This maps database structure to the high-end types.

Reasons to use Android

One of the major reasons behind this is that SQLite is reliable. Most of the plans codes are dependent on library testing. Moreover, the library has such a structure that it has the ability to handle the failures of the system like disk errors, power failure, or low storage memory. Some more grounds due to which Android utilizes SQLite:

• Need No setup and Installation: SQLite does not need any administration, as it is an independent platform. Moreover, it requires no administrator for managing data.
• Use Data in Other Platforms: The database, which is generated in SQLite is in the stable format as well as with backward capability. Therefore, it can used on the older version too.
• Creation of Dedicated Database File: It produces a single database for the complete entry & information storage when uses on the platform or on an application.
• Static Typing vs. Manifest Typing: SQLite database is not the property of column storing value, unlike SQL database, but of the value itself. Therefore, any of the value can be saved in the random column regardless of the declared type of column.

SQLite has various benefits but is prone to corruption as well. Let us, how to resolve the damage from unhealthy Android SQLite database.

How to Fix Corrupt SQLite Database

There are many utilities available in the online market to accomplish this task. This is all because of the immense increase in technology. However, we cannot trust any random software. Moreover, if users made the wrong choice they may have to face negative consequence of the same.

SQLite Recovery tool helps them out in such conditions. It is an advanced application and allows users to repair and recover the SQLite database without any difficulty. After the recovery, users can even export the files into various three different file formats, which includes SQLite, MS Access, or even any other database keeping the entire data safe and secure. It supports numerous DSN such as SQL database & Microsoft Access. You just have to follow these steps as stated below:

• To begin the process, head to Start >> All Programs >> Sqlite Database Recovery
• Now, click Browse followed by a hit on Open
• The scanning will begin, click on OK after its completion
• After this, all the details of SQLite database, which you picked up, will display. You need to click on the Close button after viewing it
• Then, click Export for exporting database into a healthy content.
• Select one file format, from the options. Your data will be migrated into that file and then click on OK
• Provide the data type mapping and to confirm it, click OK
• After this, pick the destination path for saving SQLite database. Then, hit on OK
• In the next step, hit on OK when you see the complete details of the exported file
• Now, close report & exit. After you do this, go to “Other Database” radio button to migrate database in other format & continue
• Pick any of the file formats from the options, and then click on OK button
• Once it is done, select type of SQLite data and map it to all other data types. Hit on OK to continue
• A report will be generated after the progress bar goes to 100 percent, this indicates the completion of the process. You need to click on Close after this.
• Again click on the button of OK for confirming completion of the procedure
• In the final step, exit the application, by click on Yes button.

Wrapping It Up

There are various reasons behind the Android using SQLite. However, it can be damaged anytime. Therefore, in the above blog, we have learned a method to repair Android SQLite Database after it gets corrupted. Many professionals have successfully used this method and recovered their data.

Read More

SQL Server Error 5123 Attach Database - Resolution

SQL server error 5123 occurs when users want to attach the database, which has been placed on different locations. This error is a kind of permission error that occurs in SQL Server. As we all know, the .mdf is a primary database file and .ldf file represents the log file associated with a primary database file. Regardless of attaching a primary data file via SQL Server Management Studio, sometimes it becomes very difficult to open it. On the other hand, this error may be encountered when there are several logins for attaching or detaching the database. Therefore, in the following section, we are going to discuss a manul procedure to resolve Microsoft SQL Server error 5123 in an absolute and reliable way.

Microsoft SQL Server Error 5123 – Reasons

Let us take a look on the reasons why Microsoft SQL server error 5123 occurs.

• This error occur if you detach the last database with different logins and then, try to attach a database with different logins.
• In Microsoft SQL Server this error generates at the time when the entire process is running on the Server but it has no permission to run a program folder.

To overcome this problem we have to change the permission or add permission as the owner of .mdf and .ldf files.

Fix MS SQL Server Error 5123 Attaching Database - Manual Steps

Follow these steps to solve SQL error 5123. Some of the steps are listed below:

• Right-click on MDF file that you need to attach
• Then, choose Properties to check the permissions of MDF file.
• Click on Add button to grant any other login permission or and provide the full login control.

Moreover, you can perform this for an associated .ldf file and again, you can try to attach.

You can also try "Run as Administrator" rather granting permission to everyone.

Attach the Database in Microsoft SQL Server

1. Using SQL Server Management Studio (SSMS)

• Log in to SSMS using a valid domain name and Windows Authentication
• Click on the Object Explorer and then, select 'Databases' to view the list of existing databases.
• Right-click on 'Database' and choose 'Attach' from a drop down menu
• A new Attach Database Window will appear on the screen, now click on 'Add' button
• After that, you can select the database that you want to attach.
• The database window will verify the MDF and LDF files. Then, click the OK button.

Here, you can see all the attached database files in SQL instance.

2. Using Transaction SQL (T-SQL)

With the help of sp_attach_db stored procedure, users can easily attach the databases in MS SQL Server. Let us have a look:

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

[ @dbname= ] Is the name of database that can be attached to a server.
[ @filename1= ] 'filename_n' Is the physical name, which includes location of a database file.

How to Solve Microsoft SQL Server Error 5123

If the above-stated method is not working well or you want quick solution to Fix SQL MS SQL Server Error 5123, then you can go for an automated approach ie SQL Database Recovery Tool. The tool recover database from corrupted SQL MDF File. Not only this, it is able to recover deleted database objects of SQL version 2017, 2016, 2015 & its below version.

Conclusion

SQL Server Error 5123 Access Denied error is one of the common error occur which SQL DBAs faces. The blog covers the possible reason behind this error and discusses the resolution to solve SQL Server Error 5123.

Read More

Generate Script in SQL Server With 6 Simple Steps

“I am a new user of SQL Server so I have very limited knowledge of its features. I would like to know how to generate script with data of SQL Server 2016 database. As I have mentioned, I am unaware of this process. Can anyone help me by telling how to generate scripts from SQL Server data? Thanks.”

Are you looking for a way for script generation in SQL Server ? If yes, then continue reading this post to learn two solutions of this problem.

SQL Server is widely used around the world for managing database. It contains features that can generate scripts that can be used for creating databases, inserting data, making tables, functions, views, etc. For these reasons, many SQL users want to generate script with data in SQL Server 2017. Despite wanting to generate the script, many users fail to do that because of their lack of knowledge about the procedure.

Considering this situation and user demand, this write-up will discuss different ways of generating scripts in SQL Server data.

Read on to learn more about it.

How to Generate Script in SQL Server with Data

There are two different methods of generating script. One is called the manual method and another is named automatic method.

In this section, we will going to discuss two methods to generate SQL Server script. You can choose one according to your convenience.

Method 1: Manual Technique

Follow these steps to generate SQL scripts manually

1. In order to perform this process, First open SQL Server database. Now, right-click on the Database. Select the options Task and then click Generate Scripts.

2. An introductory screen will open and you need to click on the Next button to proceed further.

3. Now, choose the database object that you need. Click on Next.

4. On the next page, users will be asked to configure settings regarding how the script should get published. After choosing the settings, click on Advanced button on the same page.

5. For the option ‘types of data to script’, choose ‘Schema and data’ from the drop-down menu. Then click OK>> Next.

6. Click on Finish when it is done generating scripts. You can check the script file now and run it if needed.

Method 2: Automatic Technique

If user downgrade the SQL Server version and want to generate SQL script, the user will not be able to generate it due to the compatibility issue. For this users can try an automated solution to generate SQL Server script with data that is using SQL MDF Repair Tool. Using this application, users can easily generate scripts with data in SQL Server 2017, 2016, 2015 & all its below versions.

This software can also recover deleted data from SQL database and create scripts out of them. Even if some of your data is damaged, this tool can easily recover them and then make scripts. To generate SQL Server script with data with the help of this application, users have to perform the below steps:

1. Download and launch the tool. Add the SQL database file for which you want to generate script.

2. Once the file is loaded, you can preview all of its contents.

3. Click on Export button from the menu options.

4. Select “SQL Server Compatible Scripts” as the export as file type.

5. Also choose the option “With Schema & Data” and then click on Save.

6. The tool will generate script of selected SQL Server data.

Conclusion

If users need to generate script with data in SQL Server , they are landed on correct page. Here, we have discussed two solutions for generating SQL scripts . Users can pick any one of them to generate SQL scripts.

Read More

Know How Table Variables Different From Temporary Tables in SQL Server

Know What Table Variables are ?

SQL Server support features that allow users to work with temporary data that gets stored in temporary tables. Microsoft introduced table variables for the first time with SQL Server 2000 as an alternate option to using temporary tables. Table variable is a type of variable data that are used to store temporary data. It has been improved with the release of SQL Server 2014 version.

It is defined by using DECLARE keyword:

DECLARE @Table1 TABLE
(
     ID INT,
      Name NVARCHAR (30)
)

Table variables can also be created by a predefined table type that is a table definition stored in the database as an object (user defined table type):

Creating User defined Table Type---
CREATE TYPE dbo.Table1 AS TABLE
(
     ID INT,
      Name NVARCHAR (30)
)

Declaring table variable---
DECLARE @Table1 AS dbo.Table1

Insert data into table variable---
INSERT INTO @Table1(ID, Name) VALUES(1, ‘Jennifer’)

Table Variables VS Temporary Tables

Table Variables differs from Temporary Tables in some ways stated as below:

Transaction Logs are not recorded for the table-variables. E.g., when we rollback transaction, table variable will retain its value and wont revert back to the old value.

Any Procedure with a temporary table cannot be precompiled whereas execution plan with table- variables can be compiled statically in advance increasing execution speed.

Like variables, the lifetime of Table Variables lies only within the same scope. Unlike Temporary Tables, table variables are not seenin inner stored procedures & in Exec statements. Moreover,it cannot be used in INSERT/EXEC statements.

Table Variables in SQL Server 2014

Table Variables are used when user needs to work with small temporary data, for passing a list of values to stored procedures/functions for auditing purpose. We will discuss how the table variable functions in several areas:

1. Table variables in SQL Server transactions

Table Variables are not affected by a rollback transaction. The table variable gives the value of the data that was modified during the transaction even if the transaction is rolled back.

Let us take an example using Temporary table as&T and Table Variable as%T

CREATE table #T (avarchar(50))
DECLARE @T table (avarchar(50))
INSERT into #T select 'old value #'
INSERT into @T select 'old value @’
BEGIN transaction
  UPDATE #T set s='new value #'
  UPDATE @T set s='new value @'
ROLLBACK transaction
SELECT * from #T
SELECT * from @T
s             
---------------
old value #

s                
---------------
new value @

In the above example, we first assign each tables with same ‘old value’ string and we begin the transaction to update the contents. After transaction, both will contain same ‘new value’ string. However, when we rollback the transaction, the table variable retains its value ‘new value’ string instead of reverting to the ‘old value’ string. Though the table variable was alteredinside the transaction, it is not considered a part of transaction itself.

2. Passing Table variables to SQL Server stored Procedures & Functions

When we need to pass a list of values to stored procedures or functions, it is not easy to do with the single parameters especially when the list contains many attributes. This can be fulfilled by using input parameter like XML type, but it is not advisable, as it requires more work and XML needs to be parsed in stored procedure.

With the introduction of table variable, it can be passed to stored procedures & functions as a parameter solving the above stated problem.

We will see the example of using table variables as a parameter in stored procedures. We need to define the appropriate table type before passing list of parameters.

We will assume that the dbo.UserRole table stores user-role mapping in which user can have only one role and should be updated periodically by a provided list.

We create a stored procedure that takes the updated user-role mapping as parameter & updates the table data.

Assuming table variables passed to a stored procedure are READONLY. We cannot do any DML operations (insert, update, delete) on a table variable inside the stored procedure or function. In the below code, we will examine the call of stored procedure with a table-valued parameter.

Updated table looks like this:

Indexing SQL Server Table Variables

While indexing table variables, indexes can be created only in table definition and after declaration, it cannot be altered. However, with the introduction of SQL Server 2014, non-unique & composite indexes can be created. Following examples define ways to declare table variable in SQL Server 2014:

Limitations of SQL Server table Variable:

Table variable cannot be altered after its creation. Therefore, we need to make sure that we choose the right structure for the table variable and necessary indexes/ constraints are created in table definition.

SQL Server does not maintain statistics for table variables. So while using large temporary data set, it is advisable that we use temporary tables instead of table variables to improve performance.

We cannot use user-defined functions in the table definition. CHECK & DEFAULT constraints, COMPUTED COLUMNS can be created on table variables in definition.

While initializing a table variable, we cannot use SELECT INTO or INSERT EXEC Statements.

Table Variables can be passed to stored procedures and functions only as READONLY.

Conclusion

Table Variables can be of good help for users working with small temporary data as they allow passing a list of values to stored procedures/functions making development simpler. Though it provides many features of regular tables like indexes, constraints etc. but they have some limitations which are stated earlier. Table variables retain the present value after the rollback transaction that can be very useful depending on user’s requirements.

Read More

How Often Should I Backup my Database - Query Answered

"In recent time, some of our counterpart organizations have faced database corruption. Though our company takes backup every week, we have decided to become more careful. I would like to pose a question to the expert admins here: How often should I backup my database? Our company is a mid-sized organization and our database size is also moderate. Please suggest us some good backup policies."

The value of data for any organization is beyond description, yet, people often neglect its importance. That is why, the cost of data loss in US alone amounts more than $10 billion every year. Sometimes, data loss is not preventable; but a recent and complete back of the database can save us from losing the data for good. In order to save money, time, reputation and most importantly data itself, every company should backup their database regularly. Now the question arises regarding the frequency of database back. Many people want to know, "how often should I backup my database?" In this post, this question will be answered as we will discuss how oftem you must take backup of SQL Database.

How Often Should You Backup SQL Database?

The frequency actually depends on several things. Database size, how busy it is, the criticality of data, all play vital role to determine how often the backup to be taken. Usually the backup process is the mixture of complete backup, incremental backup (differential), and transaction log backup. There are different types of backup frequency policies that organizations can adapt according to their need.

1. Weekly: According to this backup policy, complete database backup is taken every week or 168 hours. Besides, the differential backup is done every day. This type of backup is usually chosen for databases that have only a few transactions every week or contain less important data. A good example can be the payroll database of a tiny business company where very little changes are done usually and any lost data can be recreated easily.
2. Daily Once: This is a simple backup policy where the complete backup is done every 24 hours without any differential backup. This policy is ideal for small companies with more transactions than the company mentioned in the earlier point. Usually, companies that issue manual receipts need this type of backup. As there are manual records, any lost data can be recreated with its help.
3. Daily Four Times: This database backup policy is ideal for any particular database of a mid-sized organization or a bank. These databases go through many changes every day. For this reason, this policy supports complete database backup every 24 hours and incremental backup every 6 hours.
4. Daily Six Times: This backup policy can be termed as the through backup. As per this policy, complete backup is done once every 24 hours, differential backup at every 4 hours and transaction log backup every 1 hour. This policy is for the organizations that will face disastrous consequences or interruption in business functionality if any data loss occurs. Mid-sized retailers and large enterprises usually adopt this policy.
5. Daily Eight Times: This policy has been designed for the database that is busy and contains business-critical data. As this system cannot afford to lose any transaction, transaction log backup is done every 15 minutes. Besides this policy includes incremental backup every 3 hours and full backup every 24 hours. Large online retailers, commercial bank, and hospitals are the main subscribers of this policy.

Quick Way to Restore Database Backup

The aim of database backup is to restore it when necessary. Due to any bug, the database may face issue any time. In case of major database corruptions, it is often impossible to access the database. Apart from these, any natural disaster can also cause damage to the database. In such cases, recent backup can help organizations to overcome the adverse situation. Users can take the help of SQL Backup Repair tool to restore the backup. This tool is an efficient one to restore and export .bak files into SQL database. It supports 2014, 2012, 2008 and all other versions of SQL Server database. File size is not an issue for this program as it can export BAK file of any size to any SQL Server version. This application restores the backup file seamlessly and helps to resume business operation.

Conclusion

In this post, we have answered a common query: “How often should I backup my database?" From the above discussion, we have learned that the frequency of the database backup completely depends on factors like its size, importance, etc. Users can read the types of backup policy and decide which one to choose for their organization.

Read More

How to Read BLOB data in SQLite in an Efficient Way

SQLite is one of the most commonly used database engine. Its source code is available in public domain which can be used for commercial or private purpose. Common data types used in SQLite are NULL, INTEGER, REAL, TEXT and BLOB. Unlike Blob, all other data types stores a particular type of data. Whereas BLOB data type stores binary data, which are typically images, videos, audio or even binary executable codes. This datatype is not only deals with SQLite, but also supported by most of other databases.

Why BLOB is so Important?

As talked above, BLOB data type contains binary data, which is not in readable form. Any type of data which is in binary form appears similar for human eyes. Even database managers, do not have idea what the blob data contains and how to deal with. From a forensic investigators point of view, these BLOB data contains crucial evidence regarding digital crime. The reason behind this is that most of the web browsers, Android devices, etc. uses SQLite files to store data.

Structure of BLOB Data

Each BLOB data contains two fields namely Type and Data.
create table table_name (..., Blob_type, Blob_data)
Each column in a Blob type are meant for different data. Below describes each column name and the type of data it holds.

• Text: Holds plain text of data. This excludes NULL values from the database.
• Data: Specially designed to hold encoded binary data contents. Here,it is used to represent NULL values, whereas %25 is to present a percent symbol.
• File: This file contains the filename that points to a particular file on disk.
• BLOB: This is a reference to a separate BLOB table in the database.

How to View Data from BLOB?

Since BLOB contains binary data, the viewing and analyzing of BLOB data is very difficult for human to understand. Simple SQL queries can be used to view and analyze BLOB data contents. Let’s see how it is possible.
Using Command line interface of SQLite:
Creating a table of BLOB type:
sqlite> create table blob_test (b_id blob);
sqlite> insert into blob_test values (x'01234234566789abcjghsjddef0123456789abcdef');
Viewing BLOB contents using SELECT query:
sqlite> select * from blob_test;

☺#Egë½═∩☺#Egë½═∩

sqlite> select ''||b_id from blob_test;

☺#Egë½═∩☺#Egë½═∩

Here, you can see that the contents are not in human readable format. SQLite provide another Query ‘quote’ to view the contents in Hex format. Lets see how this query works.
select quote(b_id) from blob_test;
This will give you result as follows:
x'01234234566789abcjghsjddef0123456789abcdef'
This is not a reliable method to view and analyze data from BLOB as it is not in a human readable format. Moreover, the investigator should be an expert in database management. To overcome this limitation investigator can use a smart tool to view and analyze SQLite BLOB data contents.

Suggested Solution- SQLite Forensics

Explore & Extract SQLite Database files using SQLite Forensic Tool.

Prominent Features of SQLite Forensics tool

Support for BLOB Data Type:
SQLite Forensic tool allows users to preview and analyze tables, structures, byte code, and the multimedia contents from BLOB data type.
Recovers Deleted Records:
The tool is capable to recover the deleted contents from the SQLite files. these deleted items are listed in separate tab so that investigator can analyze these contents easily.
Multiple Views of DB Contents:
User can view and analyze contents form the SQLite database in tabular form or in hexadecimal form. This helps the investigator in deeply analyzing the database file.
Recover Associated Journal Files:
The tool is capable to recover the associated journal files related to SQLite file. This is the backup file for the original data file so these can be the major residence of digital evidences.
Export contents to multiple formats:
The recovered artifacts can be exported and saved in different file formats. Various exporting formats supported by the tool are CSV, PDF and HTML. This enables user to access the collected artifacts in various platforms.

Conclusion

BLOB data type contains binary data. The blog discusses the manual approach to read BLOB data type but as it is not in human readable form,the blog covers quick solution to read and analyze the SQLite BLOB data type.

Read More

Understanding the Role of SQL Server Agent in SQL Server

SQL Server Agent is a component of Microsoft SQL Server that is responsible to execute & schedule tasks or jobs in SQL Server. It runs as a Windows service and starts automatically when the system boots. It basically performs the backup task, handles reporting services, log shipping, and user tasks like; T-SQL scheduling, etc. It also supports operators and alerts for administrator notification.

To store information, SQL Server Agent uses SQL Server to perform a Job and each Job contains one or more tasks to be performed which is assigned to every job step.

For example, backing up a database.

It runs a job on schedule according to a specific event and on demand. Let’s take an example, if you backup database of all servers and schedule the backup for specific time duration, then an error or notification may encounter while taking backup.

Components of SQL Server Agent:

The SQL Server agent uses the following types of components to perform its tasks.It defines the tasks that is to be performed and when to perform it.

Jobs

Job is performed on particular tasks that can be run multiple times. It can run on multiple remote servers and one local server.

A user can run jobs in different ways like; based on one or more schedules, according to one or more alerts and by executing sp_start_job stored procedure.

Job step is an action in a job. The Job step might be a Transact-SQL statement, Analysis Services server commands and executing SSIS package. Each step in a job runs on security context. For transact-SQL it uses EXECUTE AS statement and for others, it uses proxy accounts.

Schedules

It specifies when a job runs, more than one job can run on the same schedule and vice versa. It can be defined on a job if SQL Server Agent starts, specific date or time at one time, CPU utilization of computer at idle level and at the time of recurring scheduling.

Alerts

An alert is an automatic reaction to a specific event. An alert can occur during one of the following conditions:

• SQL Server performance conditions

• SQL Server events

• WMI (Microsoft Windows Management Instrumentation) events on the computer where SQL agent is running. 

An alert can perform notification to one or more operators and it runs a job.

Operators

The operator defines contact information of individuals for the maintenance of single or multiple instances of SQL Server. It does not contain security information. SQL Server notifies operators for one of the following:

• Pager 

• Email

• Net send

Proxies

The security context is managed by the SQL Server agent through proxies. It can be used in more than one job steps. Proxies can be created by sysadmin fixed server roles. The members of fixed server roles have unrestricted access to the proxies and only SQLAgentUserRole, SQLAgentOperatorRole and SQLAgentReaderRole can use proxies which are assigned to them.

Conclusion

The blog explains about SQL Server agent, their roles, components (Jobs, Schedules, Operators, Alerts, and Proxies, etc.) and how they are carried out in the SQL Server. The SQL Server Agent is the most important part of SQL Server, which specifies each and every task.

Read More

Microsoft SQL Server Analysis Services and Its Synchronization

SQL Server Analysis Services (SAAS) serves a broader view of data for analysis so as to make things simpler for business analysts. Implementation of SAAS gives an opportunity to users to ask compound query and convert it into actionable insight where information for better business decision making will be available. Instead of just collecting raw database of large size, the Business Intelligence (BI) methods ensure that first the data is converted into actionable intelligence.

Composition of SQL Server:

SQL Server Analysis Service (SAAS): This helps in Online Analytical Processing (OLAP) that comprises of data mining solutions. For understanding tends, patterns, specialized algorithms are used.

SQL Server Reporting Service (SSRS): This gives a solution to create, publish, and distribute business reports to the users.

SQL Server Integration Services (SSIS): This performs ETL operations (Extract, Transform, and Load). Means, the data can be extracted from source, manipulated as per requirement, and it import to the recipient data.

Talking about the components of Business Intelligence, it’s only SQL Server Analysis Service. The worth highlighting fact about SSAS is it OLAP instead of OLTP!

Online Transaction Processing (OLTP): OLTP database is frequently updated with statements like (INSERT, DETELE, UPDATE). Its basic features include:

·         Short Transactions

·         Simple Queries

·         Small portions of data involved

·         Frequent Updates

    Online Analytical Processing (OLAP): OLAP type database is queried using the SELECT statement. Its general attributes include:

·         Long Transactions

·         Complex Queries

·         Large portion of data involved

·         Less Frequent updates

Synchronizing Analysis Services using SQL Server Management Studio

In case of SSAS, synchronization can simply be considered as replicating database of System Analysis from one running instance to another. Database administrators generally keep a copy of database on test Server and then restore them to production Server. Synchronization is one of the finest techniques that can be adopted in order to ensure that all SAAS instances have same data. Here is how to proceed:

Step1) Launch SSMS and move to “Databases” folder. Right click on it and choose “Synchronize”.

Step2) A windows named “Synchronize Database Wizard”, type source Server and source database name. Similarly, provide credentials for destination database and Server.

Step3) a dialog box will appear on screen that will give the service to change location of the local partition. Utilize this option according to your requirement.

Step4) Provide security synchronization. Choose between any of the three security options. Click on “Finish” button to start synchronization.

Conclusion:

In the above description I have explained different services of SQL Server and synchronization with SQL Server analysis services. Step by step procedure of synchronization is briefly explained through which users will be able to understand the actual fact behind synchronization process. Further more details will be given to your in next blog.

Read More

Quick Way to Rebuild SQL Server Master Database

All the system-level configuration settings and login account information of SQL server are stored in the corresponding SQL Master Database files. It contains information about other databases that are present in SQL server. Master database consist 2 files – master.mdf and mastlog.ldf. Also other information details which are included in master database are processes, locks, remote logins, etc. Since it contains records of all the files existing in database, SQL Server will not even start if the master database is – corrupt or unavailable.

Rebuild Master Database from Backup

You can rebuild SQL Master Database by using an existing backup.

To restore the backup in order to rebuild the Master Database follow these steps:

Note: First to proceed for restore task, you must have backup of the master database file on and Server instance should be running in single user mode.

• Right click on the database then goto Task then Restore ->Database MDF from the system files.
• Select Task option.
• Select the Backup option and click Restore.

Rebuild Corrupted Master Database

The process of rebuilding the master database is divided into 3 sections-Prerequisites, Rebuilding and Post-rebuild tasks.

PREREQUISITES

For rebuilding the master database in their current settings, following tasks should be performed before you start rebuilding.

1. First record the server configuration values

select * from sys.configurations ;

2. In order to reapply the updates after rebuilding master database record all the hotfixes and service packs applied to server.

3. Keep record the location of all log and data files stored in the database. This should be done because rebuilding process saves all the files in their original location. So in case you have moved your files to other location you can do the same after rebuilding if you have the record of these files’ location.

4. Locate the backup of the master database.

5. Make sure that you are permitted to rebuild the master database. For this you must be a member of sysadmin fixed server role.

6. Make sure that copies of log, master and model files exist on the local server. The templates files are used during the rebuilding process and should be available. Location of template files is:

C:\ProgramFiles\MicrosoftSQLServer\MSSQL12.MSSQLSERVER\MSSQL\Binn\Templates

 I.   REBUILDING PROCESS

The task should be performed in active mode and the SQL Server resource should be taken in offline mode before starting.

• You can insert and run the SQL Server 2014 installation media. Other method is to run the command prompt and change the location of the directory to the location of setp.exe file present on the local server. The default location of the file on server is C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Release.

• In a command prompt window enter the given commands. The command prompt should be run as administrator. If you are using Windows operating system in which User Account Control is enabled, running setup may require more privileges.

Setup/QUIET/ACTION=REBUILDDATABASE/INSTANCENAME=InstanceName/SQLSYSADMINACCOUNTS=accounts[/SAPWD=StrongPassword] [/SQLCOLLATION=CollationName]\

NOTE: Ensure that the following points are kept strictly considered:

1. When specifying multiple accounts in /SQLSYSADMINACCOUNTS=accounts, separate the accounts with a blank space.
2. It is suggested to specify a strong password for sa account as it is vulnerable to get hacked.
3. If we change the server-level collation, it does not change the location of existing database. New databases will occupy new space by default.

A. After the setup process has finished rebuilding the database, it returns to the command prompt with no message. Verify the rebuilding process has been completed by examining the Summary.txt log file.

C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Logs.

II  POST-REBUILD PROCEDURE

After the rebuilding is done you need to perform the following procedure.

1. Restore the recent full backup of the master database. If the backup is not available or the current backup is not updated, recreate the missing entries. The most suitable way to recreate missing entries is by running the scripts that created the entries.
2. Restore the distribution database if the instance of your SQL server is configured as a replication distributor.
3. Move the master database to the previously recorded location.
4. Verify whether the previously recorded values and server-wide configurations values match with each other.

NOTE: In case you have changed the server collation do not restore the master database as it may replace the new server collation with the original server collation.

Limitations of Rebuilding Master Database

There are certain limitations that apply when you want to know how to rebuild SQL master database. If a new location is provided to the rebuild statement, the master database is rebuilt at that location. Also any user defined changes made in the master database are lost during the rebuilding process. When the Master database is rebuilt, it is recreated and saved at its original location.

Read More