January 17, 2024 at 12:20 pm
Hi all,
My boss wants to move our customers over to azure from SQL express (mostly SQL Express, some are on SQL server.)
I've created a .bak backup file of the SQL database and uploaded it to the blob storage but I've have no idea how to restore that .bak file in Azure.
I've seen something online about using the following SQL but it keeps telling me it's not available in my version of SQL and I've run it in both SSMS and the query editor in azure...
create credential[my blob storage URL]
with identity = 'SHARED ACCESS SIGNATURE',
SECRET = 'my blob SAS URL'
RESTORE DATABASE My DB name
FROM URL = N'URL of my .bak file in my storage blob container';
I also tried some powershell stuff I found online but I get the same issue, powershell doesn't seem to know the commands.
I have used the migration tool but that seems a bit hit and miss, just seems to run forever and give no error messages as to why a table cannot get copied over and just seems to keep running as if it's copying data when it's not.
Can anyone tell me the easiest and quickest way to get my SQL server database into an azure database please?
I feel like I'm going around in circles, I haven't got a clue and azure seems totally over complicated to me.
TIA
Paul
January 17, 2024 at 12:26 pm
What Azure "flavour" of SQL have you provisioned as depending what you have deployed will depend on how you restore.
Do you have
Azure SQL DB
Azure SQL MI
Azure SQL VM
Azure SQL DB doesn't allow you to restore the backup using RESTORE DATABASE, you must use a BAKPAC file to restore the databases to that type of SQL service.
MI and VM you can restore via RESTORE DATABASE if you have the credential setup correctly.
January 17, 2024 at 12:43 pm
Thank you for your reply Ant.
That's a tough one for me, my boss set the database up in azure. I believe it 'Azure SQL DB' because it doesn't allow me to run the RESTORE DATABASE command and the 'Type' column has SQL database alongside the database.
Do you know what the process/commands to restore the database from a bacpac file?
I assume I need the bacpac file copied over to the blob storage to start with, where do I go from there?
Thank you
Paul.
January 17, 2024 at 12:56 pm
The following doc details the steps needed to import a bacpac to the server.
January 17, 2024 at 12:57 pm
Great, thank you.
January 17, 2024 at 1:21 pm
Just to emphasize one point, the bacpac is not like a backup. It's not transactionally aware. So, if you can't quiet the database, meaning stop transactions against it, that you're creating a bacpac from, I'd suggest restoring a backup to another location, then make the bacpac from there. If there are active transactions as the bacpac gets created, you may see errors when you attempt to deploy it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 17, 2024 at 1:26 pm
Thank you Grant.
Yes, that's not an issue for me, there's nothing connected to my database at the moment but I will keep that in mind for the future.
Just trying to learn how it's done before I decide on the best way to migrate the DB over.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply