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:

DBCC CHECKDB  ([sales]
, REPAIR_REBUILD)

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:

USE master;
GO
ALTER DATABASE sales
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

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:

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:

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:

• Database Files and Filegroups
• Database States
• File states
• Database Detach and Attach (SQL Server)