how to transfer data from Prod environment to Dev environment?

Question

My database in the prod environment is very large and I backup this database every day and restore it to the dev environment. This process takes 4 hours, which is actually too long. What else can I do instead of backup-restore?

My database size is approximately 133848.00 MB I want to move from Prod to Dev environment once a month, but I will not move all the data in this move. I only want to move data from the last year. I tried using replication and I can move the tables with this method, but I couldn't figure out how to move the last year's data in the table. Thanks in advance

Answer 1

I backup this database every day and restore it to the dev environment. This process takes 4 hours, which is actually too long.

Remove backup time from this equation. You have to do it anyways.

What else can I do instead of backup-restore?

Let me rephrase your question to how to speedup restore dev

You can setup log shipping to dev server as mydb_logship and perform cloning mydb_logship to mydb_dev by copying over mdf and ldf files.

1. Set mydb_logship to standby read-only mode
2. Set both mydb_logship and mydb_dev to offline
3. Retrieve or hardcode locations of data and log files from sys.master_files
4. Overwrite mydb_dev files from the ones of mydb_logship
5. Set both databases online

I only want to move data from the last year.

To do so you have to partition your tables and put historical data to different filegroup ie historical_fg. Then you can set this filegroup read-only and skip it from backup and restore.

Here is the link with more examples https://chad-franklin.com/2021/05/02/partial-database-backups-and-restores-with-read-only-filegroups/

Another option is to setup regular ETL process from prod to dev.

Answer 2

There are a few things that come to mind. Moving just a subset of data from prod to dev is far more surgical and will take significant work on your part to do correctly so I'm limiting this to just the original question, restoring from prod to dev more quickly.

First - The timing, 133GB whether that's completed in 4 hours (backup+restore) or 2 hours (restore only), that's a long time (IMO). Are you striping your backups across multiple files? You should be able to see quite the improvement by striping them across 4 or 8 files. Ola Hallengren's maintenance scripts support that as an argument.

If you are backing up to a cloud share, try backing up to a local disk, then copying the backups. This will almost certainly be faster.

Try using compression on your backups to further speed up the backup process. It does burn a little CPU, but reducing the amount of actual data needing to be written will dramatically speed up your backups.

Second - You can make the restoration take less time (from the perspective of your developers) by restoring the database from prod as a different name, then when it's complete, dropping and then renaming the database. You will need twice the space, but it will reduce the time that the dev database is unavailable.

So:

• Backup database [foo] from prod
• Restore database [foo] to dev, naming it [foo_copy]
• Drop database [foo] on dev
• rename database [foo_copy] to [foo] on dev

Answer 3

My database in the prod environment is very large and I backup this database every day and restore it to the dev environment.

I assume your company's Privacy Notice declares this usage of individuals' Personal Data?
If not, technical concerns might be the least of your worries.

This process takes 4 hours, which is actually too long.

Do you schedule this task overnight, when the test database is not in use? If so, four hours seems quite reasonable to me.
Of course, losing four hours of your [Developers'] working day would be a problem.

My database size is approximately 133 GB I want to move from Prod to Dev environment once a month, but I will not move all the data in this move.

Your Backups are everything you need to get your Database back if your database / server / company explodes in a ball of flames. You cannot [easily] use them to work with logical subsets of the Data. To do that, you'd have to write something yourself, with knowledge of the table structures, relationships between them, how to identify the subset of rows required, etc., etc.

Answer 4

SQL Server supports different types of backups. I think Incremental backup is what you are looking for. Please check : SQL Server Backup

An incremental backup is a type of backup that copies only the data that has changed or been created since the last backup. Incremental backups are often used when there is too much data to back up every day. They are quick to perform, use the least amount of data, and take up the least amount of storage space. However, restoring from an incremental backup requires the last full backup and every incremental backup since then.

For example, if you perform a full backup on Sunday, your incremental backup on Monday will only copy changes made since Sunday. On Tuesday, it will only copy changes made since Monday's backup.

Ref: https://phoenixnap.com/kb/full-vs-incremental-vs-differential-backup