Export SQL Schema and Data from SQL Server 2008 for Azure

2017-12-07T10:20:11+00:00 November 8th, 2012|Azure, SQL|

Have had some situations where I’ve been given database backup and have been asked to migrate it to the Azure cloud platform, without access to a full version SQL Server. This tends to be when in-house application are written and eventually needs to be move to the cloud.

Solution is as followed (Assuming the database is mounted)

– Open SSMS (SQL Server Management Studio)
– Expand ‘Databases’
– Right click the database you want to migrate and press ‘Tasks’ then ‘Generate Scripts’
– Press next (If you’ve not selected ‘Do not show this page again’ before)
– Select either ‘Script entire database and all database objects’ or ‘Select specific database objects’ and press next.
– Press ‘Advanced’
– Scroll down and look for ‘Script for the database engine type’
– Change from ‘Stand-alone instance’ to ‘SQL Azure Database’
– Select all the other options (Script indexes, triggers etc.) and then press ‘OK’
– Fill out the other details (Such as publishing locations etc.) then press next.
– Review your selections then press ‘Next’ and let SSMS generate the script.

Once the script has been generated, you can then use the Azure DB Management portal to import this file and everything will work as expected.

NOTE: You don’t get full SQL Server access unless you’ve built a Windows Server VM on Azure, this only applies to when you’re doing the changes through the portal and only use the front end management tool. Also, this is generally a syntax issue – This is A LOT faster than re-writing the SQL on Azure itself. – If you had a full version of SQL to work with, you would just restore the database itself.

Cheers,

Jason.