Copy local SQL database onto Azure

Question

I want to do the - should be simple - task of copying a database from local to live...

I have all the data and table structure I want in my local database.

I have a currently running database live database that has all the backup stuff assigned to it etc so I don't just want to create a brand new database (not that I can find how to do this either)...

I just want to remove all tables from that database and then copy all my data and tables from the local into the live azure sql database.

How do I do this???

Answer 1

1. If you goto SQL Management Studio, right click on the database you want to copy data for...
2. Goto Tasks Generate Script
3. Select just your tables not the entire database object
4. Open up your azure database in Visual Studio that you want to create the copy into
5. Open a new query for that database and paste the generated script in
6. Execute and pray to the gods

7. Jump around because it worked, now run the following command on the azure table to disable foreign key migrations:

   DECLARE @sql NVARCHAR(MAX) = N'';

   ;WITH x AS 
   (
     SELECT DISTINCT obj = 
         QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' 
       + QUOTENAME(OBJECT_NAME(parent_object_id)) 
     FROM sys.foreign_keys
   )
   SELECT @sql += N'ALTER TABLE ' + obj + ' NOCHECK CONSTRAINT ALL;
   ' FROM x;
   
   EXEC sp_executesql @sql;
   

8. now go into sql server management studio and right click on your local database and goto tasks and then export data

9. export to your azure database but make sure to edit the mappings and tick the identity box.

10. the data is moved, now set the foreign keys back using this on your azure database:

    DECLARE @sql NVARCHAR(MAX) = N'';

    ;WITH x AS ( SELECT DISTINCT obj = QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) FROM sys.foreign_keys ) SELECT @sql += N'ALTER TABLE ' + obj + ' WITH CHECK CHECK CONSTRAINT ALL; ' FROM x;

    EXEC sp_executesql @sql;
    

Answer 2

you can try to achieve this with SQL Server Management Studio.