SQL Server

SQL Azure Backup Strategies (part 2)

12/30/2010 3:09:18 PM

2. 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 2 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 2. 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 2. Checking the database copy status

3. 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.

4. 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 backup. To do this with the Database Copy functionality, you must create seven copies of the source database—you have seven backup copy databases.

This strategy works, but keep in mind that you're billed for those additional seven databases. The key here is that if you're using SQL Azure, a look at your backup plan is critical.

What does Microsoft have coming in future Service Updates? The company is working very hard on adding and improving SQL Azure backup and restore functionality. Microsoft wants to hear from you—your feedback is important. Talks of database cloning and continuous backups have surfaced; keep your fingers crossed that this functionality and more will appear in the near future.

Other -----------------
- SQL Server 2008: Troubleshooting SSB Applications with ssbdiagnose.exe
- SQL Server 2008: Service Broker Routing and Security
- Migrating Databases and Data to SQL Azure (part 9)
- Migrating Databases and Data to SQL Azure (part 8)
- Understanding Service Broker Constructs (part 5)
- Understanding Service Broker Constructs (part 4) - Creating the Conversation Initiator
- Migrating Databases and Data to SQL Azure (part 7)
- Migrating Databases and Data to SQL Azure (part 6) - Building a Migration Package
- Migrating Databases and Data to SQL Azure (part 5) - Creating an Integration Services Project
- Understanding Service Broker Constructs (part 3)
- Understanding Service Broker Constructs (part 2) - Creating Queues for Message Storage
- Understanding Service Broker Constructs (part 1) - Defining Messages and Choosing a Message Type
- SQL Server 2008 : SQL Server Service Broker - Designing a Sample System
- Migrating Databases and Data to SQL Azure (part 4) - Fixing the Script
- Migrating Databases and Data to SQL Azure (part 3) - Reviewing the Generated Script
- SQL Server 2008 : SQL Server Service Broker - Understanding Distributed Messaging
- SQL Server 2008 : Full-Text Search Troubleshooting
- Migrating Databases and Data to SQL Azure (part 2)
- Migrating Databases and Data to SQL Azure (part 1) - Generate and Publish Scripts Wizard
- SQL Azure : Security - Access Control
- First look: Apple Watch

- 10 Amazing Tools You Should Be Using with Dropbox

- Sigma 24mm f/1.4 DG HSM Art

- Canon EF11-24mm f/4L USM

- Creative Sound Blaster Roar 2

- Alienware 17 - Dell's Alienware laptops

- Smartwatch : Wellograph

- Xiaomi Redmi 2
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8