Summary- In this write-up, you will learn how to
recover deleted database views in SQL Server. I will cover two methods in
detail. In brief, the first method is a manual method that will use Microsoft's
Server Management Studio. This method, however, has many limitations. To overcome
them, we will use an automated tool that will recover your deleted objects in a
few clicks.
Even after following
all the precautions, the database and it's objects are still liable to accidental
corruption or loss. Database administrators the world over have to deal with
this issue daily. Thankfully, we have a way out this mess.
Read on to learn
about the best way to restore deleted views in the SQL server.
Why do Views Get Deleted At All-
·
Human
Error- Most often it is
due to a human error. You are working on your database and by mistake, you run
a command that you shouldn't have and you end up deleting a view.
·
Power
Loss- During an occasional
outage your database might've gotten corrupted and now all your database
objects (like views) stand altered or even completely deleted.
·
Virus- In the 21st century, viruses are always a
threat for everyone, and more so for someone working in tech. One such threat
is the infamous virus Ransomware which attacks your database and all its files,
making them corrupted or altered and sometimes even deleting them.
·
Pro
Tip- Use the SysToolsSQL Repair Tool and recover your deleted SQL Server views in a few clicks.
How to Recover Deleted Views in SQL Server-
There are two ways
that you can go about to restore your deleted views in SQL. I will explain both
of them in a step-by-step way. In brief, they are-
1
Backup
and Restore Wizard in SSMS
2
Enterprise
Recovery Tool
Backup and Restore Wizard in SSMS-
NOTE: This method will only work if you already
have a backup of your database in place. If you don't have the backup, then
proceed to the next method.
Follow the steps
below to restore your deleted views in SQL Server-
1
Launch
SSMS and connect to your database.
2
Right-click
on your database. Click on Tasks and then on Restore > Database.
3
A new
dialogue box Restore Database will appear.
4
Select
From device under Source for restore section. Then click on browse(...)
to select your backup file.
5
Set
Backup media as File in the Specify Backup window.
6
Click
on Add and select your backup file. Click OK.
7
Click
on Options under Select a page panel in the Restore Database window.
8
Set
the Restore options and Recovery state as per your requirement
and click on OK.
Your backup along
with all the objects such as views, functions and stored procedures will be
restored shortly.
Recover Deleted Views in SQL Using Enterprise Software-
The previous method,
i.e., backup and restore using SSMS sometimes works well when you have the
database already backed up in your system. However, if you don't have a backup
then as a database administrators you are in trouble. Or so many database
administrators are told.
The second method
will help you recover any data even if it has not been backed up. This is only
possible by using professional software, like SysTools SQL Repair tool, that
will take care of this process for you.
Follow the steps
below to recover your deleted database objects-
1
Install
and Launch SysTools SQL Repair Tool on your system.
2
Click
on Open to load the database files in the software.
3
Select
the database file (MDF) from the destination location and click on Open to
add the file to the software.
4
Choose
Quick Scan for normal scanning of the database file and Select Advance
Scan for Highly corrupted database files.
5
Check
the Auto Detect option, if you want to detect the SQL Server version of
your MDF file automatically.
6
The
Software will display the scanning process with complete database details.
7
After
Scanning the file, software previews the database Objects (Tables, Functions,
Stored Procedures, Views, etc) created in the database file.
8 Click on Export to move the table's data from one database to another.
The software exports
SQL database in two different ways:
1
As SQL
Server Database
2 As SQL Server Compatible Script
Here will go with
the first option, i.e., Export it as SQL Server Database.
1
Enter
the SQL Server Name manually, if the software doesn't show the desired
Server Name and select the Windows Authentication if you are using this
Authentication Mode to access your database.
2
SQL
Server Name and Login Credentials for SQL Server if you use one.
3
Select
Create New Database to export recovered data on it or Choose Export
to Existing Database to Overwrite the recovered database on an Existing
database.
4
Follow
the steps below if you want to create a new database-
·
If you
choose to Create a New Database option, then you need to give the Database
Name to a new database for example- sample_migration, and the software
will create a new database on destination SQL Server.
·
Select
the database objects to move. By default, all objects will be checked to be
moved.
·
You
can export the MDF file as: With Schema or Without Schema.
·
If you
save the database objects (views, functions, procedures, etc) With Schema,
then the software will export the schema/structure of the selected database
components.
·
Click
on OK after a successful Export.
·
Click
on Close. The software will now ask whether to export the details of
exported items in a .csv file. Click yes to save the report.
·
Browse
the folder to save .csv detail in the desired location or software allows you to
Make New Folder save the .csv detail in it.
·
Open
the exported .csv file to view exported items.
·
Run
SQL Server again and expand the databases to check newly migrated the database.
· Check all the migrated database objects.
Follow the steps
below if you want to export in an existing database-
·
Check
the Export to Existing Database option to export files to an existing
database.
·
Here
we have created a new database named as EMPTY to show how to migrate
database objects in an existing database.
·
Now
for example- migrate a single table dbo.productdetail from the
SAMPLE database to the EMPTY database.
·
Checking
the available records in dbo.productdetail by executing a select
statement.
·
In
database name, select the destination database to migrate selected
database objects from the SAMPLE database.
·
Now
select dbo.productdetail for migration.
·
Choose
to migrate data With Schema or Without Schema.
·
The
software will ask for Export Deleted Records as well. Click on Yes, if
you want.
·
The
Export completed popup will prompt on the software screen once the export the process gets completed successfully.
·
Click
on OK and the software will display the exported database object with
record count and Status.
·
By
Clicking on Close, SQL Server Database Migrator asks for export the
details of exported items in .csv file. Click on Yes to save the report.
·
Browse
the folder to save .csv detail in the desired location or software allows you to
Make New Folder save the .csv detail in it.
In this way, you can
recover your deleted views in SQL Server. Another benefit of this professional
software is that it doesn't just help you restore your database and its varying
objects like views, procedures, functions, etc., but also takes care of the
following:
·
Migrate
even Corrupted MDF and NDF files to the target location.
·
It
allows users to move database objects of an offline SQL Server database file
from one Server to another Server with the only schema.
·
It can
also, convert your MDF files to SQL files.
·
It
gives you the option to transfer multiple databases.
·
You
can also copy databases with schema or without schema.
Conclusion-
And that's it.
As you saw, database
errors like deletion and corruption are very common and are faced by database
administrators daily. The reason for these hiccups includes, but are not limited
to, human error, power outages, etc. In case of a deleted view, we have two
methods at our disposal to recover it.
You can use the
manual backup and restore method in SSMS, provided you have the backup. In
case, however, if you don't have the backup, or if your backup has been
corrupted, you can use an enterprise tool like SysTools SQL Tool and
restore your deleted views. If you have any other takes on how to recover
deleted views in SQL Server, then leave them in the comment section below.
0 Comments