I'm trying to backup a database from SQL Server on Azure and restore it in a different tenant, but I'm running into issues. My DBA can create a .bak file using SQL Server Management Studio, but when we try to restore it in another tenant, the 'restore' option isn't available. He's able to create databases and make changes, yet restoring seems out of reach. I've come across a lot of conflicting advice online, from claims that it can't be done to overly complicated processes involving multiple setups. That can't be the case, right? What am I missing here?
1 Answer
Are you using SQL Server on a VM or Azure SQL Database? Azure SQL Database doesn't support .bak backups; it uses .bacpac files instead. If you're trying to go from SQL on an Azure VM to Azure SQL, you might have to transfer the data table by table using tools like SQL Import Export Wizard or PowerShell scripts from dbatools.io.
I think you might be onto something. The database admin isn't really in control, so he might not know exactly what setup we have. I've set up a standard SQL server and created a database in it, but without the 'restore' option, we're stuck. Is there an easy way to figure out exactly what type of SQL setup I'm working with?