Sample Header Ad - 728x90

Move SQL Server database files to Azure storage account container

1 vote
1 answer
147 views
I'm trying to move SQL Server database files into an Azure storage account container. Creating or restoring databases in container storage works flawlessly, but moving fails. First, I create a credential with the SAS token (permissions: rwdl):
CREATE CREDENTIAL [https://foobar.blob.core.windows.net/databases]  
WITH IDENTITY='SHARED ACCESS SIGNATURE', SECRET = 'secretsecretsecret'
After that I'm altering the existing database and point the both data and log file to the storage account:
ALTER DATABASE [AdventureWorks] MODIFY FILE 
( NAME = AdventureWorks, FILENAME = 
'https://foobar.blob.core.windows.net/databases/AdventureWorks.mdf ');  

ALTER DATABASE [AdventureWorks] 
MODIFY FILE ( NAME = AdventureWorks_log, FILENAME = 
'https://foobar.blob.core.windows.net/databases/AdventureWorks.ldf ');
After I copied the MDF and LOG file to the
container, I restart the database:
ALTER DATABASE [AdventureWorks] SET OFFLINE WITH ROLLBACK IMMEDIATE;    

ALTER DATABASE [AdventureWorks] SET ONLINE;
But I'm getting this error:
Msg 5181, Level 16, State 5, Line 8

Could not restart database "AdventureWorks". Reverting to the previous status.

Msg 5069, Level 16, State 1, Line 8

ALTER DATABASE statement failed.
However, if I'm creating a new database and point the files to the storage account, it works:
CREATE DATABASE [WorkingAdventure] ON PRIMARY 
( NAME = N'WorkingAdventure', FILENAME =
 N'https://foobar.blob.core.windows.net/databases/WorkingAdventure.mdf ', 
SIZE = 1GB, MAXSIZE = UNLIMITED, FILEGROWTH = 50MB) 
LOG ON 
( NAME = N'WorkingAdventure_log', FILENAME =
 N'https://foobar.blob.core.windows.net/databases/WorkingAdventure.ldf ', 
SIZE = 2MB, MAXSIZE = 250MB, FILEGROWTH = 1MB)
Restoring a backup works, too. I'm trying to avoid creating a backup from the databases and restore them into a storage account, because of the database sizes (in total about 20TB).
Asked by s3b (11 rep)
Aug 23, 2024, 02:11 PM
Last activity: Jul 22, 2025, 07:02 AM