It doesn’t matter if a company is large or small, almost every database administrator needs to maintain a copy of the production databases in a test environment for testing and development needs.
All you need to do for this is to copy the databases from the production server to a test server, and this can be done in different ways. Let’s take a look at them.
Backup/Restore
The easiest way is to perform a backup on the production server and restore it on a test server.
As a rule, such a process should be performed regularly, which means you have to create service bat/PowerShell scripts for regular backups on the production server and restore the backups on the test server.
If the production server and test server have access to one shared folder, then the task is simplified: to create backups on the production server, you can use a bat script with the following text:
sqlcmd -Q "BACKUP DATABASE [AdventureWorks] TO DISK = '\\shared\backup\AdventureWorksProd.bak'"
and on the test server, the script must restore the backups from the shared folder:
sqlcmd -Q "RESTORE DATABASE [AdventureWorks] FROM DISK = '\\shared\backup\AdventureWorksProd.bak' with REPLACE"
The user under whom the SQL Server runs on the production server and on the test server must have access to the specified shared folder.
You can schedule script for regular backups on the production server and restore them on the test server with Windows Scheduler (Windows + R -> taskschd.msc )
This is a simple and transparent solution, but it also has disadvantages: if a test database and production database are on different subnets, then transferring the backups can become a non-trivial task.
Regular full backups can be a significant load on the production server. To reduce this load, it is recommended to add transaction log backups to your backup plan. However, this will require creating more complicated service scripts. If you are not a PowerShell pro, then it is better to use ready-made solutions for this task.
SQLBackupAndFTP
SQLBackupAndFTP tool has all features for the regular database restore process on the test server without overloading the production server. To set regular copping into a test environment you have to create a backup job on the production server and a restore job on the test server.
Backup job
Once SQLBackupAndFTP is installed on your production server, set the connection to your DBMS.
Now choose the databases that should be backed up and sent to the test server.
Next, choose a place where the backups will be sent (from this place your restore job will take backups). This can be either a local folder or FTP or cloud storage (Amazon S3, Google Drive, DropBox, etc.).
The last of the basic steps in setting up a backup job is creating a schedule for the backups. It is highly recommended to add differential and transaction log backups to your plan.
Also, there are numerous useful options that can be set for your backup job, such as email notifications if the backups are failed or succeed, backup compression and encryption, and many more.
The backup job is set, run it.
Once the backup job is set and the backups are stored in the selected destination places, it’s time to set a restore job on your test server. The restore job will take the backups from the destination places and restore the backups on the test server.
Restore job
After SQLBackupAndFTP is installed on the test server, click on the “Job” and choose “Add Restore Job”.
Firstly choose the destination place where the backups are stored, it should be the same one that is used in your backup job (if you specified subfolder, it is also important to specify it).
When the place where the backups are stored is chosen, it will be scanned, and the available databases will be shown, choose the needed.
Next, connect to the needed DBMS and set the restore schedule, for example, once a day at 1 a.m. If the backup job creates differential and transaction log backups as well, then the “keep files local” option will be useful. If it’s enabled, only the missing files required for recovery will be downloaded from the transit storage.
SQLBackupAndFTP effectively solves the problem of not only keeping a test database up to date, but also the typical task of creating database backups and sending them to the destinations.
SqlBak
SqlBak – it is a cloud service that allows you to schedule backups and restores, as well as sending them to cloud storage.
One of the options of SqlBak is the ability to run a restore process on another server after the backup is complete, which is ideal for copying a database from a production server to a test server.
For a start, you have to register, download, install the application on the production server, and enter the secret key.
Now it’s time to set a connection to your SQL Server.
The same should be done on the test server.
The bother servers (production and test) should be displayed on the “Dashboard” page. Click on the “Add new job” button, choose the production server, and create a new job.
On the opened page, select the databases that should be backed up and restored on the test server, as well as transit cloud storage.
As in SQLBackupAndFTP, you can add differential and transaction log backups to your backup plan. Running a transaction log or differential backups, along with the full backups, will significantly reduce the load on the production server.
Once all the needed settings are done, set a restore option, and specify a test server in the “Restore to” box.
After all the settings are made, please save the settings and run the job. If everything was set correctly, then at the end of the log, you’ll see a record that the needed databases have been restored to the test server.
SqlBak allows you not only to keep the test server up to date, but it is also a convenient service for monitoring the servers and performing regular backups.
SQL Server Management Studio
The main tool for working with SQL Server is SQL Server Management Studio (SSMS). Among its many features, there is also the possibility of copying a database from one server to another.
Copy Database Wizard can be used to copy a database from the production server to the test server. To run it please right-click the database to clone and click “Task” > “Copy Database”.
In the first two steps, you need to set up a connection to the production server and the test server.
In the third step, you have to select the type of database copy. The “Detach” and “Attach” methods are difficult to set up and worse, require shutting down the databases while copying. The optimal choice would be the SQL Management Object method.
Now select the databases that should be copied.
In the next two steps of the wizard, you can set the paths to the .ldf and .mdf files, set up the rules in case if a database already exists, and specify the name of the Integration Services (SSIS) Packages.
Next, right in the wizard, you can set a schedule for regular backups.
In the last step, the correctness of the settings will be made and a job will be created in the SQL Server Agent.
That method has two drawbacks:
- The test database and production database should be communicating directly with each other. This is not always possible, for security reasons.
- Copying via SQL Management Object is very slow, and can significantly load the production server.
Bottom line
Several ways of regularly copying a production database into a test environment were discussed. To copy the database, it is enough to perform a backup and restore, or use the built-in functionality of the SQL Server Agent, however, to do this regularly and not load the production server, you can use ready-made tools, such as SQLBackupAndFTP or SqlBak.
When choosing the final method, it is worth focusing on your level of proficiency in .bat/PowerShell scripts, the size of the databases, and the amount of your time that you are willing to spend on maintaining the processes you have configured.