Sergey Nivens - Fotolia
What is database refreshing?
IT testing and software development constantly requires fresh data. It's easy to run a database refresh and generate new data for sandbox or production tasks.
If you're a database administrator, you may have seen the term database refreshing, and now, you may wonder what the phrase means and if it's the same as database cloning or database restoring.
Depending on the type of database you're working with, database refreshing can have different meanings. Generally, it is the process of overwriting an existing database with a stage, development or production database to purge old data.
This process refreshes the database and its data while keeping all database objects intact. No new database is created, and it can only be performed on existing databases, which is the main difference from other database actions.
Differences between cloning, restoring and refreshing
The idea of database refreshing is not standardized and varies among application code, such as MySQL or Oracle. In some instances, database refreshing may be the same as database restoring. It is often confused with the term database cloning, but those two terms are slightly different.
- Database cloning is the process of creating an exact copy of a database.
- Database restoring is the process of loading a backup copy of data onto a database.
When you clone a database, the copy reflects the database in that moment in time. You can then use the copy as a backup of that database. This allows you to perform tests on a database without the concern of data loss.
When you restore a database, you load the backup or clone on an existing database or a new database. When completed, the database should match the clone exactly. This process restores all data and database objects as recorded in the backup file.
When you refresh a database, you are not creating a new database. All the data and database objects are kept intact and only unnecessary data is purged. The type of data that is removed is usually leftover data from tracking changes in a test phase.
Why and when to perform a database refresh
The need for fresh data will drive how often you should do a database refresh. For example, you may need a refresh when you move a database from stage to production, when you need to sync stage and production databases or when you want to test an application with production data.
It's most commonly done when users test a development database and must use a fresh copy of the production database. You must scrub the development database data for a fresh testing sandbox that reflects the production database.
As for how often you should do a database refresh, it's up to your particular requirements. If a team tests a database daily, you'll need to perform a refresh every night. If you are writing code, you won't need frequent updates; once a week should be fine.
It may also depend on how long the task takes for your database. If it takes a long time to do, it may not be worth running every day, unless necessary.
Before you conduct a refresh, be sure to:
- Always have a backup database, in case something goes wrong.
- Always verify the refresh after it is complete.
- Make sure there is enough space to store the backup and refresh the database.
- Make sure the database being refreshed is the same size as the source database.
- Create an audit log for automated jobs to capture failures.
You should also end all connections and tasks and warn users to save and log off before refreshing. The job won't automatically log out users, so they may lose work if they stay logged on.
These steps and considerations keep your database and its data fresh and up to date on a regular basis so you can ensure users are working with the most accurate information.
How to refresh a database
To perform a database refresh, have the version you want to load onto the database ready to go. Then, back up the database you decide to refresh. Once the backup is complete, you can start to load the fresh database.
Once it's finished loading, you should verify that it worked correctly. Check to make sure the database is online, accepts connections and contains the same number of database objects as the original file. You may also want to run a record count or compare the data file and log files. Do everything you can to ensure the database is working properly after refreshing, and once it's verified, delete the backup database.
The manual database refresh process is straightforward, but the overall time you spend on the task will start to add up quickly if you must refresh often. This makes such a task an ideal candidate for automation.
Once you've performed a manual database refresh and verified the job, schedule the job to run however often you'd like. Simply save the script to run any refresh steps and have it run on demand. This will automate the task and ensure your database has fresh data on a regular basis.
