Programming4us
         
 
 
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 Azure Backup Strategies (part 1) - Copying a Database
- 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
 
 
Most View
- Exchange Server 2010 : Exchange Unified Messaging Architecture
- Exchange Server 2007: Monitor Your Exchange Environment (part 2)
- Adding, Dropping, and Configuring Linked Servers
- SharePoint 2010 : Configure Access Requests for Lists and Libraries
- Securing Exchange Server : Configure Message Hygiene Options (part 2) - Protect Against Unwanted Mail Sources
- Windows 7 : Using Parental Controls to Restrict Computer Usage (part 1) - Activating Parental Controls
- Writing Your First Phone Application - Adding Code (part 2)
- Windows 7 : Scripting Windows with PowerShell - Getting Started with PowerShell
- Recovering from a Disaster in an Exchange Server 2010 Environment: Identifying the Extent of the Problem (part 1)
- SharePoint 2010 : Scaling Out a SharePoint Farm - Identifying a Logical Location of Services on Servers
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS