Home > Workload Solutions > SQL Server > Guides > Design Guide—SQL Server 2022 Database Solution with Object Storage on Dell Hardware Stack > Backup and restore use case
T-SQL has been used to run backup and restore operations long before Microsoft SQL Server 2022. The newly introduced features allow BACKUP and RESTORE to have their locations set to external object storage which increases the reliability of backups by storing them across multiple fault domains while still maintaining accessibility. Database Administrators no longer need to be concerned about running out of storage capacity either, because Dell’s Elastic Cloud Storage can be scaled to meet their storage needs. Dell’s ECS is secure, and it requires the appropriate credentials to perform backup or restore operations for Microsoft SQL Server 2022 through the S3 protocol. This use case was validated by simulating how a backup and restore procedure would work using the S3 protocol and Dell ECS.
The credentials used for connecting with S3 compatible storage require an appropriate storage URL and user credentials. Credentials that are created can be observed in the sys.credentials table. The following is an example of the process of creating credentials:
CREATE CREDENTIAL [s3://<ECS Bucket URL>]
WITH IDENTITY = ‘S3 Access Key’,
SECRET = ‘sqluser:<Username>/<Password>’;
Microsoft SQL Server 2022 allows users to directly store their backups in external object storage supported by the S3 protocol. A single query is all that is needed to back up the database to Dell’s Elastic Cloud Storage once the credentials have been confirmed. The query looks as follows:
BACKUP DATABASE <DatabaseName>
TO URL = ‘s3://<ECS Bucket URL>/<FolderName>/<Filename>
WITH FORMAT, COMPRESSION, STATS = 10,
NAME = ‘S3 backup to Dell ECS’;
The following snippet shows the process of backing up a database into Dell’s Elastic Cloud Storage using S3 and SQL:
It is also possible to restore a database directly from a backup file located in external S3 compatible storage without copying the data locally. A single query can specify the location of the database file to restore and the destination for the restored database.
The RESTORE FILELISTONLY command can be used to verify that the backup files are available in object storage and the database admin can verify the sanctity of the backup file along with its metadata. This query can be used as follows:
RESTORE FILELISTONLY
FROM URL = ‘s3://<ECS Bucket URL>/<FolderName>/<Filename>;
The following image shows the results of running this query in Microsoft SQL Server 2022:
Use the following query to restore the database from a file located in object storage:
RESTORE DATABASE <DatabaseName>
FROM URL = ‘s3://<ECS Bucket URL>/<FolderName>/<Filename>
WITH
MOVE N’<LogicalName1>’ TO N’/var/opt/mssql/data/<Destination FileName1>,
MOVE N’<LogicalName2>’ TO N’/var/opt/mssql/data/<Destination FileName2>,
MOVE N’<LogicalName3>’ TO N’/var/opt/mssql/data/<Destination FileName3>,
REPLACE, STATS = 10;
Database restoration with a backup file in ECS is demonstrated below: