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
Last activity: Jul 22, 2025, 07:02 AM