SQL Server 2023 Backup and Restore Options
-
Written By Charanjeet Kaur
-
Approved By Kuljeet Singh
-
Updated on 22 Aug, 2023 -
Min Reading 3 Min - Share
Summary: Last year in November, Microsoft announced the next major version of SQL Server called SQL Server 2022, available in private preview. SQL Server 2022 has exciting new features to provide consistent database experiences from the cloud to on-premises and even the Azure Edge. This blog provides a brief overview of the three most important features of SQL Server 2022. Most importantly, the blog discusses about backup and restore capabilities.
With every new version release, capabilities have been included in SQL Server to connect it to the cloud. However, Microsoft calls SQL Server 2023 the most cloud-connected release yet.
Essentially, SQL Server 2023 is Azure-enabled, allowing users to leverage the power of the cloud even when the database runs on-premises. In addition, the new SQL Server version makes it easy to integrate with the cloud with the new link feature in Azure SQL Managed Instance. Also, the Azure Synapse link in SQL 2023 enables analytics over on-premises operational data.
An Overview of SQL Server 2023 Features
Let’s explore in brief the three new major features available in SQL Server 2023:
- Disaster Recovery in the Cloud
The traditional way of disaster recovery (DR) has been a pain point for users. There’s a lot of time and cost involved in setting up a DR site. A disaster recovery site requires building and managing an offsite data center that must be continually updated and frequently tested. But, with SQL Server 2023, you can now failover SQL Server to an Azure SQL Managed Instance – no setup or maintenance is required.
You can even fail back (i.e., restore) the managed instance databases on the cloud to SQL Server on-prem.
- Near Real-time Analytics
In previous SQL Server versions, you need to set up ETL pipelines to move data from on-prem SQL Server databases to a warehouse or an analytics system like Synapse. Since configuring and running ETL pipelines requires a lot of work, the data is often out of date and prevents real-time insights. However, with SQL Server 2023, change feed capability captures changes from the transaction log and feeds them into SQL pools for Synapse in real-time.
- Azure Purview Integration
SQL Server 2023 comes integrated with Azure Purview, a new data governance system that helps break down data silos. This integration allows capturing metadata by automatically scanning on-premises SQL Server. Also, it helps configure and control specific rights to SQL Server and more.
Backup and Restore Options
Following are the two backup and restore options provided by SQL Server 2023:
- S3 Compatible Storage Systems
Connection to S3 compatible storage options in SQL Server 2023 enables fast backup and restore capabilities. You can read more about it in a post by Bob Ward, a principal architect at Microsoft. He states:
“We have new extensions to the T-SQL language to support data virtualization and backup/restore with S3 compatible storage systems.“ – Bob Ward, principal architect at Microsoft
- Backup in Cloud and Restore Database in On Premises SQL Server
The cloud-connected SQL Server 2023 allows backing up a database from the Azure SQL managed instance and restoring it to on-premises SQL Server. Here’s a demo where the ‘WideWorldImporters’ database is backed up in Azure SQL Managed Instance.
Once the backup procedure is complete, you can easily restore the backed up database from Azure SQL managed instance to on-prem SQL Server using the following T-SQL query:
References Used
Besides the SQL Server 2023 features covered in this post, the new version release also provides several other exciting features, including built-in query intelligence for enhanced performance and scalability, Ledger with Blockchain functionality for security, etc. For further information about SQL Server 2023, check out these references:
Conclusion
This blog provided a brief overview of the major features of SQL Server 2023 – disaster recovery, analytics, and Azure purview. And it discussed the backup and restore options available in the new SQL Server version. For now, SQL Server 2023 is available for private preview. If you are interested in trying out the new rolled-out version, apply for the private preview by clicking on this link.
Was this article helpful?
YES0 
NO About The Author
Charanjeet is a Technical Content Writer at Stellar®who specializes in writing about databases, e-mail recovery, and e-mail migration solutions. She loves researching and developing content that helps database administrators, organizations and novices to fix multiple problems related to MS SQL and MySQL databases and Microsoft Exchange.
