Best Ways to Export SQL Database to Another Server
Do you want to know how to export SQL database to another server? Then, keep reading!
Whether you are updating or migrating to a different server or a cloud, this article is for you. In this weblog you will learn the following methods to copy or migrate SQL server data to another server:
– Backup & Restore Method (SSMS)
– Copy SQL database to different server (SSMS)
– Using SysTools SQL Server Database Migration Tool
Method 1: Move SQL Server Database Using Backup & Restore Facility of SQL Server Management Studio
This process will take place in two section, first we have to backup the SQL data & secondly restore it on another server using the SSMS only.
Part1: Backing up the SQL Server Databases
Step-1: In the SSMS, go to “Object Explorer”.
Step-2: Click “Database” and select the database file you want to export by using Right-click.
Step-3: Then, go to “Tasks” >> “Back Up”.
Step-4: “Backup Database” dialogue box will open. Make sure to ‘set the Back type’ as Full & ‘Destination’ as Disk.
Step-5: Click on “Add” to state your backup destination.
Step-6: Click on “OK”.
Now we will have to restore the backup that we created.
Part 2: Follow These Steps to Restore the Backup on Your New Server or System
Step-1: Open SQL Server Management Studio and connect to your database.
Step-2: Choose your database by right-clicking on it and then, go to “Tasks” >> “Restore” >> “Database”.
Step-3: ‘Restore Database’ dialogue box will appear.
Step-4: Using the ‘Source’ section to specify the source and location of the backup, on the general page. Select ‘Device’ >> click on the (…) to choose your backup file.
Step-5: In ‘Specify Backup’ window, set ‘Back media’ as File.
Step-6: After that, click on “Add” and pick your backup file. Click on “OK”.
Step-7: On ‘Restore Database’ wizard, under “Select a page panel”, click on options.
Step-8: Specify the ‘Restore options’ and ‘Recovery state’ as per your need and click on “OK”.
Method 2: Copy SQL Server Database on a Different Server by Copy Wizard
This is a lot simpler than making backup and restore the SQL server data.
Follow These Steps to Export SQL Database to Another Server by Using Copy Method:
Step-1: Go to ‘Object Explorer’ in SSMS.
Step-2: Select your database file using right-click, then, go on ‘Task’ >> ‘Copy Database’.
Step-3: Specify the name of the source server. Do the authentication process and hit on ‘Next’.
Step-4: Enter the Destination server name and do the authentication.
Step-5: Select ‘Use SQL Management Object Method’ and click ‘Next’.
Step-6: Put check on the ‘Copy’ box, click ‘Next’.
Step-7: Check your MDF and LDF files path for the destination server.
Step-8: Press ‘Run Immediately’.
Step-9: Click on Finish and your database will be copied.
After performing the above steps, refresh the destination server to view your copied database.
Points to Remember:
– Above methods can be time taking.
– As both methods are done using the SSMS which is a highly expensive software, so its a quite high-cost deal.
Export SQL Database to Another Server Using SysTools Migrator For SQL
The advantages of using this software:
– The software is super affordable, it will cost you almost 23x less than SSMS.
– It also helps you to repair damaged MDF file .
– It will let you export selected SQL objects and database.
– You can choose to export schema and records or only schema.
Free Edition Will Export 25 Items
Steps to Export SQL Database to Another Server Using The SysTools Software:
Step-1: You can download the free edition from above and launch it in your system.
Step-2: Click on ‘Open’ (given on the top-left side) to load your database files.
Step-3: After adding the database file, choose between ‘Online mode’ (exporting data from live SQL server) or ‘Offline mode’ (exporting data from offline file). Fill in the details.
Step-4: After scanning, the tool will let preview the database objects. Click on “Export”.
Step-5: In this step, you have to select the export option. Since you want to export your SQL database to another server, choose “SQL Server Database”. There are other that you can choose according to your needs. Lastly, click on “Export”.
And we are done here!
Note: You can also use SQL commands for backing up of a SQL server database and restore it on a different server, but you have to mount and dismount the data accordingly. Also, this method (SQL commands) does not guarantee full database transfer.