How to Copy MSSQL Database | Two Methods

What is Microsoft SQL database

Microsoft SQL Server is a relational database management system developed by Microsoft. It is a software product used to store and manage large amounts of data in a structured manner. SQL Server uses the Structured Query Language (SQL) to manage and manipulate data stored in relational database tables.

SQL Server supports a range of features such as data warehousing, business intelligence, and analytics, making it suitable for a wide range of applications. It also includes tools for managing security, backups, and disaster recovery, and supports a variety of programming languages such as C#, Java, and Python.

Why you might want to copy the database

There are several reasons why you might want to copy a database in Microsoft SQL Server. Here are some common scenarios where copying a database can be beneficial:

• Development and Testing: It allows you to experiment, troubleshoot, and test code changes without risking data integrity or disrupting ongoing operations.
• Reporting and Analytics: By creating a separate copy of the production database, you can run resource-intensive queries and generate reports without impacting the performance of the live system.
• Backup and Disaster Recovery: By regularly MSSQL copy database to another server or storage location, you can have a redundant copy of the data that can be used for restoration purposes in case of disasters.
• Data Migration and Upgrades: Copying a database can be part of a data migration or upgrade process, which allows you to ensure a smooth transition while minimizing the downtime and risks associated with the migration.

Proper planning and testing are essential to ensure a seamless copying process and to avoid any adverse impact on business operations.

How to copy MSSQL database to another server

MSSQL copy database to another server can be accomplished using various methods. Here, this article will outline two commonly used methods for you.

Method 1. Using the Copy Database Wizard

1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

2. Expand Databases, right-click the desired database, click Tasks >> Copy Database.... If the Welcome to the Copy Database Wizard splash page appears, select Next.

3. Follow the steps to Select a Source Server, Select a Destination Server, Select a Transfer Method, Select Databases.

4. Configure Destination Database: Change the Filename and Destination Folder as needed. Continue with the database transfer by selecting Drop any database on the destination server with the same name, then continue with the database transfer, overwriting existing database files. Click Next.

5. Configure the Package: Type Newname Weekly Refresh in the text box labelled Package name. Check the box next to Save transfer logs. Click Next.

6. Schedule the Package: Click Schedule and then select the Change Schedule button.

7. Select the appropriate proxy from the Integration Services Proxy account drop-down list. Select Next.

8. Complete the Wizard: Examine the list of the chosen alternatives. Select Back to change an option. Select Finish to execute the task. The Performing operation tab keeps track of status data regarding the Wizard's execution.

9. Performing Operation: Select Close, if the operation is successful. Review the error log if the operation fails, and then maybe go back for further review. Otherwise, select Close.

10. Check the instance's new database to make sure it is there and review the task history.

Method 2. Copy Databases with Backup and Restore

✧ Copy the MSSQL database

1. To copy MSSQL database with SQL Server Management Studio. Connect to your SQL instance using SSMS.

2. Expand Databases and then select your database. Right-click the database and then select Tasks >> Back Up…

3. Select or change the backup location and then click OK. The default backup location is C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup. You can alter it though (network locations are also supported).

4. When the backup is complete, the confirmation window will appear. Select OK. Navigate to the backup file's storage location next. The file has a.bak extension.

5. Copy the file to a new server with a newer SQL Server version installed.

✧ Restore the MSSQL database to another server

1. Navigate to the new server and connect to the SQL Server using SSMS. Next, expand Databases. Right-click Databases and then click Restore Database....

2. Select Device and then click the … to browse to the device and location where you stored your backup file on the new server.

3. Click Add to add new backup media.

4. Navigate to the SQL backup file's storage location, choose it, and then click OK to confirm.

5. If everything is OK, a popup window stating that the restore was successful will appear. Click OK.

✍ Remember to take the old MSSQL database offline

Take the old database on the old server offline if everything goes as planned. Navigate to your old server, connect to your SQL Server using SSMS, and follow the procedure.

1. Right-click your database and then click Tasks >> Take Offline.

2. Select Drop All Active Connections, and then click OK.

Easy way to backup and restore your MSSQL database

The process of MSSQL copy database often goes hand in hand with the concept of database backup. Both practices share the common goal of safeguarding valuable data and ensuring its availability in case of unexpected events or system failures.

If you want to backup and restore SQL Server flexibly with the simplest operation, you can try the professional tool - AOMEI Cyber Backup. With its user-friendly interface and robust features, AOMEI Cyber Backup offers an easy way to safeguard your data.

▸ Operation Within LAN: Assist you in doing numerous backups of SQL database, including SQL Express within LAN.
▸ Centralized Backup: Schedule backups of SQL server database and run it automatically.
▸ Schedule Backup: Create backup schedules to automate backups daily, weekly, monthly.

AOMEI Cyber Backup supports Microsoft SQL 2005-2022, including SQL Express. In addition, it also supports the virtual machine backup within LAN, please download the 30-day free trial to have a try:

✍ Before you perform a database backup, please make sure:
1. Your computer with both AOMEI Cyber Backup Agent and Microsoft SQL Server installed.
2. The local disk or network shared to store backup files.

How to back up and restore SQL database with ease

1. Access to Source Device >> Add Microsoft SQL. If the database exists and the version is supported, it will appear automatically. Otherwise, you can click ​​​Add Microsoft SQL >> Download proxy program, and install the program on the device with SQL Server installed. Then click ​​​​​Already installed proxy and select the proxies you want to add.

2. Click … >> Authentication to validate the database instance. You can choose Windows Authentication or SQL Authentication. Enter the credentials and click Verify.

3. Create Backup Task: Click Backup Task >> Create New Task, and select Microsoft SQL Backup as your backup type. Then set the Device Name, Backup Target, Schedule, Backup Cleanup and Email Notification according to your need.

• Device Name: Click Device Name to select SQL instance and database for backup. You can choose the number of databases you want to backup according to your needs.

• Backup Target: Specify a local path or network path as backup target. To back up SQL database to remote location, you could click Add a new network storage. The added path will be saved in Add storage.

• Schedule (optional): Set up a schedule to run the SQL database backup daily/weekly/monthly, and set the backup method as Full/Incremental/Differential.

• Backup Cleanup: Help you to delete older backup version automatically and therefore save storage space.

• Email Notification: Enable you to receive email notifications when the task is abnormal or successful.

4. Start Backup: You can choose to Add the schedule and start backup now or Add the schedule only and click Start Backup to execute automatic SQL server backup.

5. Restore from backup: Click Backup Task on the left menu bar, locate the task you want to restore, and click … >> Restore. You can specify the target location by choosing Restore to original location or Restore to new location.

Conclusion

In conclusion, Microsoft SQL Server is a powerful relational database management system that is widely used in enterprise environments to store and manage large amounts of structured data. Copying a database is a common task that is required for various reasons.

Fortunately, there are various methods available for MSSQL copy database to another server and backup database in SQL server, including the use of built-in tools such as SSMS or third-party software - AOMEI Cyber Backup. By following best practices and using the right tools provided in the article, copying a Microsoft SQL database can be a straightforward and reliable process.