Restore SQL Server Database from Multiple Backup Files: Learn the Best Practices

SQL Server can come across errors at any time. To tackle any severe situation, it is always standard practice to backup SQL database. The backup of primary and secondary database files, as well as Transaction log files, are saved as .bak files. In case the database encounters some irreparable errors or there are some hardware issues, users have no other option but to restore SQL database backup files to the Server.

Backup restoration is not a tough task to perform if you have proper knowledge about the process. But the thing gets critical when one has to restore multiple backup files. Even SQL DBAs and other experts cannot restore multiple backup files to SQL Server database easily. To help them, we will describe some of the most used methods of restore SQL Server database from multiple backup files. Depending on the requirement, they need to pick the right solution. But first, we will check out related user queries.

User Query 1: “SQL Server is not permitting me to restore multiple .bak files in a new server when I attempt to restore all at once. When I try to add multiple backup files to the backup software, it accepts only the first one from the list. Before I try any manual technique, looking for your opinion whether this is feasible or not.”

User Query 2: “Hello all. I am looking for an approach to restore all the SQL backup databases on the SQL server. The main issue here is that I have a large number of backup files and I want to restore those all in one go. Please tell me is it even possible or not? If Yes, then tell me an easy solution.”

How to Restore SQL Server Database from Multiple Backup Files

When it comes to restoring multiple BAK files to a SQL Server, users can take the help of manual as well as automated method. In this section, we will check out two manual techniques for multiple backup file restoration and then we will learn about a tool that can also conduct the restoration process.

Restore Multiple SQL BAK Files SQL Server Management Studio Manually

With the help of SSMS, it is possible to restore multiple backup files. But first, split backup files need to get created. Here is how to do this:

  • Open Object Explorer and expand Databases
  • Select the database you need to backup and right-click on it. From the list of options, choose Tasks >&ft; Back up…
  • On the next window that appears, select the General tab. Choose the source database and type of backup. Click on Add button to determine multiple backup paths.
  • To add each path, either type the path manually, or Browse to the folder where the backup file will be saved. When done, click on OK. Repeat the process for all the destinations.
  • When all the paths get added and displayed on the window, click OK to begin backup process. You will get a message when it is done.

Now comes the process of restoring these split backup files:

  • Go to the Object Explorer of SQL Server and right click on Databases on the left panel. Choose Restore Database…. Option.
  • In the General tab of the next window, enable the Device radio button. Click on the Browse button to select all the backup files.
  • Click on Add button from the Select backup devices window to add all the backup file locations.
  • Either type the full path address or navigate to the backup file through the folder tree structure. Repeat this for all the locations. When the listing is done, click OK.
  • All the paths will get loaded and then destination database name along with restoration type will be displayed. Finally, click OK to finish the restoration process.

Manually Restore Multiple SQL Server Database BAK File Using T-SQL

These TSQL commands can be used to restore multiple backup files to SQL Server database:

Restore One Full Backup File and One Transaction Log Backup

Here, the first command will restore the database and leave it in a restoring state. The second command, however, restores the log file backup and put the database in useable condition.

restore

Restore Full Backup and Two Transaction Log Backups

These scripts will restore the first two backups (one full backup and one log backup) with NORECOVERY and then for the last log file with recovery.

restore bak

Restore Full Backup, Latest Differential and Two Transaction Log Backups

In order to restore three initial backups with NORECOVERY and the last one RECOVERY, use the script.

restore bak file

Restore SQL Server Database from Multiple Backup Files Using Application

If you are looking for an easy technique to restore more that one SQL BAK file at once, you can choose SQL Backup Recovery. This application supports restoration of any number of backup files to all the latest SQL Server versions including 2017, 2016, and 2014. This tool also has to file size limitation for BAK files. Users can export any number of MDF, NDF and LDF file backup using this tool.

Conclusion

If you are facing the need to restore SQL Server database from multiple backup files, you can follow the methods provided here. To perform the manual methods, you can take the help of step-by-step instruction shared here. Else, you can use the cited software for smooth and hassle-free restoration.

Advertisements
Posted in Sql Server, T-SQL | Leave a comment

Check Out How to Open LDF File in SQL Server

The data of SQL database can be broadly categorized into two primary file types. One of MDF file and another is LDF file. MDF file is the master database file that contains the basic data and LDF file (log database file) stores all the transaction logs that take place in the database. Sometimes, users need to know how the transactions are being recorded in the log file. At that point, they need to know how to open LDF file in SQL Server. If you are also interested in opening SQL log files, you can do that using the native technique. Here, we will tell you how to open SQL LDF files easily.

How to Open LDF file in SQL Server

In order to read LDF file by opening it, users need to use a function named “fn_dblog.” This function offers data that shows what is happening in your log file when you perform a transaction. This must be tested in the lab-environment at first. To run this (fn_dblog) function, a beginning log sequence number (LSN) and ending LSN of a transaction is essential. For this function, NULL is the default that will return all LDF file records.

Creating a Sample Database
Here, we will create a sample database and a table to check the functionality of LDF file by running this SQL code.

The recently created sample database is named “ReadingDBLog” and its Table location contains three columns. Now is the time to check how the processes of database and table creation performed by SQL Server have been registered in LOG file. For this, we need to run this command:

The result shows that 339 rows have been made for creating an empty database and a blank table. Try this function to learn about the processes used for database and table creation.

Open LDF File to Check How DML Commands are Captured
Now, we will check how DML commands like insertion, updating or deletion get logged in LDF file. The process of page allocation or de-allocation can also be tracked by this operation.

We will check the LDF file again and this time, we will filter it because the database log file contains a huge amount of data.

After running this script, you will get output similar to this image that shows INSERT.

Similar kind of info will get displayed in case of UPDATE and DELETE.

How to Open Log File to Find Internal Operations of SQL Server

Certain internal functions of SQL including page splitting can be checked by this function. All the relevant information about page splits including number of occurrence of page splitting, on which operation and page did it occur. Run this SQL scripts to view all page splits related operations for the above INSERT operation.

We can see here that Page Split happened three time for the INSERT activity as the first script allows us to know. The second script explains all the internal SQL processes done during the first Split Page. If users perform any other tasks, it will get logged in the LDF file and this function will display it.

In Case of Backup

As the final task, we performed a backup and opened the log file to see what is written there:

After opening the log file we can see that the number of rows has been decreased at an alarming rate after the backup. The reduction of rows from 528 to 9 means the section of log file that recorded those transactions have been moved to the backup file. At the same time, original log file entries have been removed. If users want they can shrink LDF file if needed.

Conclusion

In this post, we have elaborated the manual approach check the LDF file data in SQL Server. If you want to know how to open LDF file in SQL Server, you can follow the guideline provided in this post. Users can also try SQL Log Analyzer for a simpler and easier solution to open and view LDF file.

Posted in Sql Server | Leave a comment

Top Free SQL Server Tools & Performance Tuning options from Microsoft

Top Free SQL Server Tools & Performance Tuning options from Microsoft

(A Benefit to the company)

microsoft-tools-training

I am writing this blog to share the importance of the tools provided by Microsoft along with SQL Server to use to monitor the health of the databases, performance related issues and troubleshoot the same. Microsoft has provided several tools and other options for such scenarios related to SQL Server Databases and these are absolutely free therefore it is better to use these tools instead of procuring third party tools. By using these free tools we can reduce the cost which cast to the company. However there are several free third party tools available in the market but these are not secure and not recommended as per the company policy as well.

Therefore how to prevent these issues and run the SQL environment secure and smooth, it is recommended to use free tools provided by Microsoft.

Free Tool from Microsoft

  1. Profiler
  2. Perfmon (Performance Monitor)
  3. Activity Monitor
  4. Extended Events
  5. DTA (Database Tuning Advisor)

Other Options

  1. DMV (Dynamic Management Views)
  2. Trace Flags
  3. DBCC (Database Consistency Check)

Profiler:

Profiler is a user interface tool which can be used to monitor overall health of your database and instance by tracing inside the SQL Server. It gather the complete information about the event which we have selected to monitor e.g. deadlock info etc. and give us the result in form of a trace file which we can use and check thoroughly and find the cause of the issue.

Path: Run — type Profiler — Profiler window will open

Perfmon:

Performance monitor or Perfmon is another great tool which can be used to set the counters and see the result graphically. This tool provides the overall picture of the server health which includes the counters information as Processor, Memory,Buffer etc.

Path:  Run — type Perfmon — Perfmon windows will open

Activity Monitor:

This tool is available in the SQL Server Management Studio and can be used to monitor the processes, recent expensive queries, resource wait etc. It displays the information graphically as well. It is easy and very much handy tool to find the quick information about the system along with the processes running.

activity monitor

Path: Open SSMS (SQL Server Management Studio) —connect to the instance —Object Explorer — Activity Monitor

activity monitor -ssms

Extended Events:

Extended events is a more useful tool which can be used to monitor server in below cases:

  1. Application Tuning
  2. Troubleshooting Performance
  3. T-SQL Query Performance Analysis

There are predefined template available in SQL Server Management Studio which can be used while configuring the extended events while we can also design a custom event. Mainly available template focus on Locks & Blocks, Query Execution & System Monitoring. Always On monitoring can also be done through it.

We can configure the Extended event, captures the event which we want and troubleshoot the problems.

extended events

DTA (Database Tuning Advisor)

It is another tool which is used to tune the query and advise us about the specific requirement of the indexes. We can find this tool in Tools menu options in SQL Server Management Studio and we can open it in search programs in window.

DTA can be useful in such cases:

  1. Tuning Queries
  2. Adding indexes (clustered, non-clustered and indexed views)
  3. Adding partitioning
  4. Adding statistics

Don’t Do:

Do not start DTA when SQL Server is running in single user mode.

Trace Flags

Trace flags plays an important roles in database administration. These are frequently used to diagnose performance issues or to debug stored procedures. As I have already written a blog on this so please go through the link

https://mirzahusain.wordpress.com/2017/12/31/trace-flags-in-sql-server/

DMV (Dynamic Management Views)

DMVs are used to fetch the server state information and which can be used to monitor the health of the instance, diagnose the problem and tune the performance.

There are several types of DMVs available.

E.g. OS Level, Index Level etc.

DBCC (Database Consistency Checker)

DBCC is used to check the consistency of the databases. These commands are used for maintenance of databases, tables, file group and indexes.

E.g.

DBCC sqlperf (logspace)

DBCC checkdb

DBCC loginfo ()

Note: Hope you have got the valuable information, I have just provided a brief about the tools however if you want detailed description please go through Microsoft blogs.

Please Share it

Posted in Database Administration, Other, Sql Server | Tagged , , , , , , , , , , , | Leave a comment

Let’s Discuss the Importance of Backup in SQL Server

There should be proper planning done to create backup of SQL Server Database. As all the data saved within the database is prone to get corrupt in several ways such as any human error, network intrusions, natural catastrophes, poor power outrage, any hardware failures, etc can damage your most crucial data, thereby leading to data loss or data inaccessibility issue. Hence, here comes the importance of backup in SQL Server. So, let’s elaborate it and make the best use of this article.

Importance of Backup in SQL Server

In order to minimize the risk of disastrous data loss, it is wise to take backup of the SQL databases. This backup file will help to preserve from any modifications that may happen while working with SQL Server on a daily basis. Therefore, a well-planned backup will definitely help a user to protect the database against data loss that may happen due to different failures. In short, taking a backup copy of the database is the only way to protect the data.

If a user takes necessary actions after understanding the importance of backup in SQL server, then he/she can redeem the data from various failures like media failure, user or human errors such as dropping a table by mistake. While coming to hardware failures, it can a corrupted disk drive or a permanent loss of server. Other failures can be due to natural disasters. Additionally, whenever a backup copy of the database is taken, it will be extremely useful for the routine administrative purpose like copying a database from one server to another server. Basically, backup is nothing but a copy of data that can be used to recover the files after the occurrence of any catastrophes.

As we understand that backup is a replica of information, hence simply copy and pasting the MDB(Master Database File) won’t work. A user must run a process via the backup command within the SQL server. This will help to create a complete, page-by-page copy of the database, which includes even every single object of the database.

Different Objectives of SQL Server Backup

Instead of focusing on the importance of backup in SQL server, a user should also ensure to carry out the backup process accurately. Also, storing and maintaining them at the right location. As a wrongly done backup process is anyways more harmful than no backup at all. Here are the major points that is to be understood by a user while initiating the backup process.

  • Maintain the Frequency: A disaster is always an unwelcome guest. Hence, this has to be made clear at the very first itself, as you cannot go for weekly backups if the organization does not want to lose a single day’s work. Therefore, it is suggested to backup the database on a daily basis for full backup, including the log backups and differential backups.
  • Demonstrate the Backups: As it is always said that prevention is better than cure, similar such theory applies for backup. Once the backup file is created and also stored at the safe location. A user must implement certain restore commands so that it will be easy to recover the files if any disaster occurs in future in a straightforward manner.
  • Risk-less Storage: Usually, the backup file is stored in the local drive. However, the data stored in the drive is also not that safe enough as it is also inclined to data corruption. Moreover, a user must be clear about the number of files and copies of the backup stored.
  • Backup to be Retained: It is often that most of the users while deleting unwanted data may also unintentionally delete the backup file of the database. Hence, make sure that you follow the retention policy and not delete before its right time. Also, use DBCC CHECKDB command to verify that the backup file is not undergoing any corruption. In order to reduce the storage size of the backup file, use compressor so as to occupy less space

An Expert Approach to Recover Data from Corrupted Backup File

Although, after following all the above described approaches to maintain the backup file properly, still if in case the backup file itself has got corrupted. It will be a huge loss as it is a challenge to restore the lost files from the backup file. Under such circumstance, do not get panic instead simply switch to the efficient utility named, SQL Bak File Repair. It is a top-notch tool comprising of power-packed features that helps to restore and export the corrupted backup file(.bak) to SQL server in no time. It also ensures to recover the MDF and NDF file from the damaged backup file. Moreover, it can support the backup file recovery of SQL server 2014 and all the previous versions.

Concluding Words

While working with SQL server, as a precautionary act it is important to backup the SQL database as we have clearly discussed about the importance of backup in SQL server. Considering this aspect, we have described various objectives that is to be kept in mind while dealing with the backup file. Apart from that, if the backup file has got corrupted by any means, then simply adapt the automated tool to restore the files from the backup file instantly.

Posted in Sql Server | Leave a comment

Introduction to SQL Server 2019

Introduction to SQL Server 2019

SQL server 2019

Microsoft has announced the first public community technology preview (CTP 2.0) of SQL Server 2019. It is powerful and enhanced version of SQL Server. This SQL Server 2019 has a lot of new features, enhanced existing features, more on security and mainly focus on big data analytics. SQL Server 2019 comes with Apache Spark and Hadoop Distributed File System (HDFS) for intelligence over all your data.

Here I’m giving a glimpse of main points of this release.

Platform Support:

SQL Server 2019 supports the platform as below:

  • Windows
  • Linux
  • Containers (Docker & Big Data Analytics)

Main Highlights

  • Intelligence over any data
  • Choice of language and platform
  • Industry-leading performance
  • Advanced Security features
  • Make faster, better decisions

Database Compatibility Level

150

Top 10 Reasons to Choose & Use SQL Server 2019

Top 10 Reason to choose SQL Server 2019

To learn more, attend a free Webinar hosted by Microsoft on “Introduction to SQL Server 2019” on 9th Oct 2018.

Note: Please change the details while registering for Webinar.

Register to Webinar

Summary:

As I have given an idea about SQL Server 2019 , its new features, benefits and a lot of more to come to explore on documentation on Microsoft. Hence go through Microsoft site to explore more. Moreover I’ll also share more on this in coming blogs.

—————————–Hire Me———————————

Hire Me

Posted in Database Administration, Microsoft News, Mirza Husain, Sql Server New Feature, T-SQL | Tagged , , , , , , , , , , | Leave a comment

SQL Warning Fatal Error 605 occurred? Here is A Complete Profound

If you are getting SQL error 605 while working with SQL Server then, this troubleshooting guide will help you resolve this issue.

User Scenarios:

Query-1 “I am acquiring following error while running queries from one of my tables:

sql 605

In order to resolve this, someone told me to run DBCC CHECKDB command. I ran the check however, it reported 0 allocation errors along with 0 consistency errors hence, I am not sure where to go from here. Please help!”

Query-2“Code, which was working fine till last week is suddenly throwing this exception:

sql error 605

We are using .NET Framework 4.0 on the SQL Server 2008 R2 and this exception is thrown while running the stored procedure. I have no idea that how to fix this issue. Is there any solution for this?”

SQL Server Fatal Error 605 – Problem Description

Sometimes while users try to login with a user who is not a System Administrator user then, they encounter this Microsoft SQL Error 605 message. When the severity is Level 21, the session of a user becomes disconnected. After that, the error is written into SQL ERRORLOG and the Windows Application Event Log as EventID=605. The severity also can be 12 that will just throw the error as result to the client application.

In this error message, the first allocation unit that is after the “belongs to…” is the real allocation unit. The other allocation unit that is after “not to” is the expected allocation unit.

Note: An allocation unit is an ID that determines that from which index and object a page belongs to.

SQL Warning Fatal Error 605 occurred – Find the Cause

1. Error: 605, severity: 21, state: 3.
This error message can create a serious issue with the database page or with SQL Server engine while detecting the expected allocation.

2. Error: 605, severity: 12, state: 3.
The severity of this error message also can be Level 12 if the execution of query becomes failed while using the read uncommitted isolation level. Or, the NOLOCK query executed that is also called “dirty read”.

Microsoft SQL Error 605 – Find the Solution

The manual methods to resolve the SQL error 605 depends upon the reason behind its causes. Hence, recognize the cause and opt the fix accordingly.

1. If the Error is Occurred Due to Severity 21

The page may be damaged or incorrect if you encounter the error code 2533 after running DBCC CHECKDB or the CHECKTABLE command. Hence, to resolve this issue, one needs to restore data from the server backup file. In case if the backup file in not present then, utilize DBCC CHECKDB command to repair the data file. Below points will help you to determine the actual cause of this error:

  • Analyze all the issues that are associated with hardware and system
  • Make sure to enable PAGE_VERIFY=CHECKSUM on SQL Server
  • If the backup file of SQL Server is available then, try to restore data

In case if the issue occurs even after executing DBCC CHECKDB command then, contact with the Microsoft technical support team for further help.

2. If the Error is Occurred Due to Severity 12

  • Try not to use the reading uncommitted isolation level
  • Do not modify the existing tables during execution
  • Repeat query until you did not get the error code

MS SQL Error 605 – Resolve it by Expert Solution

SQL Database Recovery tool is an ultimate solution to repair corrupt MDF and NDF SQL Server Database and export it into SQL Server. The software is capable enough to recover the deleted SQL database table’s data. Using this, one can even scan and recover Triggers, Functions, Rules, Tables, Stored Procedures. It provides support to both ASCII and Unicode XML datatype and maintains the folder hierarchy throughout the process.

Final Words

This article includes a complete information about SQL error 605. Here, we have discussed different manual solutions to resolve this SQL Server fatal error 605 . In case, if the manual tricks do not work for you then, an alternate solution is also recommended here.

Posted in Sql Server | Leave a comment

DBA- Interview Questions & Answers

Hi Friends,

This blog is based on the interview based questions.  I have collected, summarized & prepared the answers and presenting in front of you.

SQL-dba-interview-questions-answers

These questions are based on Page & Extents in SQL Server.

  1. What is the fundamental unit of data storage in SQL server?

Ans. Page

     2. What is the page size?

Ans. 8 kB

  1. What is the page header size?

Ans. 96 byte and it is used to store system info about the page

  1. Name the different types of pages in SQL server?

Ans.

  • Data
  • Index
  • Text/Image
  • GAM (Global Allocation Map)
  • PFS (Page Free Space)
  • IAM (Index Allocation Map)
  • BCM (Bulk Changed Map)
  • DCM (Differential Changed Map)
  1. Does log files contains pages?

Ans. No, log files contains only a series of log records

  1. Name the type of extents?

Ans.

  1. Uniform Extents
  2. Mixed extents

7. What is PLE (Page Life Expectancy)?

Ans. It is a perfmon counter, found in the SQL server buffer manager. PLE is the best   indication of how volatile your buffer pool is. Volatility is measured by taking the   average”life” of a page within the buffer pool.

So if lots of pages are being overwritten with new data very often, the average PLE will be low and our BP volatility will be high.

Formula:

PLE threshold= ((maxbp(mb)/1024)4)*300

We can find the vaule of PLE by running this command

T-SQL

[SELECT [object_name],

[counter_name],

[cntr_value] FROM sys.dm_os_performance_counters

WHERE [object_name] LIKE ‘%Manager%’

AND [counter_name] = ‘Page life expectancy’]

8. What is PFS?

Ans. Page free space (PFS) pages record the allocation status of each page. After an          extent has been allocated to an object, the Database Engine uses the PFS pages to    record    which pages in the extent are allocated or free. This information is used when the Database Engine has to allocate a new page.

A PFS page is the first page after the file header page in a data file (page id 1) followed by GAM & SGAM pages.

9. What is DCM?

Ans. DCM stands for differential changed map. This tracks the extents that have changed since the last backup database statement. Differential backup just reads the DCM pages to determine which extents have been modified.

10. What is BCM?

Ans. BCM stands for bulk changed map. This tracks the extents that have changed since the last backup log statement. Although BCM pages appear in all databases, they are only relevant when the database is using the bulk logged recovery model.

Note

Your likes & comments are always appreciated.

 

Posted in Database Administration, Q & A, Sql Server, T-SQL | Tagged , , , , , , , , , , , , , , , | 2 Comments