Database Reference
In-Depth Information
Knowing When a Copy Is Complete
The question then becomes, how do you know the copy is finished? The answer is that Microsoft created
a new data management view (DMV) to return the details of the database copy operation. This DMV is
called sys.dm_database_copies, and it returns a great deal of information about the status of the
database copy, such as when the database copy process started and completed, the percentage of bytes
that have been copied, error codes, and more. In addition, Microsoft modified the state and state_desc
columns in the sys.databases table to provide detailed information on the status of the new database.
Figure 5-21 was generated by deleting the TechBio2 database and creating it again. This time, a
statement looks at the sys.dm_database_copies DMV and checks the status of the copy. You can see the
statement highlighted in Figure 5-21. You can also see results in the figure from the query against the
DMV. The TechBio database was tiny to begin with, so the copy takes only a few seconds, if that.
Figure 5-21.
Checking the database copy status
Automating a Database Copy
You can schedule a database copy via an on-premises SQL Agent job and an SSIS package (as discussed
earlier in this chapter). The job can be scheduled like a normal on-premises SQL job, as long as the
connection information for the Execute SQL task points to the SQL Azure database.
Although this may not be the most favorable solution, it's certainly an option, and it does provide
the scheduling capabilities you're looking for. The key for this solution is to first delete the copy database
before you re-create it.
Maintaining a Backup History
The Database Copy functionality lets you create an instant backup of your database, but it doesn't
provide a way to create a backup history. In other words, you can't append to the backup and create
multiple days' worth of backups. You do have several options, however.
If all you care about is backing up the current day's data, you can delete the current backup copy
and re-copy the database. This is a viable option and doesn't require a lot of maintenance.
If, on the other hand, you want a backup history, doing so is a bit more tricky. Many, if not most,
companies like to keep a week's worth of backups. These companies back up their databases each night
and keep seven days' worth of backups so they have the option to restore past the previous night's
