The only way to create a SQL Server database snapshot is to use Transact-SQL. SQL Server Management Studio does not support the creation of database snapshots.
Syntax:
The source database, which can use any recovery model, must meet the following prerequisites:
- The server instance must be running an edition of SQL Server that supports database snapshot. For information about support for database snapshots in SQL Server 2017.
- The source database must be online, unless the database is a mirror database within a database mirroring session.
- To create a database snapshot on a mirror database, the database must be in the synchronized mirroring state.
- The source database cannot be configured as a scalable shared database.
- The source database must not contain a MEMORY_OPTIMIZED_DATA filegroup.
Syntax:
CREATE DATABASE database_snapshot_name
ON
(
NAME =logical_file_name,
FILENAME ='os_file_name'
) [ ,...n ]
AS SNAPSHOT OF source_database_name;
ON
(
NAME =logical_file_name,
FILENAME ='os_file_name'
) [ ,...n ]
AS SNAPSHOT OF source_database_name;
Example:
create database AdventureWorks2014_Snapshot on
( NAME = AdventureWorks2014_Data, FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks2014_data_1800.ss' )
AS SNAPSHOT OF AdventureWorks2014;
GO
( NAME = AdventureWorks2014_Data, FILENAME =
'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\AdventureWorks2014_data_1800.ss' )
AS SNAPSHOT OF AdventureWorks2014;
GO
Comments
Post a Comment
Hi User,
Thanks for visiting My Blog and please provide your valuable feedback and subscribe for more updates. Please don't post any spam content or comments.
Thank You