Contents

1 る 襷 ろ襴渚
2 



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



[edit]

1 る 襷 ろ襴渚 #

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)

[edit]

2 #

  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008