How to Restore Deleted SQL Database from a Backup / Online File
In this write up, you can get complete information about how to recover deleted SQL database from backup & online database files. Restoring DELETED database is something that you would like to ignore, but it doesn’t matter how careful you are, it may happen any time. Having usual complete database & transaction log backups is a crucial practice & a good advantage in the condition, but then getting back the deleted database may not be as simple as it sounds.
The good scenario is that the database are deleted directly after taking the last backup (transaction log file or full), with no changes happen after that. You can get the records downtime & restore the backup. As such situations almost never occur in actual life, these types of movement will overwrite the entire transactions that are done after the deletion of database, and the downtime situation that users face a long with all deleted database.
Now the question arises that do you have backup of database which have all records that you need to retrieve? If yes then you can try the SQL Server management studio & export data function –
- Get complete backup of database & retrieve it on SQL Server.
- If you are retrieving it to same SQL Server instance, take care that you provide a new name & path for log files & data.
- In the recovered database, search the deleted records. It is simple if they all have been deleted from a table. But if the database were deleted from lots of tables and also fulfil particular requirements, it will be relatively challenging. This step is not mandatory, but it will assist to retrieve the database if you know what you are searching for.
- Script the database, right click on the database in object explorer & choose tasks >> Export data.
- Check the retrieved database as a data source & original one as a destination.
- Hit on next button & display a dialog box –
Even if the function write a query to specify the data to transfer shows more potential in a condition when the database from multiple tables are deleted by a particular query to sort them, it will not add the chosen database in corresponding tables. Entire database will be added into a single table. This function doesn’t allow lots of result sets & only the first outcome set is added.
If you can go for this function –
- Identify the chosen statement that returns the deleted database.
- Check the destination table. By default, a new table dbo.query is set, therefore you have to modify it to match the source table.
- Click on Edit Mappings button. And then you can –
- Confirm whether to drop & recreate the destination table. This function is disabled when the dependency will be broken.
- Confirm whether to enable identity add in the destination table. It is recommended to keep away from failure due to adding into a read-only column.
- Identify whether to truncate the current destination table.
4. Click on OK button & complete the wizard process. After the database is added, you have to again do the steps a. to d. for entire tables with deleted database.
Another accessible process in SQL Server Management Studio Export data wizard is to copy data from one or more views & tables. You can provide multiple tables at once but it is not probable to filter database.
a. Choose the table from which the database has been lost.
b. Verify the mapping for each table
c. Go with wizard. After that database are added, if the destination tables are not blank, you may get an error message such as –
The disadvantage of the above mentioned method is that for lots of tables, itsnot simple to extract database that you need to add and to successfully add them into active tables. This issue can arise by breaking primary & foreign keys, adding duplicate data, etc. The database tables with restriction cannot be recreated & dropped therefore you cannot resolve some of issues this way.
Try another Solution
To know about how to restore deleted database in SQL Server flawlessly, you can go with SQL Database Recovery tool that can scan & recover deleted SQL database from server and provide export option to save recovered database in SQL server without misplacing any data or details. To clear all your doubts, you can also download & scan, recover all deleted SQL files (also provide a complete preview of recovered SQL data) and export only 25 items in SQL Server without any charges or cost. Therefore, try any above mentioned solution and solve all issues about how to restore deleted SQL database.
