-- 创建数据库快照
CREATE DATABASE 数据库快照名称 ON
(NAME = 数据库文件名称, FILENAME =
'E:/SQL2005/MSSQL.1/MSSQL/Data/数据库快照文件名称.ss' )
AS SNAPSHOT OF 数据库名称;
GO
-- 恢复数据库快照
RESTORE DATABASE 数据库名称 FROM DATABASE_SNAPSHOT = 数据库快照名称
GO
-- 删除数据库快照
DROP DATABASE 数据库快照名称
--示例1:
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'myDB.mdf', LOWER(physical_name)) - 1)
FROM myDB.sys.master_files
WHERE database_id = 7 AND file_id = 1);
--print @data_path
-- execute the CREATE DATABASE statement
-- select * from master.sys.master_files
EXECUTE (
'CREATE DATABASE myDB_snapshot0600 ON
( NAME = myDB, FILENAME = '''+ @data_path + 'myDB_0600.ss'')
AS SNAPSHOT OF myDB');
--