SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE RESTORE_BD_BY_BACKUP
@basename [nvarchar] (200)
, @path_to_restore [nvarchar] (4000)
, @backup_path_and_file [nvarchar] (4000)
, @Nt_UserName [nvarchar] (50) = NULL
as
if right(@path_to_restore,1) <> '\' or right(@path_to_restore,1) <> '/'
set @path_to_restore = @path_to_restore + '\'
declare @SQL [nvarchar] (4000)
declare @Log_LogicalName [nvarchar](128)
declare @Data_LogicalName [nvarchar](128)
/*Вытаскиваем логические имена*/
create table #t
(
LogicalName nvarchar(128)
, PhysicalName nvarchar(260)
, Type char(1)
, FileGroupName nvarchar(128)
, [Size] numeric(20,0)
, [MaxSize] numeric(20,0)
)
set @SQL = N'RESTORE FILELISTONLY from DISK = N''' + @backup_path_and_file + ''''
insert #t exec(@SQL)
select @Log_LogicalName = LogicalName from #t where Type = 'L'
select @Data_LogicalName = LogicalName from #t where Type = 'D'
drop table #t
/*Вытаскиваем логические имена*/
set @SQL =
'RESTORE DATABASE [' + @basename + ']'
+ ' FROM DISK = N''' + @backup_path_and_file + ''''
+ ' WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY , REPLACE '
+ ', MOVE N''' + @Log_LogicalName + ''' TO N''' + @path_to_restore + @basename + '_log.ldf'''
+ ', MOVE N''' + @Data_LogicalName + ''' TO N''' + + @path_to_restore + @basename + '.mdf'''
exec(@SQL)
set @SQL = @basename
+ ': Востановлена из бэкапа - ' + @backup_path_and_file
exec p_insert_t_event_log @SQL
if not(@Nt_UserName is NULL)
BEGIN
set @SQL = ' exec master..xp_cmdshell N''net send ' + @Nt_UserName + ' " ' + 'База '
+ @basename + ' востановлена из бэкапа : ' + @backup_path_and_file + ' "'''
exec(@SQL)
END
/*
exec PAM_WORK_..RESTORE_BD_BY_BACKUP
'Bd_cont90'
,'D:\DATA\'
,'F:\BackUp\EMPTY_BASE\BDCont240.bk'
,'user0700_11'
/*
(2 row(s) affected)
Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
(1 row(s) affected)
(3 row(s) affected)
*/
*/
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Запускаю процедуру:
exec PAM_WORK_..RESTORE_BD_BY_BACKUP
'Bd_cont90'
,'D:\DATA\'
,'F:\BackUp\EMPTY_BASE\BDCont240.bk'
,'user0700_11'
Результат:
Server: Msg 3101, Level 16, State 2, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
(1 row(s) affected)
(3 row(s) affected)
Каким образом отключить всех пользователей от БД перед восстановлением?