Presently i am generating SQL scripts everyday to take the backup of SQL Azure database. I need to automate this process using some freeware. How can i achieve this?
Note:- I need to take the backup in form of SQL Script to some local machine.
-
Did you solve this problem? How do you generate your SQL scripts?Jonas Sourlier– Jonas Sourlier2012-10-16 13:47:06 +00:00Commented Oct 16, 2012 at 13:47
Add a comment
|
5 Answers
Option 1: Run some code/script to backup the database
You could run the SQL using the SQLCMD utility.
http://msdn.microsoft.com/en-us/library/ms162773.aspx
To schedule this, just use the Windows Task Scheduler.
http://msdn.microsoft.com/en-us/library/bb756979.aspx
Option 2: Use Azure Data Sync
Keep a local database in sync with your SQL Azure database, like a constant backup.
http://msdn.microsoft.com/en-us/library/windowsazure/hh456371.aspx
Option 3: Use BacPac
There is an API available in Azure to backup your database to a BACPAC format.
http://msdn.microsoft.com/en-us/library/windowsazure/hh335292.aspx
A RedGate tool then allows you to restore to a local SQL Server.
http://www.red-gate.com/products/dba/sql-azure-backup/
Edit
I have since created a command line tool which will create a copy of your database, and back it up to BacPac using the Azure Management API:
4 Comments
Balraj Singh
Thanks for your reply. But can you elaborate on how to generate SQL Script for data using SQLCMD? as we get the data from generate script feature in SQL Azure or SQL Server.
Simon Opelt
The command line utility from redgate has been discontinued. It is still available but contains open issues.
Richard Astbury
I have actually published a command line tool that helps you do this. It's not completely finished, but serves as an example of how to automate this: github.com/richorama/SQLDatabaseBackup
Flea
If you create a secondary Azure SQL server you can do this as Transact-SQL by executing this command on your secondary Azure Server:
CREATE DATABASE YourBackUPDatabaseName AS COPY OF YourProductionServer.ProductionDatabaseName;You could try RedGate Azure Sql backup http://www.red-gate.com/products/dba/sql-azure-backup/
That can be also scheduled using Task Scheduler.
I have written some ideas on how to do that: http://anttitech.wordpress.com/2011/11/06/azure-sql-backup-and-restore-scenarios-using-bacpac-exportimport/
1 Comment
Balraj Singh
Thanks for your reply i am now using this and it is working properly. Only issue that i am now facing is that since in the script we have to mention local database name and it is constant always so the previous day backup is lost. Is there any way that i can declare the databasename like [DATABASENAME_CURRENT].Following is my script that i have used and scheduled a job. ./RedGate.SQLAzureBackupCommandLine.exe /as: AzureServerNAME/ad: AzureDatabaseName /au:AzureUserName /ap:AzurePassword /cc /s /ls:. /ld:LOCALDATABASENAME /dl /v /ba
SQL Azure Database does not support SQL Server Agent or jobs. You can, however, run SQL Server Agent on your on-premise SQL Server and connect to SQL Azure Database.
Source: http://msdn.microsoft.com/en-us/library/windowsazure/ee336245.aspx
SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks, which are called jobs...For example, if you want to back up all the company servers every weekday after hours, you can automate this task. Schedule the backup to run after 22:00 Monday through Friday
Source: http://msdn.microsoft.com/en-us/library/ms189237.aspx
Comments
Create an in-the-cloud backup of the database (to get a consistent snapshot) and then use the Data-Tier Application Framework (DACFx) client tool to copy the snapshot to your local machine, as described in Automating Windows Azure SQL Database Backup.
Comments
I have come across a free tool that makes backups of your SQL Azure Databases. Backups can be stored locally or on blob. You can also schedule backup operations with its built-in scheduler, or use the Windows Scheduler and the provided command-line utilities.
