#title Create Snapshot
[[TableOfContents]]


{{{
declare 
    @sql nvarchar(4000)
,   @dbname nvarchar(100)

set @dbname = 'test'

set @sql = '
if db_id(''@dbname_snapshot'') is not null
    drop database @dbname_snapshot

create database @dbname_snapshot on 
 (name = @dbname, filename = ''d:\snapshot\@dbname_snapshot.ss'')
as snapshot of @dbname
'
set @sql = replace(@sql, '@dbname', @dbname)
print @sql
}}}

{{{
/*
select name
from test.sys.database_files
where type = 0
*/

if db_id('test_snapshot') is not null
    drop database test_snapshot

create database ddon_log_snapshot on 
 (name = test1, filename = 'd:\snapshot\test1_snapshot.ss')
,(name = test2, filename = 'd:\snapshot\test2_snapshot.ss')
as snapshot of ddon_log
}}}



==== 스냅샷 만드는 스크립트 ====
{{{
DECLARE 
	@DBName nvarchar(500)
,	@DT nchar(8)
,	@SnapshotPath nvarchar(4000)
,	@DBSuffixes nvarchar(255)
,	@SQL nvarchar(4000);

--<사용자설정>
SET @DBName = N'데이터베이스명';
SET @DBSuffixes = N'접미어' --+ CONVERT(NCHAR(8), GETDATE(), 112);
SET @SnapshotPath = N'파일경로(파일명제외, 예)C:\Snapshot\)';
--</사용자설정>

SET @SQL = 	
	N'USE ' + @DBName + CHAR(13) + CHAR(10) + 
SET @SQL = 	
	N'USE ' + @DBName + CHAR(13) + CHAR(10) + 
	--N'SELECT name FROM sys.database_files WHERE type = 0 order by file_id'; --2008 r2 version
    '
    select
        substring(reverse(substring(reverse(physical_name), 1, charindex(''\'', reverse(physical_name))-1)), 1, charindex(''.'', [파일명.확장자])-1) name
    from (
        SELECT 
            name logical_name
        ,   physical_name
        ,   file_id
        ,   reverse(substring(reverse(physical_name), 1, charindex(''\'', reverse(physical_name))-1)) [파일명.확장자]
        FROM sys.database_files WHERE type = 0 
    ) t
    order by file_id
    ' --2012 sp1 version, logical_name으로 되어야 하는데, physical_name으로 된다. bug다.
      --아.. 이것도 아니다. 일부의 DB만 그렇다. 뭐지?


CREATE TABLE #Temp(name nvarchar(500));
INSERT #Temp EXEC(@SQL);

IF exists (select * from sys.databases
where name = @DBName + @DBSuffixes
and source_database_id = db_id(@DBName))
BEGIN
	SET @SQL = N'USE ' + 'master' + CHAR(13) + CHAR(10) + 
		N'DROP DATABASE ' + @DBName + @DBSuffixes;
	EXEC(@SQL);
END
	
SET @SQL = '';
SELECT @SQL = 
	@SQL +
	 N', (NAME = ' + name + N', FILENAME = ''' + @SnapshotPath + name + @DBSuffixes + '.ss'')' + 
	 CHAR(13) + CHAR(10)
FROM #Temp

SET @SQL = STUFF(@SQL, 1, 1, '')
SET @SQL = 
	N'CREATE DATABASE ' + @DBName + @DBSuffixes + N' ON ' + CHAR(13) + CHAR(10) + 
	@SQL + N'AS SNAPSHOT OF ' + @DBName 

PRINT @SQL
DROP TABLE #Temp;
EXEC(@SQL)
}}}

==== 적용 대상 ====
 * Microsoft SQL Server 2005
 * Microsoft SQL Server 2008