There are a bunch of ways you could create a database clone. Backup and restore is one method. Export/Import is another. There are even third party tools that will help with that. However, each of these has a problem. It’s moving all the data, not just once, but twice. You move the data when you export it and you move it again when import it (same thing with backup and restore). That makes these methods slow for larger databases. How can you create a database clone without moving the data multiple times?

Don’t Move the Data At All

New with SQL Server 2016, expanded in SP1, and added to SQL Server 2014 SP2 is a new command, DBCC CLONEDATABASE. This is like a dream come true. The use is extremely simple:

DBCC CLONEDATABASE(WideWorldImporters,CloneDB);

What happens is that it creates a new database on the server. In that database you get the full set of schema plus all the statistics and meta data. What meta data you might ask. Query Data Store information is moved as part of this. The database is created read only and the file size is whatever the value is on the Model database.

For example, my WideWorldImporters database is currently about 1gb worth of data. The new CloneDB that I created is 80mb in size. You can, as of SP1, control this even further by choosing to not move statistics or not move Query Store data.

And the Point Would Be?

Why would you just create an empty database with statistics and Query Store information? What is that going to do for you?

Obviously, without data, you’re not going to be troubleshooting data issues. However, what you have is a database that you can run performance tests against. Because it has the statistics and the meta data from your original database, this cloned database will behave as if it had data inside of it in terms of generating execution plans. You can run all kinds of queries and see how they would behave, without moving data around.

You’re excited now, right.

The database is read only so that you avoid a statistics update. If you were to take the database out of read only and then update the statistics, there’s no data. Therefore you would end up with statistics showing that there is no data. Any execution plans generated would not be reflective of the original database.

I’d recommend caution when using this. You may create the clone on production, but I wouldn’t suggest leaving it there or using it in tests there. Even Microsoft suggests caution. Here is the output from my clone operation:

Database cloning for ‘AdventureWorks2014’ has started with target as ‘QDSClone’.

Database cloning for ‘AdventureWorks2014’ has finished. Cloned database is ‘QDSClone’.

Database ‘QDSClone’ is a cloned database. A cloned database should be used for diagnostic purposes only and is not supported for use in a production environment.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I’d probably back up that clone and restore it to a non-production server for any testing.

Only Move the Data Once

So, what if you really do want to have data. Is there a fast way to move it twice? Not really. However, what if you only move it once? Redgate SQL Clone is a new piece of software that does just that.

What happens is a copy is made of your database. It’s about the size of a backup and takes just as long. Then, using virtual drives, that is mounted to the server and it occurs pretty much instantly. However, the data doesn’t move at all. You’re reading from the copy of the database, not local storage. Further, more than one server can attach to the copy, so lots of servers can have a clone.

The main use for this would be for troubleshooting production data, but offline, away from the production server. If you cleaned the data and made a clone of that, you have another primary use as your development database. You can have a clean copy of production that only takes up the space of a single backup on multiple development servers. That’s a win.

Again, I wouldn’t recommend using this in a production environment either. Yes, the initial backup can be from production, but the mounted clones should not be.

This is a great way to get a clone of your database but only have to move the data a single time.

Conclusion

It is possible to get that copy of the database off your production server without having to move the data twice. If you’re only looking for performance tuning, check out DBCC CLONEDATABASE. If you also need data, take a look at SQL Clone from Redgate. Either way, it is possible to get a copy of your database faster and easier than moving the data two times.

The post Database Clone appeared first on Grant Fritchey.