SQL Azure Backup / Restore

After a coworker and I spent the better part of a day trying to get a local database (with data) deployed and running on our SQL Azure instance, I finally found a tool that seemed to make this task at least attainable.  SQLAzureMW has a wizard that automates some of the very manual tasks required to manage a SQLAzure instance.  I used the sync data from local to azure instance options through the wizard, and after some trial and error, it did actually work.    Although, it is no replacement for actually having a full suite of smss tools....  hello....  But, I digress.


I found that in my case anyway, it was an easier task to simply create a new catalog each time instead of trying to sync the data or create import scripts.  After finishing that, the database was created, but I couldn't actually log in to it or use it from my app.  So, we realized that you have to implicitly grant all permissions using a sql on your new catalog.  There is no tool for user security management on SQL Azure.  In order to speed things along, I wrote this quick little statement:

DECLARE @userName varchar(100);  

DECLARE @loginName varchar(100);  

SET @userName='myNewUser';  

SET @loginName='myExistingLogin';  

SELECT 'DROP USER ['+@userName+'] ;'  

UNION ALL SELECT 'CREATE USER ['+@userName+'] FOR LOGIN ['+@loginName+']
   WITH DEFAULT_SCHEMA=[dbo]'  

UNION ALL  

SELECT 'GRANT ALTER, DELETE, REFERENCES, CONTROL, INSERT, SELECT, UPDATE,
  VIEW DEFINITION ON [dbo].['+name+'] TO USER ['+@userName+'] ; GO'  

from sys.tables  

UNION ALL SELECT 'GRANT EXECUTE, ALTER, VIEW DEFINITION ON [dbo].['+ name+']
  TO USER ['+@userName+']; GO'  

FROM sys.procedures;    

At this point, we were able to run against our newly created catalog and so far, it has been working well. We have signed up for a beta group with Microsoft to use what they told us were backup and restore tools on azure.  Hopefully, that will be a real solution.  But in the meantime, this worked pretty well.

New Blog

I am starting a new blog and moving from the wiki format. Moving forward, it will be DevStorm, not DevRamble. That was already taken. So, this is DevStorm for development Brainstorming.