locked
Cannot take backup of SQL Azure database RRS feed

  • Question

  • Hello

    i run following command to take backup

    CREATE DATABASE  database_destination_name
    AS COPY OF [source_server_name.]database_source_name

    or

    CREATE DATABASE  [destination_servername.]table_name
    AS COPY OF [source_server_name.]table_name

    but i got an error i.e

    The CREATE DATABASE statement must be the only statement in the batch.

    Can anybody help me on this issue. I want to create sql job for backup of SQL Azure database rather than taking backup of database manually.

     

    Saturday, September 18, 2010 12:08 PM

Answers

All replies

  • Hi Jaspreet,

    You need to place the command as appart of a batch file.

    1: Create a Simple Text File c:\backupdatabase.txt

    2: Open the text file and place your command:

    "CREATE DATABASE  database_destination_name AS COPY OF [source_server_name.]database_source_name  "

    3: Save the file and run this command from Start Menu (RUN())

    sqlcmd -U <username@servername?  -P <password> -S <servername>.database.windows.net -d <databasename> -i C:\backupdatabase.txt -o C:\Output.txt

    4: After the command is executed, check the c:\output.txt file for errors. If everything went ok this file will be blank.

    This advice is given as is, always test this command on a non important database before trying. Also check SQL Help about backing up the database.

    Hope this helps

     

    John Galvin


    Sometimes the most important history we make… is what we make today www.backupearth.com
    Saturday, September 18, 2010 3:20 PM
  • You can automate a backup of you SQL Azure database using several methods. I wrote a blog post about one one of the methods here:

    http://geekswithblogs.net/ScottKlein/archive/2010/09/09/automating-a-sql-azure-database-copy.aspx

    If you have any questions, let me know. We can look at other options.

    Scott

    • Marked as answer by JaspreetSingh8 Tuesday, September 21, 2010 12:38 PM
    Monday, September 20, 2010 11:01 PM
  • hi

    i create package as you explain in ur blog post and it successfully run. But i want to know how i create a SQL Job for this package because i don't want to run it manually each day. When i create a SQL job for this it ask me for server name where package store. Can you tell me please how i do this?

    Thanks for your reply.

    Tuesday, September 21, 2010 12:38 PM
  • Yes, I can help with that. It is a multi-step process so I will create a follow-up blog post today to illustrate that. I will post back here when the blog post is up. Will that work?

    Tuesday, September 21, 2010 1:41 PM
  • OK, I posted a Part 2 which explains how to create the job to automate the backup. It is here:

    http://geekswithblogs.net/ScottKlein/archive/2010/09/21/automating-a-sql-azure-database-copy---part-2.aspx

    Let me know if you have any questions.

    Scott

    Tuesday, September 21, 2010 9:24 PM
  • Hello

    I create a sql job. But it gives me following error:

    Source:       Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8007000D "The data is invalid.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.  End Error  Error: 2010-09-23 18:19:56.01     Code: 0xC0202009     Source: Package Connection manager "Azure Server Name"     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E4D  Description: "This session has been assigned a tracing ID of '941cf914-35c3-4aaf-a000-6dfae6bf9e61'.  Provide this tracing ID to customer support when you need assistance.".  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'Azure DB Username'.".  End Error  Error: 2010-09-23 18:19:56.16     Code: 0xC00291EC     Source: Execute SQL Task Execute SQL Task     Description: Failed to acquire connection "Azure Server Name". Connection may not be configured correctly or you may not have the right permissions on this connection.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  6:19:53 PM  Finished: 6:19:56 PM  Elapsed:  2.61 seconds.  The package execution failed.  The step failed.

    but when i run the package manually then it run successfully but not run thru sql job.

    Thursday, September 23, 2010 1:08 PM
  • Did you get it to work? Let me know and I'll see what I can help with...

    Tuesday, September 28, 2010 10:19 PM