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

SQL Saturday 589 Interview

Tweet SQL Saturday 589 with Power BI

Switch to our mobile site