데이터베이스의 복사본을 만들고 싶을 때 항상 새 빈 데이터베이스를 만든 다음 기존 데이터베이스의 백업을 그 안에 복원합니다. 하지만 이것이 정말 오류가 가장 적고, 가장 복잡하지 않으며, 가장 효율적인 방법인지 궁금합니다.
빈 데이터베이스 생성 단계를 건너뛸 수 있습니다. 복원 프로세스의 일부로 새 데이터베이스를 만들 수 있습니다.
이것은 실제로 데이터베이스를 복제하는 가장 쉽고 가장 좋은 방법입니다. 백업 및 복원 프로세스를 SQL Server 관리 스튜디오를 통해 실행하는 대신 스크립팅하여 오류를 제거할 수 있습니다.
다른 두 가지 옵션도 살펴볼 수 있습니다:
필요한 경우 백업 및 복원 및 자동화를 계속 사용하는 것이 좋습니다.
다음은 제가 과거에 사용했던 동적 SQL 스크립트입니다. 추가 수정이 가능하지만 기본 사항을 알려드릴 것입니다. 저는 관리 스튜디오를 사용할 때 발생할 수 있는 실수를 피하기 위해 스크립트를 작성하는 것을 선호합니다:
예제 코드
; 선언 @OldDB varchar(100) Declare @NewDB varchar(100) vchBackupPath varchar(255) 선언 쿼리 varchar(8000) 선언/구현할 테스트 코드 OldDB = 'Pubs'를 선택합니다; NewDB = 'Pubs2'를 선택합니다; 선택 @vch백업경로 = '\dbserver\C$\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\pubs.bak' /
nocount를 설정합니다;
쿼리 = '데이터베이스 만들기 ' + @NewDB 선택 exec(@query)
Select @query = ' 선언 @vBAKPath varchar(256) 선언 @oldMDFName varchar(100) 선언 @ldfName varchar(100) 선언 @newMDFPath varchar(100) 선언 @newLDFPath varchar(100) 선언 @restQuery varchar(800)
select @vBAKPath = ''' + @vchBackupPath + ''' select @oldLDFName = 이름 = ' + @OldDB +'.dbo.sysfiles에서 파일 이름이 ''%.ldf%''와 같은 이름; select @oldMDFName = 이름 = ' + @OldDB +'.dbo.sysfiles에서 파일 이름이 ''%.mdf%''와 같은 파일 이름; select @newMDFPath = physical_name from ' + @NewDB +'.sys.database_files where type_desc = ''ROWS'' select @newLDFPath = physical_name from ' + @NewDB +'.sys.database_files where type_desc = ''LOG''
select @restQuery = ''RESTORE DATABASE ' + @NewDB +. ' FROM DISK = N'' + '''''''' + @vBAKpath + '''''''' + '' WITH MOVE N'' + ''''''' + @oldMDFName + '''''''' + '' TO N'' + '''''' + @newMDFPath + '''''''+#39;''. '', MOVE N'' + '''''' + @ldfName + ''''''''' + '' TO N'' + '''''' + @newLDFPath + '''''''' + '', 명사로드, 바꾸기, 통계 = 10''
exec(@restQuery) --print @restQuery'
exec(@query)
::================ BackUpAllMyDatabases.cmd ============= START
::BackUpAllMyDatabases.cmd
:: COMMAND LINE BATCH SCRIPT FOR TAKING BACKUP OF ALL DATABASES
::RUN THE SQL SCRIPT VIA THE COMMAND LINE WITH LOGGING
sqlcmd -S localhost -e -i "BackUpAllMyDatabases.sql" -o Result_Of_BackUpAllMyDatabases.log
::VIEW THE RESULTS
Result_Of_BackUpAllMyDatabases.log
::pause
::================ BackUpAllMyDatabases.cmd ============= END
--=================================================BackUpAllMyDatabases.sql start
DECLARE @DBName varchar(255)
DECLARE @DATABASES_Fetch int
DECLARE DATABASES_CURSOR CURSOR FOR
select
DATABASE_NAME = db_name(s_mf.database_id)
from
sys.master_files s_mf
where
-- ONLINE
s_mf.state = 0
-- Only look at databases to which we have access
and has_dbaccess(db_name(s_mf.database_id)) = 1
-- Not master, tempdb or model
--and db_name(s_mf.database_id) not in ('Master','tempdb','model')
group by s_mf.database_id
order by 1
OPEN DATABASES_CURSOR
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
declare @DBFileName varchar(256)
set @DBFileName = @DbName + '_' + replace(convert(varchar, getdate(), 112), '-', '.') + '.bak'
--REMEMBER TO PUT HERE THE TRAILING \ FOR THE DIRECTORY !!!
exec ('BACKUP DATABASE [' + @DBName + '] TO DISK = N''D:\DATA\BACKUPS\' +
@DBFileName + ''' WITH NOFORMAT, INIT, NAME = N''' +
@DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 100')
FETCH NEXT FROM DATABASES_CURSOR INTO @DBName
END
CLOSE DATABASES_CURSOR
DEALLOCATE DATABASES_CURSOR
--BackUpAllMyDatabases==========================end
--======================RestoreDbFromFile.sql start
-- Restore database from file
-----------------------------------------------------------------
use master
go
declare @backupFileName varchar(100), @restoreDirectory varchar(100),
@databaseDataFilename varchar(100), @databaseLogFilename varchar(100),
@databaseDataFile varchar(100), @databaseLogFile varchar(100),
@databaseName varchar(100), @execSql nvarchar(1000)
-- Set the name of the database to restore
set @databaseName = 'ReplaceDataBaseNameHere'
-- Set the path to the directory containing the database backup
set @restoreDirectory = 'ReplaceRestoreDirectoryHere' -- such as 'c:\temp\'
-- Create the backup file name based on the restore directory, the database name and today's date
@backupFileName = @restoreDirectory + @databaseName + '-' + replace(convert(varchar, getdate(), 110), '-', '.') + '.bak'
-- set @backupFileName = 'D:\DATA\BACKUPS\server.poc_test_fbu_20081016.bak'
-- Get the data file and its path
select @databaseDataFile = rtrim([Name]),
@databaseDataFilename = rtrim([Filename])
from master.dbo.sysaltfiles as files
inner join
master.dbo.sysfilegroups as groups
on
files.groupID = groups.groupID
where DBID = (
select dbid
from master.dbo.sysdatabases
where [Name] = @databaseName
)
-- Get the log file and its path
select @databaseLogFile = rtrim([Name]),
@databaseLogFilename = rtrim([Filename])
from master.dbo.sysaltfiles as files
where DBID = (
select dbid
from master.dbo.sysdatabases
where [Name] = @databaseName
)
and
groupID = 0
print 'Killing active connections to the "' + @databaseName + '" database'
-- Create the sql to kill the active database connections
set @execSql = ''
select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '
from master.dbo.sysprocesses
where db_name(dbid) = @databaseName
and
DBID <> 0
and
spid <> @@spid
exec (@execSql)
print 'Restoring "' + @databaseName + '" database from "' + @backupFileName + '" with '
print ' data file "' + @databaseDataFile + '" located at "' + @databaseDataFilename + '"'
print ' log file "' + @databaseLogFile + '" located at "' + @databaseLogFilename + '"'
set @execSql = '
restore database [' + @databaseName + ']
from disk = ''' + @backupFileName + '''
with
file = 1,
move ''' + @databaseDataFile + ''' to ' + '''' + @databaseDataFilename + ''',
move ''' + @databaseLogFile + ''' to ' + '''' + @databaseLogFilename + ''',
norewind,
nounload,
replace'
exec sp_executesql @execSql
exec('use ' + @databaseName)
go
-- If needed, restore the database user associated with the database
/*
exec sp_revokedbaccess 'myDBUser'
go
exec sp_grantdbaccess 'myDBUser', 'myDBUser'
go
exec sp_addrolemember 'db_owner', 'myDBUser'
go
use master
go
*/
--======================RestoreDbFromFile.sql