DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. Backup and Restore of a SQL Clone

Backup and Restore of a SQL Clone

Learn about working with clones, backing up the clone, and restoring the backup. Also learn why cloned databases are SQL server databases.

Steve Jones user avatar by
Steve Jones
·
Apr. 18, 17 · Tutorial
Like (0)
Save
Tweet
Share
2.88K Views

Join the DZone community and get the full member experience.

Join For Free

With SQL Clone, I can create multiple copies of my database for testing very quickly. First, I create an image of the source database, and then from that image, I can create multiple clones. Each copy, or clone, appears to SQL Server as a normal database, and yet occupies a fraction of the disk space of the full database from which the clone was created.

I can work on my database clone locally, making changes that are saved to a local differencing file. However, when I query the clone database, SQL Clone reads any other data — i.e. any data I’ve not changed locally — from the data image file. However, what if I want to continue working on my local clone while disconnected, such as when traveling? At this point, I have no connection to the data image location unless the image is local or to SQL Clone Server (which is required for clone maintenance and management).

However, since SQL Server sees each clone as a normal database, I can perform normal backup and restore operations on a clone, just like any other database. I can back up my clone, copy the backup file to the machine on which I want to work while disconnected, and then perform a restore operation. I’d only want to do this for database backups that don’t contain any sensitive or PII data.

When we capture a full database backup of a clone, we capture the local changes, plus all the unchanged objects and data from the source data image, which means that while disconnected, I’m working with a normal, full-sized database.

Let’s see how all this works.

Working With Clones

I’m starting from a data image called PartsBase from my PartsUnlimited database. If you don’t know how to create a data image, there is a good getting-started post from Tony Davis.

I want to create a clone from this image, so that I can work on it, and develop code, on my local development database. I’ll call the clone PartsUnlimited_Clone so that when I access it in SSMS, I’m aware that it’s a clone.

Creating the clone takes only a few seconds, and I see it in SSMS and work with this database during development, just like any other database. In my case, I’ll add a new stored procedure.

Having altered my database clone, I now have a change stored locally on my development workstation, which isn’t in the original data image.

Backup the Clone

Suppose I want to move a copy of this database elsewhere? Perhaps, I want to continue working on an existing clone, to which I’ve made development changes while connected, while working remotely, such as during an upcoming flight to England, with no connection to the remote data image or to SQL Clone Server.

Let’s set that up. First, I’ll make a backup of the clone. I can do this because SQL Server sees the clone as a normal SQL Server database. All the structures and data pages appear to be in the database.

Now, I have this file in the backup folder on my development workstation.

I’ll copy this backup file to the backup folder of the SQL Server instance on my disconnected machine — in this case, my laptop. Since this is a normal backup, it’s about the same size as my original PartsUnlimited database, something I need to be aware of. While SQL Clone saves me disk space, SQL Server backup and restore doesn’t. I need to make sure I’ve enough space for the backup file and the restored data/log files on my laptop.

Restoring the Backup

From this point on, I’m just working with a normal, full database backup, but one that incorporates the development changes I made to my clone. If I were running SQL Clone Server on my laptop, I could consider creating a new data image locally, from the backup, and then clone from that image, but since I’m not, I’ll just perform a normal database restore operation.

All I need to do is connect to the instance on my local laptop and perform a restore, just the same as any normal SQL Server restore. First, choose the Restore Database option:

Next, I select the backup file as my device and, as we can see, the default database name and original instance are listed. In this case, I also need to click the Move files checkbox on the Files tab since the paths on my laptop are different from those on my development workstation.

I’ll change the name of the restored database to PartsUnlimited since this isn’t a cloned database, and click OK to restore the database. Once that’s done, I see it in my new instance. I have my new stored procedure as well, which is what I expect. This is a normal SQL Server backup and restore.

Now, I’m set for my flight and can work in the air. When I get back to the office, if I want to share this with others or use it in test environments as a part of my CI process, I can make a new SQL Clone image and additional clones from this. If I’ve made any development changes. I’d want to ensure these are committed to my VCS.

Cloned Databases Are SQL Server Databases

SQL Server sees the cloned database as a user database. That means that I can treat it as one, including backup and restore. If there are situations where some developers can’t connect to the SQL Clone Server, then backup and restore works to get them a copy of the database. If you want to move databases between SQL Clone installations, such as in remote offices, you can also use this technique to move your databases.

If you have many copies of development and test databases, SQL Clone can potentially save you a lot of provisioning time and a lot of disk space.

Clone (Java method) sql Backup Database Backup and Restore

Published at DZone with permission of Steve Jones, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • A Real-Time Supply Chain Control Tower Powered by Kafka
  • How Do the Docker Client and Docker Servers Work?
  • Spring Boot Docker Best Practices
  • What Java Version Are You Running? Let’s Take a Look Under the Hood of the JDK!

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: