_대문 | 방명록 | 최근글 | 홈피소개 | 주인놈 |
FrontPage › CreateSnapshot
|
|
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)
|
다정스런 말은 시원한 물보다도 더 시원하게 목마름을 축여준다. |