Introduction:

Since SQL Server preserves data in a version, those facts need to move somewhere, but where does it shift? In which Filegroups come into play in SQL Server. These are the substantial files on your disc volumes that hold data and can be used for backup and administrative control purposes. An indispensable thing that comes along the way of the database is its security and protection that includes the restoration of the database too. Piecemeal restore process comprises a set of restore step sequences, initiating with the primary and, at least, one secondary read-write filegroups followed by read-only filegroups.

Summary:

The first component to recognize is what types of files SQL Server makes use of:

The Primary Data File is the startup data file holding data in SQL Server and signals to other files present in your database. They have a .mdf extension on them.

Secondary Data Files hold all data that do not relate to the Primary Data File. They only hold user data, and so are user-defined Secondary Files are optional. Moreover, a secondary data file can be used to continue growing the size of the database if it rises above the limited capacity of a single windows file. The recommended extension for these files is a .ndf extension.

Transaction Log Files are the data files containing all logging actions available in the SQL server. Each database must contain at least one logging file. The recommended extension for these files is a .ldf extension.

All data files are preserved in the filegroups cataloged in the following table:

Every database possesses a primary filegroup. This filegroup has the primary data file and any secondary files that are not part of any other filegroups. User-defined filegroups can be created to categorize data files together for administrative, data allocation, and placement means. For instance, let there are three files named file1.ndf, file2.ndf, and file3.ndf, built on three disk drives, respectively, and appointed to the filegroup named fgroup1. A table can then be created explicitly on the filegroup i.e. fgroup1. The three disks will advance queries for data from the table; this will enhance performance. The same performance enhancement can be achieved by using only one file created on a RAID (redundant array of independent disks) stripe set. However, files and filegroups enable you to add new data to new discs easily.

Rules to be considered while designing Filegroups:

• A filegroup can be used by only one database. For example, file data.mdf and data.ndf, which has data and objects from the sales database, can be used by only one database.
• A file can be associated with only one filegroup.
• Transaction log files can never be a part of any filegroups. Transaction log files can never be a part of any filegroups.

Sometimes, a database restore from the backup is desired. As we know, we do have the choice to restore needed file groups, but not all of the filegroups are needed to make the database online at a specific time. It is always enforced to recover the primary filegroup, but any secondary user-defined file groups are optional, at that instance, while doing the restoration. We can obtain partial data as it is available online after restoring it. We have to wait for the rest of the data to recover other filegroups.

The command RESTORE DATABASE with PARTIAL clause initiates a new piecemeal restore operation. The keyword PARTIAL points that the restore process includes a partial recovery. The partial keyword indicates and starts the partial-restore sequence. This will be validated and authorized during the restoration stages. As the database recovery has been delayed or postponed, so the position of the database remains pending.

To test the concept of piecemeal process, do follow the following steps:

1. Add another filegroup to preserve the static data. Add a table to the filegroup and insert a few records.
2. Query the table to validate or authorize the data
3. To change the filegroup state to read-only, use the “alter” database command and backup the database.
4. The database has three filegroups, one is read-only, and the other two are in read-write mode. Verify the filegroup status by executing T-SQL and backup the read-only database.
5. Drop the database to mimic the piecemeal recovery process of the database.
6. Perform the database restore operation before you start changing the session context to the master database. Now, we are going to do the read-write filegroups restore using the READ_WRITE_FILEGROUPS clause.
7. Next, restore the read-only filegroups and verify the output by querying the read-only table data.

Conclusion:

Preserving data file groups and database-recovery and healing processes to the requirements of the organization is crucial. For instance, let our SQL Server crashes then we are unable to bring this server up, now we need to recover databases on a separate server and bring them online. The database is enormous, and so recovery might take a few hours to finish. Here the Piecemeal database works. In this technique, we can recover the database on a filegroup level and can minimize the downtime outstandingly.

Introduction:

Sometimes due to the power outage or power shortage, we have to face some hardware or software issues regarding our system (computer). And when you log in again on the system afterward, you see that your most important SQL Server database data is not available. This database is so important to you that your all business accounting and financial records saved in it. One and only one solution remains when he/she has to face this kind of problem. And this solution is to reboot the system many times, sometimes it works, but the working probability of this solution is too low. And system always shows the disputable message that the database not found or corrupted. And sometimes you contact the service provider, and they said this software version is no longer supported. We will show some scenarios through which we can solve this corruption issue. After these scenarios and at the end of this article I will suggest you a software which will solve all your problems with only one to two clicks.

Power out problems

Depending on your production environment, you may have some Power out problems due to this, the server may be shut down, and the database can be corrupt. For this scenario, try to have a UPS for your server or a battery backup system.

Backups

While searching for a solution, the first solution is to have a recent backup of your most important database if you have this one then don’t worry about it. Your SQL server is smart enough to tackle this of problems. It automatically recovers your corrupted database with the help of SSIS or some scripts and saves your entire database but, in case if you have no recent backups now this time you should worry about that you are going to face a huge loss because your database has all kind of transaction and financial records of your business.

Torn Page detected

What is Torn page?

It is a setting to detect database corruption in SQL Server.

It detects failures related to I/O headers that try to access a page that has miswritten to the disk. It reports an I/O message an error (torn page) detected throughout the reading.

Afterward, while searching for a solution when you have no recent backups for your database. You can take a copy of MDF and LDF

Hex editor:

What is the hex editor?

A hex editor allows checking the manipulation of the elemental binary information that constitutes a data file. There are several hex editors on the web that are free like UltraEdit, HxD, BVI, etc.

You find that a table sys.allocation_units which was the part of your database. You can use the hex editors to verify the data corrupted and using the page number edit the data and fix it:

Proposed Solution:

Detach the repaired database from the failing environment. Copy the MDF and LDF files back to the customer’s environment. Recommended scripting the entire database with data and recreating to get it back to a safer position.

Another way to repair is by using the T-SQL commands. You can use the DBCC CHECKDB to check consistency errors in the database and also you can repair your database.

For more information about the DBCC CHECKDB commands, please refer to this link: DBCC CHECKDB (Transaction-SQL)

Alternative Solution:

As I told you at the start of this article that I will suggest you a software which will solve all these kind of problems with only one to two clicks. This software name is Stellar Repair for MS SQL. It repairs corrupt MDF and NDF files and maintains the database integrity.

This SQL Database Recovery Software provides powerful and distinctive options that will address and repair all types of corruption from the SQL database.

The software has excellent features to repair the corrupt MDF and LDF files. It also recovers the modules of databases such as the table’s keys, stored procedures, etc. The beauty of this software is that even you can recover the deleted records from your SQL database.  This software allows a user to save the repaired SQL database in different formats like MS SQL, HTML, CSV, and XLS formats. It supports more than ten versions of MS SQL.

• While launching this software, the window will look like this.

• Press Browse to select the corrupted MDF file:

• Click Repair to start the repairing process

• After completion, this message popups. Press OK:

• Now save the file.

The software will repair the database and all the information will be restored.

Conclusion:

In this article, we talked about the most common scenarios to recover a database corrupted due to a power outage. These scenarios are faced by SQL DBAs which is related to database corruption. This type of SQL recovery software can play an important role to fix database corruption. Just try it and you will have a pretty nice experience.

Working in teams is an essential part of today’s modern workplace. An increasing number of workers are remote, mobile, or work from different time zones and geographic locations. In this blog post I will share details about Microsoft 365 tools that facilitate teamwork for all workers, no matter where they are located or how they connect to your organization’s resources.

Teamwork tools in Microsoft 365

The nature of teamwork continues to evolve as teams of various shapes and sizes come together for short-term and long-term projects.  Microsoft 365 give your organization the right tools at the right time for the right task, along with common services to help you work fluidly across applications.

The teamwork products benefit from the same enterprise-level security, compliance, and manageability as the rest of Microsoft 365.

Outlook:

You must be familiar Outlookemail-based experience which help you stay in touch with colleagues, and share calendars, files, and tasks, to make sure important deliverables get attention.

SharePoint & OneDrive for Business:

You can store your content in the cloud with SharePoint and OneDrive for Business. This lets you access your files on any device and share them with others inside and outside your organization. Because the files are in the cloud, team members can collaborate on them in real time using familiar Office applications like Word, Excel, and PowerPoint.

Microsoft Teams

Microsoft Teamsis the digital hub for teamwork in Microsoft 365. It brings together team conversations and content so your users can stay up-to-date on critical projects. It includes everything teams need to stay connected like chat, phone calls, content, and meetings. It can also be customized with applications and bots that support a given project. With guest access in Teams, you can invite people from both inside and outside your organization to work on projects.

Yammer

Yammeris a community conversation tool designed to help encourage open dialogue, idea generation, and connections across the company. Yammer lets you create communities of interest and forums that bring people together, improve transparency, and give everyone a voice. You can even grant external access to partners and customers as needed.

Microsoft Graph

Microsoft 365 is built on an intelligent fabric that keeps it all connected and secured no matter what app or service you are working in as a team. Microsoft Graphprovides a seamless connection between people and relevant content.

Office 365 Groups

Office 365 Groupsenable a single team identity across apps and services and centralized policy management enhances security and compliance.