How to Backup and Restore SQL Database from One Server to Another?

Many organizations use SQL Database Server for managing their organization’s activities and it is one of the largest database management systems. But it is important to take frequent backups of your database in order to prevent data loss. Another possible reason for SQL Backup and Restore can be upgrading the version of your SQL Server and moving the database from one server to another. Here you can see how to backup and Restore SQL database from one server to another through SSMS. The SQL Server Database Repair Tool can be a good option if you face any corruption issues or errors while restoring SQL Database.

  Download     Buy Now

Need to Backup SQL Database

There are many possible reasons why an individual needs to backup SQL Database and Restore it to another server.

• When you upgrade the current version of your SQL Server to the Latest Version.
• When you want your database to be available for operations at remote branches.
• The consistency of the Database is tested across different SQL servers.
• When you configure database mirroring forte database.

Methods to Backup and Restore SQL Database from One Server to Another

Before you start the backup and restoring process, make sure you install SQL Server Management Studio (SMSS) and SQL server on your laptop or PC. Firstly, you must see how to backup SQL server database and then the steps to restore SQL Server Database from one server to another.

Steps to Backup SQL Server Database

• Launch SQL Server Management Studio and connect to the old SQL Server instance
• After connecting to the server, from the Object Explorer pane expand Databases.
  

• Then make right-click on the database you want to backup and choose Backup after expanding Tasks.

• The Backup Database window will open where you will have to choose the Backup type: as Full and then click on the OK button.
  

• The backup of database ‘Test’ completed successfully message box will appear where you will have to click on OK.
  

• Then you will have to open the location where the database backup is stored.
• The default location of the backup directory is C:\Program Files\Microsoft SQL Server\MSSQL. n\MSSQL\Backup.
  

• After locating the backup file, make right-click and go to Properties.

• Then in the Properties, go to the Security tab and click on the Advanced button.

• You will have to change the permission configuration in the Advanced Security Settings dialog box and then click on the Add button.
  

• Change the permission to everyone and you can do this by:-
  • Click on the Select a Principal option.
  • A new dialog box will open Select User or Group.
  • Enter Everyone in this dialog box under Enter the object name to select section.
  • Check the Names and hit OK.

• You will have to Give full control to users to access the file and then click on the OK button.

• Now, Click on Apply and then click OK.
• In the end, open SQL Server Management Studio (SSMS) again and take the database you have currently backed up offline.

Steps to Restore Database from One Server to Another SQL Server

After creating the backup of the SQL Database, you can restore it to another server. Here you can explore the steps to restore the database backup of SQL Server 2008 R2 to another SQL Server that is 2016. You are free to choose any SQL Version for Database backup and restore.

• Launch SQL Server Management Studio and connect to another SQL Server instance from where the backup database copy has to be restored from the old server.
• Then you will have to create a database with the same name that you are willing to restore.
• Now open the backup file location and copy the .bak file.

• Paste the copied backup file in a temp folder onto Windows Server 2012.

• Make a right-click on the Test database and in the Tasks go to Restore and then on Database.

• The Restore Database window will open where you will have to click on the button with 3 dots.
• Then click on the Add button in the Select backup devices dialog box.

• Select the backup file that you want to restore in the Locate Backup File dialog box, then click on OK.
• Click again on the OK button.
  
• Now the .bak file will be added to the Restore Database window.

• Click on the OK button and you can see the message Database Restored Successfully.

The above steps will backup and restore SQL Database from one server to another.

What to Do If the Above Method Fails? Copy SQL Backup Files to Another Server

The above steps will restore the SQL Database but if it won’t work then you might check the corruption issue in your SQL Server. You must run the command DBCC CHECKDB (‘Test’) with NO)INFOMSGS, ALL_ERRORMSGS; to check if your SQL database is corrupted or not. If it shows consistent errors then your SQL database is might be corrupted. You can try the SQL Database Recovery Tool to repair the SQL Database and then try to backup and restore SQL Database from one server to another. Moreover, this tool can also fix all types of SQL errors like SQL Server Error 15105.

Conclusion

The steps are easy to follow for backing up and restoring the SQL Database from one SQL Server to another but if your SQL database is corrupted then you cannot implement the above steps. First, you will have to repair the SQL database and then only you can backup and restore it from one server to another.

Related Post