Assign/Revoke Administrative Access on Deleted OneDrive Using PowerShell

When a deleted OneDrive is restored then its also important to give access to some user. The easiest way to restore a deleted OneDrive is to restore the deleted user but this is not ideal case every time, so you must restore the deleted site/OneDrive and then assign administrative access to an active user using Set-SPOUser cmdlet. This command requires the URL of OneDrive, user login name and token of administrative access.

Assign access OneDrive

Important: You must have administrative access to run the command.

Restore SQL Database from One Server to Another

Introduction

In this new article, we will show how to restore a SQL Server database on a different server.

For example, if the old server fails and the database stops working as well, you may need to migrate and restore SQL database from one sever to another server.

The database can get corrupt when a virus attacks the database or sometimes due to a hardware failure like an issue in the hard drive. Hacker attacks can also damage any database. In situation of server damage you may need to restore your MS SQL database in a new server. The SQL Server is required to be installed on the new server.

Requirements

The following requirements are necessary for this article:

  1. Any SQL Server version installed.
  2. The SQL Server Management Studio (SSMS) installed on the machine.

Getting started

In case of failure, we will try the DBCC CHECKDB.

The DBCC CHECKDB allows to repair the database if it is damaged. In order to repair a database, we will run the following T-SQL command in SSMS:

Note that the database must be in single user mode. In order to set it to single user mode, you can use the following command:

If you do not like T-SQL, you can use the SSMS. To do that in SSMS right click the databases and select the Option page and in State select single mode for the restrict access option:

SQL Database in Single User Mode

Using Stellar Repair for MS SQL

If the manual process of repair fails, it is possible to repair the corrupted database using SQL database recovery software: Stellar Repair for MS SQL. The software solution is more reliable and effective to overhaul tougher database corruption cases.

The first thing that we are going to do is to stop the SQL Server Service in SSMS:

Stop SQL Server

Stopping the MS SQL Service will allow us to repair the file. If the service is not stopped, the file is in use and cannot be repaired. To repair the file, you need to download the software from official website.

Once that the MS SQL Service is stopped, you can move your mdf file to another machine. Using a USB or an external hard drive if the network connectivity fails, copy the mdf to another machine. The mdf file contains the database information in SQL Server.

In the new machine, open the Stellar Repair for MS SQL:

The ‘Browse’ button allows to browse and look for the .mdf migrated from the other server. By Using Search option, user can search the database from a specific folder.

By default, the MS SQL Server databases are stored in a primary data file with the MDF extension. If the file is too big, you can split the file into one primary file and multiple secondary files. This configuration can help in the performance because your files can be stored in multiple hard drives and give a faster response as multiple hard drives are working. Another advantage is that you can backup faster since it requires some of the data files instead of the entire database.

Once you select your primary file, you can use the ‘Repair’ button to repair the damaged database. Once repaired you can restore the SQL database in the new server using the software.

If you are going to repair the old server, it is a good idea to have a backup of the database there. You can use the restored database in the old and the new server at the same time. Even when Stellar Repair for MS SQL can repair a damaged database, it is always recommended to periodically back up your database for security reasons.

Conclusion

In this article, we learned how recover a damaged database and restore it in another SQL Server. To do this we need to stop the SQL Server data file from one server to another using the network or an external device. When mdf is in the new Server, you can use the Stellar Repair for MS SQL to repair the mdf file and once repaired, you can use the software to select the new server as the server to restore the database.

References

If you want to know more information about mdf files, migrating databases, you can refer to the following links provided:

Efficiently Repair Corrupt SQL Backup File and Restore in Live SQL Database

SQL database can encounter some serious issues anytime and often, the users have no other option other than restoring the last good backup file. But what to do if the SQL backup file itself becomes corrupt? Sounds tricky, right? Do not worry as we will share the ideal solution to repair corrupt SQL backup file.

“Due to virus attack, our SQL database got infected badly. Even after trying several manual methods, we failed to remove the corruptions. The only option left was to perform a BAK file restoration. But the real surprise was waiting for us. During the restoration, we found out the BAK file has also been damaged. The database contains mission-critical data and we cannot see any other option than to fix the BAK file. Does anyone know how to repair corrupt SQL backup file and restore it in Live SQL database?”

SQL BAK file corruptions also affect numerous SQL Server users. Therefore, we see people complaining about BAK file corruption as they do regarding MDF or LDF file corruption. Understandably, BAK file corruption causes more trouble than MDF or LDF file corruption. The reason is, users can restore BAK file during database corruption but in case of backup file corruption, there is no other alternative. Moreover, there is no manual approach to repair corrupt SQL backup file.

Because of these issues, damaged SQL BAK puts SQL Server users in a vulnerable situation. This write-up will focus on how they can get rid of this situation in an effortless manner. Continue reading to learn the stepwise automated method to fix SQL BAK file corruption.

How to Repair Corrupt SQL Backup File and Restore it in Live Database or .CSV Script

As mentioned earlier, if your SQL backup file has gone corrupt, you cannot fix it manually. But, SQL BAK file repair tools like SQL Bak Repair can easily solve this problem for you. This application has the ability to remove SQL BAK file corruptions. At the same time, it can restore the corruption-free healthy backup file in live SQL database or in .csv format. Let us share the complete process of BAK file repair and restoration for your convenience.

1. Download and start the tool and click on Browse  to load the file.


2. To add a single file, choose ‘Scan Options’ tab and click on Browse button. Select the corrupt BAK file. Also, choose the correct SQL Server version or allow the software to auto-detect the version.
Or

To repair multiple BAK files, select ‘Multiple Backup Files Option’ tab. Then, you can add bulk backup files by Add File(s) or Add Folder options. You can also ask the tool to fetch all the backup files from which you can select the necessary ones.

 

3. When the files are loaded, click on Recover button to get rid of all the corruptions.


4. After scanning, the software will show the summary of recovered item list. Click on Close.


5. Now, all the components of SQL BAK file will be available for Preview. You can select any item to get its preview.
6. Click on Export button to restore the backup file.


7. A dialog box will appear where you need to choose certain settings. Select between SQL Server Database and SQL Server Compatible SQL Scripts under ‘Export To/As’ section.


8. Choose the database authentication mode and provide necessary information.


9. Also, choose the destination database and collation settings.
10. Now, it is time to select the database components you wish to restore. Also, select ‘Export options’ between Export with Only Schema and Export with Schema and Data.


11. Click on Export to start restoring the backup file.


12. The tool will notify you upon the completion of the restoration process and show you the export records.
13. When you click on Close button, the application will prompt you whether you want to export the files in CSV file format. Click Yes and choose a folder to save the CSV file report.


14. Open SQL database and you will see the restored data there.

How the Avail the Application

If you want to use this software, there are two different versions available. You can download the demo version of the tool to perform SQL BAK file repair free. But this version has some limitations as it will only allow you to preview the items of SQL backup file. For complete data recovery and backup restoration, you need to purchase the full version. The purchase is done by fully secured gateway and your data will remain completely secure.

Final Words

If you are looking for the solution to repair corrupt SQL backup file and restore in Live SQL database, here is the right method for you. Using this technique, you can remove all corruptions from the BAK file and restore it in live database. This tool will also let you save the details as .csv script.

Restoring a deleted OneDrive using PowerShell

In Office 365, when a user is deleted, his/her OneDrive will be automatically deleted after the given retention period which is by default 30 days. Only a Global Administrator or SharePoint Administrator can restore a deleted OneDrive and assign access to some other user.

All the shared content will be available as they were available before deletion of OneDrive.

Important: One Drive can be restored after 93 days of its deletion. If retention duration is 30 than a deleted OneDrive will be available for 123 days (30 days active, 93 days in recycle bin).

Restoration steps:

First you need to verify that the deleted User’s OneDrive still exist in recycle bin, for that you need to check that using below PowerShell

If you do not the url of deleted OneDrive then run below command which can return list of all deleted OneDrives.

OneDrive all deleted powershell

If you know the url then pass Url to Get -SPODeletedSite cmdlet which can confirm the site availability.

Get deleted OneDrive

If the site is available in recycle bin, then you can follow below URL to restore it. The restoration process is very simple, just pass the OneDrive Url to Resotre-SPODeletedSite cmdlt.

Restore deleted Onedrive

You can also get an error message “Unable to find the deleted site”, there is a possibility that you have added ‘/’ at the end, just remove it and then restore OneDrive.

Restore deleted OneDrive error

Now the OneDrive is restored to an active state.

Know the Importance of SQL Server Integrity Check

Do you have an environment having multiple databases on the individual server? Unfortunately, all of a sudden the integrity check failed on any of your databases? Do you want to know the process to overcome such circumstance on the basis of the priority level? In this write-up, we will focus exclusively on the best practices while implementing SQL Server Integrity check.  Well, DBCC ensures to report any integrity issues that are related to the database. Moreover, the issues are then further noted by the database owner or the system administrator.

Let’s follow a list of different environment comprising of varied priority level based on the database.

For High Priority Database

If in case the database holds the most crucial data in the organization, which overall improves the revenue of the organization. Suppose if an organization storing bitcoin trades which loses a trade of 5000 bitcoins due to corruption in the files. In such case, the individual cannot afford the lose of 5000 bitcoins. Unluckily, some of the valuable data were lost after the integrity check failed. However, implementing any preventive measures would have avoided and prevented such drastic failures.

Solution

Under such instance, it is suggested to increase the frequency of the integrity check. This is because, the data cannot be compromised for data loss or data corruption by means of any technical failures. Moreover, an Active-Active environment can be utilized where one will be brought down for maintenance while the other is running and vice versa. To be more precise, one can perform the maintenance of the database by checking the integrity while the other is the database which the application uses. After all, the company cannot afford to lose any row of data with its high-level costs which pays to the organization.

For Medium Priority Database

If the database consists of data in which the priority level is high, but at the same time a small amount of data loss can be managed where the data can be categorized into medium-level priority. This data can be monthly payment wherein almost the same amount is scheduled every month. Even if the data is missed or lost, the organization can credit the payment if the customer fails to make it. Under medium priority level, a small amount of data loss can be afforded as the business can accept the price that is in low range. Therefore, to lower the risk of data loss, before creating the backup of the database, it is wise to run the integrity checker. Hence, keep the databases in full recovery mode with the recent log backups and align the available groups with AlwaysOn model.

Solution

For medium priority databases, try to follow the below listed techniques to check whether CHECKDB can be used without leading to any data failures.

  • With the help of the recent transaction log file, restore the database and verify CHECKDB on another server

  • For all the non-clustered indexes, drop all of them and re-create the same. This will more or less help to fix CHECKDB error

  • Based on the results of integrity check for the tables, one can drop the table and re-create it via backup and restore

For Low Priority Databases

For low priority databases, loss of data by any means is acceptable. This can be data from an ETL process, which can be reloaded in a minimum duration of time. For instance, marketing data that holds the data of new clients where the database stores the valuable clients. For certain companies, the priority for such data can be low, medium or high. However, the recovery plan is the re-load process or in worst case the data loss is acceptable. Moreover, the data can be restored using the old backup process or try to repair and afford the data loss if any.

Solution

While considering the low priority database, one can implement the criteria which we applied for medium-level priority i.e., re-creating non-clustered indexes. Moreover, data loss is been accepted in this priority level of database. Apart from that, there are couple of important points which is to be considered when such problem is encountered. This includes like do not keep the backup in the single location, generate regular restore agenda for the backups which is easy for Active-Active environment. Moreover, only the restore process and executing the CHECKDB command will verify the database validity. Additionally, it is always important to communicate with the end users or the clients whenever any failures occur.

Final Verdict

In this blog, we have described the importance of SQL Server Integrity check. In addition to that, this write-up helps users understand the actions to be carried out based on the priority level of the database.

Add/Remove Administrative Access on a Site Collection

There are three ways to change the administrative access for a site collection.

  • Go to SharePoint Site Collection settings and click on Site Collection Administrators link under Users and Permissions. On this screen you can add/remove a user for assigning or removing site collection administrative access for a user. You can also follow this step for administrative access in SharePoint on-premise site collection.

Site settings administrative access

  • The second option is to give administrative access through SharePoint Admin panel. You must be SharePoint admin access on the office 365 tenant. Just select the site collection and click on the Manage Administrators and you can add/remove users on the popup dialog.

Site collection admin SP Online

  • Third option is to use Powershell to Add/Remove user for the administrative access by using Set-SPOUser cmdlet. This command required the URL of site collection, user login name and token of administrative access which can be true or false for Adding or removing administrative access for a user.

Important: User must have SharePoint tenant Admin access to run the command through SharePoint Admin Panel or PowerShell for any site collection or OneDrive. You need Site collection admin access to add/remove user in a site collection.

SharePoint Post Migration Comparison Report to Verify Migrated content using PowerShell

Being involved in migrations for past couple of years, one of the pain area is to verify the migrated content. There are some amazing migration tools like Sharegate, Metalogix, AvePoint and they provide some very useful reports to verify the content but still I haven’t seen a report which can compare the migrated content in two sites, like which lists are migrated and how much content is migrated. We can check the migrated list names on migration tool. Metalogix provides item count on the Migration console window but still we need a report which can be used for document it properly.

The default solution is to open site content of source and destination sites and compare all the lists and item count manually. To minimize that effort, I have written a PowerShell script for SharePoint online which can generate a very useful report to verify the migrated lists and item count in two sites.

To make it easier for end user, I created a function PostMigrationComparisonReport, which take Source site URL and Destination Site URL as parameter. I did this job for migrating content with same tenant, so I used a single user account. If you are migrating content to separate office 365 site, then you need call the Get-Credential function for destination site separately for destination context. I used the credentials of both source and destination lists.

The script is written to compare content between two sites for SharePoint online, but if your migration source is SharePoint Server environment like SharePoint 2010, SharePoint 2013, SharePoint 2016 or 2019 then you can change the PowerShell script, so it can fetch the list and item count detail from on-premise environment.

Now, you need to call the function PostMigrationComparisonReport and pass the source and destination site URLs as parameters and execute the function.

Or export output as csv file where you can have the excel view.

SharePoint Migration Compare two sites

Major differences between SharePoint Server 2016 and SharePoint Server 2019

Microsoft is working on cloud first approach which means that the SharePoint Server 2019 will have all the SharePoint online features. SharePoint online supports both Modern and Classic UI, where modern experience is shared on SharePoint server 2019 and classic ui experience is shared on SharePoint Server 2016.

Below is a list of difference between two Server versions:

SharePoint Server 2019  SharePoint Server 2016
SharePoint home page A page with modern experience where you can easily find and access SharePoint sites within your organization. Class version shows the default SharePoint home page
Lists and libraries Optimized list and library experience. Copy, move, share, sync commands on tool bar. Pin documents, create and customize views, can easily sort and filter content RSS Feed, Most Popular Items, Connect to Outlook, Connect to Office, Export to Excel and Bulk Checkin are few options listed in ribbon which are available in classic ui
Team Sites Modern Team sites wither modern home page which include modern news, site activity and page comment features You can use default classic page home page or can create one from available page templates or can also customize pagelayout.You can add custom or list view web parts on the page.
Branding Can use site themes and Modern UI pages for a custom look n feel. No customization through css and master pages Can use site themes, alternate css and master pages for custom branding.
Communication Site/Portal New Communication site template introduced in SharePoint 2019 which are similar in purpose of a publishing site but do not relay on site publishing feature. Communication portal is usually a SharePoint publishing site which require a master page and site publishing template.
Web parts In modern site pages, you can only add modern ui web parts which can easily configure. They cover most of the modern web components. Old classic web parts cannot be added on modern pages A rich set of web pats is available which can be added on site pages and list views. Easily customizable and can add custom web parts using client side or server side web parts.
Pages Fast and responsive pages, support modern web parts Normal html pages which do not support modern web browsing, only support classic web parts.
Wiki pages Not available in modern ui pages Available
Microsoft Flow Available Not available
Workflow Available Available
Search Modern Search Experience Class search experience

Adding a Column to list Index using PowerShell and UI

Adding a column to a SharePoint list is pretty simple job, you need Edit permission on the list to update the list column index and also item count in list must be less than 20000 as you can not add a field to list index if item count is over 20K.

You can add upto 20 index columns in list. For adding a index column using interface you have to go to list settings and click on Indexed columns, it will take you to the indexed column page.

SharePoint add list indexed columns

Click on Create a new index link and select the column shown in drop down. You can have index column by selecting the dropdown link. You can see few of the column from your list/library are not showing in the list because there are few column types which are supported, and few are not supported. Check this link for details on supported column types.

Adding Index column using PowerShell

I have created a simple PowerShell script which can be used to add/remove list, you can download the script from TechNet.

In below CSOM script written in PowerShell, you can see that after loading the list, I am getting the Documents and then fetching the Title field and then updating the field index value to true.

SharePoint list indexed columns

In above screenshot, you can see the title field created using powershell script. yo

SQL Server Performance Issues and Solutions: An Ultimate Guide

SQL Server is the most important application of Windows system like any other program. It plays a vital role with cooperative scheduler and helps to share CPU and other resources. It can handle and execute multiple tasks simultaneously that means SQL Server lets take its turn to the host. While executing queries and running SQL environment on a regular basis SQL Servers might face performance issues. Some of the issues are bad queries, slow processing, indexes with more writes and reads, memory or CPU issues. Therefore, in the upcoming section, we are going to discuss SQL Server performance issues and its solution.

Common SQL Server Performance Issues & Solutions:

Here, we are going to discuss the following SQL Performance issues and how to get rid of them. Let’s have a look:

Issue 1: Query Taking Long time than usual processing
This issue may occur due to resource contention from locking. The issue arises when a new process is getting added to your computer system, or the system load gets increased.

You can resolve the issue by fixing this problem via Query Analyzer and SP_WHO2 command. You can check BlkBY field in the results of a command which shows offending SPID numbers as a result if they are already blocked. You can follow a large chain of blocks to find out head. You can also use DBCC inputbuffer command to display SQL statement that SPID is running. To do this, you might be able to find out the cause and resolution of this problem.

Issue 2: Database may face out of space error
It happened several times you receive messages that the database is running out of space. There might be two reasons behind the occurrence of this issue, firstly, physical drive that stores the database, or the transaction log file out of space. To fix this issue, test the disk for any file that is growing rapidly or some other issues. Secondly, database or the transaction log file contains maximum size. To solve this case, you need to increase the limit to permit the database operation. The other reason that takes place when tempdb log is full.

To overcome this problem, restart MS SQL service and it will recreate the tempdb from scratch. Keep in mind that tempdb file has a maximum size and increase the file size if necessary to avoid this kind of issue.

Issue 3: Client loses connection to the SQL Server
SQL Server connection issue is not very common but, if it occurs, then it usually blocks the SPID to hold a lock on the resource while waiting for timeout. This is the major issue in SQL client running the previous version of Microsoft Access.

To troubleshoot this problem, use SP_WHO2 and KILL commands to delete the orphaned process from SQL server. Another way to fix this issue is that decrease client connection timeout, this will lead server to kill the orphaned processes quickly and easily.

Issue 4: Complete system goes to a Deadlock state
If you are getting the error message that says “you are in deadlock state” and the system that suffered from deadlocking. So, the system is entering in a deadlock state when two or more queries in SQL, each locking resources and none of them is finished. When deadlock state issue gets resolved SQL log file will report the situation. Moreover, SQL server log is the best place to verify whether the issue has been resolved or not

One of the simplest methods to solve problem is turning ON trace flag-t1204, it will provide the complete information in the log. You will have to determine the contention level on the server.

Time to Conclude

SQL Server performance and database consistency checks are important to keep the server always up and run smoothly. Users should always manage regular backups, check SQL services and monitor resources. If users might face issues such as corrupt MDF/ NDF files or SQL database files are inaccessible, it is always recommended to use a commercial solution, i.e. SQL Repair Tool. It is designed in such a way to repair corrupt database of SQL server without facing any difficulty.