You’ve already seen how to create database copies using the
CREATE DATABASE AS COPY OF command. You can also use database copying as a simple backup and restore methodology. To do this you first create a T-SQL backup script that works against your SQL databases. Next you incorporate this script into a scheduled job that executes using your local installation of SQL Agent. One example might resemble the following scenario.
Every evening your backup script creates a fresh copy of your production database, naming it with a suffix indicating the current day, month, and year (e.g.,
MyDatabase_03_10_2013). After accumulating the desired maximum number of copies, your script drops the oldest copy before creating ...