Resolved: Database ‘msdb’ Can not be Opened. It Has Been Marked Suspect by Recovery.

User Query 1: This is my first post here and I am in a bit of trouble I guess. Yesterday, when I attempted to access the database from Visual Studio to work on my SQL Server, I got a message that says Database ‘msdb’ cannot be opened. It has been marked SUSPECT by recovery. Error: 926.” I have no clue why this error is appearing. This is literally stopping me from accessing the database. I use the 2008 version of SQL Server. Please tell me how to get rid of this error message.

User Query 2: “I am a user of SQL Server 2008 R2 on 64-bit Windows 7 system. Currently, I am unable to find any of the several databases in object explorer. Though the new query window appears and lets me perform queries, those are not visible to me. During database exploration, I get this error message: “Database ‘msdb’ cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. (Microsoft SQL Server, Error: 926).”I have tried restarting the database and similar commonly used tricks. But none of them worked. What should be my next step to fix this annoying problem?

SQL Server 2008 R2 is quite prone to numerous errors. One of them is Database ‘msdb’ Cannot be Opened. Error 926.” Issues like offline device, dirty shutdown, database corruption, unavailable database file can cause this error and it prevents users from accessing any database object. Do not worry, as we will be offering some of the reliable solutions to this error. Continue reading this post to get the detailed procedure of the techniques.

Three Trusted Solutions to Fix Database ‘msdb’ Cannot be Opened Error

If you are one of the unlucky people who are receiving this error, try these techniques. Here, we will be presenting three different methods that are often used to resolve this issue. Users can take the help of any one or all of these methods.

1. Run DBCC CHCKDB Command

a. Open a new query window.

b. Run “EXEC sp_resetstatus ‘DB_Name’” command. Here, ‘sp_resetstatus’ will reset the suspect status of that particular database. In sys.databases, the status column of the database will get updated. Remember, you will be able to perform this command only if you logged in with system admin privileges.

c. Now run “ALTER DATABASE DB_Name SET EMERGENCY.” The database will be switched to emergency mode, which is actually the READ_ONLY copy. Only sysadmin members with designated server roles can access it.

d. Run the script “DBCC checkdb(‘DB_Name’)” for integrity verification among all database objects.

e. Turn the database in the single user mode by ALTER DATABASE DB_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE command.

f. Now, you can repair the data errors and corruptions by running DBCC CheckDB (‘DB_Name’, REPAIR_ALLOW_DATA_LOSS)

g. Set the database back in multi-user mode by “ALTER DATABASE DB_Name SET MULTI_USERDetails”.

2. Stop and Restart the Process

a. Open Object Explorer and select the connection item. Right-click on it and select Stop Object Explorer option.

b. Go to Control Panel>> Administrative Tools>> Services Control Panel>> Administrative Tools>> Services.

c. From the list of services, Select SQL Server MSSQLSERVER to right-click on it. Choose Stop option.

d. Navigate to this location:

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

e. Transfer MSDBData.mdf & MSDBlog.ldf files to some other location.

f. Now, copy those files from the new location and paste in the old location.

g. Open Object Explorer and right-click to select Start option.

h. Refresh your database and detach the MSDB file.

3. Remove Corruption using SQL Recovery Tool

Ratherthan trying these manual tricks, it is better to fix all thecorruption issues of database by using SQL Recovery Tool. This application is capable of removing all types of corruptions, thanks to its Advance scanning mode. This utility will work perfectly on all the latest versions of SQL Server including SQL Server 2019. Here is how you can run this application to resolve this problem:

a. Start the tool and add the MDF file.

b. Select Advance scan and enter the server version.

c. Preview all the database components.

d. Save the corruption-free MDF file.

Final Words

Database ‘msdb’ Cannot be Opened. It Has Been Marked Suspect by Recovery is a common error message that can significantly harm the day to day activity of SQL Server users. Here, we have discussed various techniques to remove this problem for good. We expect that users will get benefited by following the instructional guide provided here.

Get to Know How to Reset SA Password in SQL Server 2017

It is a cumbersome situation for an Organization when SQL Server database engine loss access as a system administrator. And, there is no relevant solution in hand on how to reset SA password in SQL Server 2017. Because if you do not regain access to password means you cannot use database anymore. Apart from this, there are plenty of other factors due to which users need to change SA password in SQL Server.

1. Mistakenly removed all logins that devised for sysadmin fixed server role.

2. It might be possible that SA account gets disabled and no member knows its associated password.

3. This is the major reason where user who login as system administrator left the organization and no one knows the SA password.

4. Windows admin user has not been permitted for sysadmin role Microsoft SQL Server 2017.

5. When the set password is complex that is hard to remember and user forgets it.

So, after considering all these reasons, we have come up with this article. In this write-up, we will cover all the solutions on how to reset sa password in SQL Server 2017.

Techniques to Regain Access to System Admin Password in SQL Server

1. The first workaround is to again install the SQL Server and connect all existing databases to new instance. However, it is a time-taking procedure. Also, in order to restore the logins, recent copy of master database is required. Also, it should possess the similar logins as last instance. Else, administrator will not get the access and still considered as locked out.

2. The second technique to reset sa password in SQL Server 2017.

3. Use an outstanding utility entitled as SQL Password Recovery. In few mouse clicks, it can reset SA password in SQL Server Management Studio.

4. Now, check out each method in a detailed manner.

How to Reset SA Password in SQL Server 2017 – Manual Way

In the following section, readers will know how to use sqlcmd to change the sa password when lost or forget.

1. To initiate the process, launch SQL Server Management Studio.

2. Next, click on Registered Servers under the View menu.

3. Now, you need to right-click on the server and select SQL Server Configuration Manager. It prompts you and asks to run as administrator in order to start Configuration Manager application.

4. Once the above steps executed, close the SQL Server Management Studio.

5. From the SQL Server Configuration Manager, stop the SQL Server Services that includes MSSQLSERVER. To do this, just right-click on the SQL Server instance and press Stop button. Then, start the Command Prompt.

6. Use the below command to run the SQL Server in single-user mode.

net start MSSQLSERVER /m “SQLCMD” 

7. Now, you need to add the database on system with the help of reliable solution.

sqlcmd -E -S localhost

8. Once the sqlcmd starts, then run the following command.

Create Login TempLogin WITH PASSWORD - ‘*********’
GO
ALTER SERVER ROLE sysadmin ADD MEMBER TempLogin
GO

9. In order to close SQLCMD, type ‘exit’ in cmd.

10. Now, again start the SQL SERVER service in order to remove the single-user mode.

net stop MSSQLSERVER 

11. Once the above command executed, run the below one.

net start MSSQLSERVER

12. Then, again start the SQL Server Management Studio and use the new login to connect the local database.

13. After that, navigate to the Security option from left sidebar and click on that to expand it. Thereafter, open the Logins folder.

14. Next, right click on sa and select Properties from the menu.

15. At last, type the new password and click on OK.

Easy & Automatic Way to Reset SA Password in SQL Server 2017

It is evident from above that user has to follow such a long procedure to regain the access of SQL Server as an administrator. So, to overcome this situation, an automated tool named as SQL Password Recovery Tool is developed. It gives you power to reset system admin as well as user password from the master database file. The best part is the assistance of SQL SERVER is not required for the functioning of software. Apart from this, the user can also set a new password for SQL Server 2017 or below versions.

So, it is a cost-worthy solution that will work when SA password is forgotten, lost, or locked by the user.

The Bottom Line

Through this write-up, we are trying to resolve the most searched query ‘how to reset SA password in SQL Server 2017’. Now, it depends on the users, which solution they choose to change the system administrator password. In case, when the users do not want to take the risk with their sensitive database, SQL Password Recovery is always there for help.

Recovering SQL Server Database Without Backup – Instant Solution

problem:

“I dont have backup of my database and my mdf & ldf file get corrupted, Is it possible to restore database to previous state?”

“My .bak file get corrupted by accidental shutdown of SQL Server Database and I dont have backup. How to recover SQL Server Database without backup?” Please help.”

I have repeatedly came across these questions, many users and DBA faces that there is corruption in their mdf as well ndf file, and they have no backup .

Microsoft SQL Server is widely used as database management system which stores its data in MDF, NDF, & LDF according to Database file format. And the backup act as the protection of these files on which whole business is dependent. Taking regular backup is the key to recover your database from corrupted state. But What happens if your database files get corrupted? Let us imagine more worse, What if you don’t have backup of those files ?

Disaster is something which can occur at any time. It could be occur accidentally or could occur itself. Hardware failure, Failed disk drivers, network intrusion, media failure are some of the common reason of corruption in SQL Server. So proper disaster recovery plan is important to prevent these kind of situation.

Let us discuss the solution, What you can do if corruption persists in SQL Server Database and you are at worst situation,ie, you don’t have backup of your SQL Database.

What to do If Corruption Persists in SQL Database and you don’t have Backup ?

So if you are in worse situation, this blog is for you!

If you dont have backup of your SQL Database and your database becomes corrupted, you can recover your corrupted data manually by running DBCC CheckDB command.

Or if the corruption persists in database tables, then you can check this by running following command:

DBCC CHECKTABLE (‘tablename’)

Repairing Corrupted Database:

Repair your database by running REPAIR_ALLOW_DATA_LOSS and REPAIR_FAST.

Warning: Prefer REPAIR_ALLOW_DATA_LOSS as a last option if there is no other option. It may lost some amount of data if the database brings back to its physical consistent state. Before performing this command on your corrupted database, user must take a backup copy of its database.(MDF/LDF/NDF)

How to Recover SQL Server Database Without Backup

It is possible that the above repair command will help in repairing corrupted database. But there are chances of data loss if it get fails. So, if the level of corruption in SQL Database file is severe, then there is another option to repair corrupted database that is SQL Server Recovery Tool.

Quickly Recover SQL Server Database without backup

SQL Database Recovery Tool helps you to recover corrupted database even if you are not having backup. It scans & repairs corrupted SQL database MDF/NDF File of SQL Server 2017, 2016, 2014 & its below version. If you don’t know the version of SQL mdf file, the software provides you feature of Auto Detect option to detect the version SQL mdf file. Further, it gives you two different option to export your recovered data. Either export your data as SQL Server Database or as SQL Server Compatible Script.

Steps to Perform to recover SQL Server Database without backup:

  1. Install and Launch SQL Recovery Tool and Click Open.
  2. Browse to select the location of mdf file.
  3. Click Advance scan and select the version of .mdf file
  4. Preview the data and Click Export. Either Export as SQL Server Database or as SQL Server Compatible Script.

Conclusion:

The blog discusses the solution of users common problems related to database corruption. The blog covers both manual as well professional solution to recover SQL database if you are not having SQL database backup.There might be possibilities of data loss in manual approach.So, it is advised to go for an automated solution to prevent data loss.

SQL Server Page Level Corruption And Resolution

SQL Server Page Level corruption is encountered due to several reasons, which includes hardware related issues, i.e. failure of certain hardware, hard disk functioning failure, improper network establishment, or logical level of issues such as Read more »

My next session at Cloud Conference 2017

Power BI: Data Modelling

Comparison of Features Available Per Tier

Feature/Capability   Power  BI Free Power BI Pro Power BI Premium
Create datasets and reports in Power BI Desktop  x  x  x
Publish datasets and reports to Power BI Service  x  x  x
Publish datasets and reports to Power BI Report Server  x
Create dashboard  x  x  x
(compilation of visuals from one or more reports)  x  x  x
Q&A natural language queries x  x  x
Data alerts  x  x  x
E-mail subscriptions (“subscribe”)  x  x
Export to CSV, Excel  x  x  x
Export to PowerPoint  x  x  x
Analyze in Excel (connectivity to a published dataset from Excel for creating reports based on an existing dataset)  x  x
Power BI Service Live Connection (connectivity to a published dataset from Power BI Desktop for creating reports based on an existing dataset-requires )  x  x
Access to all data sources (unrestricted)  x  x  x
Scheduled data refresh via On-Premises Gateway in Personal Mode Up to 8x/day On-Premises
Data Gateway Recommended
Scheduled data refresh via On-Premises Data Gateway Up to 8x/day Up to 8x/day Up to 48x/day
DirectQuery and Analysis Services Live Connection via On-Premises Data Gateway  x  x  x
Use of streaming datasets  x  x  x
Use of custom visuals from Office Store  x  x  x
Sharing read-only dashboard with individual colleagues (peer-to-peer)  x  x
Sharing read-only dashboard with an external user  x  x
Create and publish Apps  x  x
Create and publish Organizational Content Packs  x  x
Publish to Web (public report distribution)  x  x  x
Power BI Mobile Apps  x  x  x
Cortana/Windows integration for report rendering  x  x  x
Maximum size of an imported dataset (*=future size increases over time) 1GB per file 1GB per file 1GB per file*
Maximum data storage quota 10GB per user 10GB per user 100TB per capacity
Multi-tenant service  x  x
Isolated capacity  x
 x
Unlimited # of consumers (view-only users) of Apps  x
Dedicated hardware capacity with scalability on-demand  x
Use of Power BI Report Server for on-premises deployment scenarios  x
APIs for embedding reports in an internal custom application  x  x
Power BI Embedded for embedding reports in an external custom application  x
Third party SaaS Apps (from AppSource)  x  x  x
Share queries in Azure Data Catalog  x  x
Integration with Office 365 Groups  x  x
Integration with Azure Active Directory  x  x  x

Power BI and business application platform

Creation translation system of labels with Power BI

In an Power BI Desktop data models, you can’t do multiple translations of a caption to provide a culture-specific. Today we will see For how perform translations Power BI models.
A translation consists of a translated caption for the name of fields in the table a binding to a column that provides data values in the target language. You can have multiple translations. There is no theoretical limit on the number of translations you can embed in model.
To perform this using:

In this post we get data from three csv file: product, sales territory  and sales from AdventureWorks. We will create a model in the file model.xlsx and we will create the dictionary in the Dictionary.xlsx. See below the file in the folder

So, we have a series of label, and want to look them up in this table (lookup table) in the dictionary.xlsx with two columns one for it-IT label and one for en-US label, see below

Now we will import into Power Query into model.xlsx file then territory’s lookup table

Now we will import into Power Query into model.xlsx file then product’s table from SalesTerritory.csv file. The objective is change the label with a correct translation for examples SalesTerritoryRegion change in Region or in Italian language “Regione“. After importing it we will can observed in power query the following structure:

Now we will create a query for perform the translation. The M formulas is:

let
    Source = Csv.Document(File.Contents(PathFile & "\SalesTerritory.csv"),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Removed Columns2" = Table.RemoveColumns(Source,{"Column2", "Column6"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"Column3", "Column2"}, {"Column4", "Column3"}, {"Column5", "Column4"}}),
    #"Kept First Rows" = Table.FirstN(#"Renamed Columns",1),
    #"Removed Top Rows" = Table.Skip(#"Renamed Columns",1),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Top Rows",{{"Column1", type number}}),
    #"Transposed Table" = Table.Transpose(#"Kept First Rows"),
    #"Invoked Custom Function" = Table.AddColumn(#"Transposed Table", "lkpLabel", each lkpLabel([Column1], lbTerritory, 2, false)),
    #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"Column1"}),
    #"Transposed Table1" = Table.Transpose(#"Removed Columns"),
    #"Table Combine" = Table.Combine({#"Transposed Table1",#"Removed Top Rows"}),
    #"Promoted Headers" = Table.PromoteHeaders( #"Table Combine", [PromoteAllScalars=true])
in
    #"Promoted Headers"

Begin analyzing the formula from row 4 and 5. In this two row set two variables “Kept First Rows” e “Removed Top Rows”  the first contains the first line where there are labels that I have to replace

the second contains the all line that they will need later

The First result (“Kept First Rows“) is now ready to be transposed using the Table.Transpose() function

Here is the key step where the call the lkpLabel  function for create a colum with the new translate label. This function use 4 parameters:

    #"Invoked Custom Function" = Table.AddColumn(#"Transposed Table", "lkpLabel", each lkpLabel([Column1], lbTerritory, 2, false))
  • Lookup_value  => the value of the column which must be converted ([Column1])
  • Table array => table array is the lookup table (lbTerritory)
  • column = >number of column for lookup
  • approximate_match => it’s optional

and see bleow the new column (lkpLabel):

Once we got the new value, transpose the table again to restore the headers as a row


So now remains to connect the new header with the body of the table that contains all rows and that we previously saved to a variable “Removed Top Rows” . The table with the single header row needs to be combined with the original table with Table.Combine function

    #"Table Combine" = Table.Combine({#"Transposed Table1",#"Removed Top Rows"})

Once the tables are combined, you can see the altered headers to promote the first row to headers using the Table.PromoteHeaders() function.

   #"Promoted Headers" = Table.PromoteHeaders( #"Table Combine", [PromoteAllScalars=true])


you can download the folder with all of the demo file from this link.

Optimizing DAX

Switch to our mobile site