Database Reference
In-Depth Information
SQL Azure Backup Strategies
Your data is in the cloud, but it really doesn't stop there. Much of the functionality that DBAs have at their
fingertips when dealing with local data stores doesn't exist in the cloud yet. The operative word in that sentence
is
yet
. At the time of this writing, SU4 was recently released, and it supports Database Copy functionality.
Database Copy enables you to copy your database to make a new database on the same SQL Azure
server. Alternative, you can copy to a different SQL Azure server in the same subregion or data center.
This functionality is much needed, but at the same time it has some shortcomings; some can be worked
around, but others will require future service updates.
Copying a Database
The Database Copy feature allows you to create a single copy of a source database. You do so by adding a
new argument to the
CREATE DATABASE
statement:
AS COPY OF
. As a refresher, the syntax for
CREATE
DATABASE
is as follows:
CREATE DATABASE MyDatabase (MAXSIZE= 10 GB, EDITION= 'Business')
To create a copy of a source database, the syntax now becomes
CREATE DATABASE MyDatabase AS COPY OF [
source_server_name
].
source_database_name
]
Thus, if you want to create a copy of your TechBio database, the syntax is
CREATE DATABASE TechBio2 AS COPY OF
servername
.TechBio
Figure 5-20 shows the execution of the previous statement. The interesting thing to note is the
message in the Messages window. When you execute the
CREATE DATABASE
statement with the
AS COPY
OF
argument, you immediately get the “Command(s) completed successfully” message. Does this mean
the copy finished that quickly? No. This message means the copy has
started
. You can also see in Figure
5-20 that the TechBio2 database is already listed in the list of databases; however, that doesn't mean the
database copy has completed.
Figure 5-20.
Copying a database
