declare @name nvarchar(56)
declare @cmd nvarchar(512)
DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FOR
select '0'+right(servername,2) as name from s0055.rkur_dbastat.dbo.ServerName
where is_disable = 0
--and servername = 's1027'
OPEN @Cursor
FETCH NEXT FROM @Cursor INTO @name
WHILE (@@FETCH_STATUS = 0)
BEGIN
print @name
set @cmd = 'RESTORE DATABASE [planograms'+@name+']
FROM DISK= N''H:\MSSQL\Backup\etalon_planograms_20150112.bak''
WITH FILE = 1
,MOVE N''Planograms'' TO N''H:\MSSQL\Data\planograms'+@name+'.mdf''
,MOVE N''Planograms_log'' TO N''H:\MSSQL\Data\planograms'+@name+'_1.ldf''
, NOUNLOAD
, STATS = 10
'
print @cmd
--exec (@cmd)
FETCH NEXT FROM @Cursor INTO @name
end
Хранимая процедура для добавления новой бд
Как использовать
use master
go
exec CreateNewPlanograms
@name='999'
код процедуры
USE [master]
GO
/****** Object: StoredProcedure [dbo].[CreateNewPlanograms] Script Date: 01/12/2015 12:05:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Курьянов Руслан
-- Create date: 20150112
-- Description: 0.2
-- =============================================
CREATE PROCEDURE [dbo].[CreateNewPlanograms]
-- Add the parameters for the stored procedure here
@name nvarchar(3) = ''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
-- deploy db
if len(@name)<=2
begin
print 'введи правильное значение @name, формат @name=''NNN'', пример @name=''001'' или @name=''029'' и т.п'
print '
пример правильного скрипта(сделаем бд для магазина 19):
-----------------------------------
use master
go
exec CreateNewPlanograms
@name=''019''
'
end
else
begin
declare @cmd nvarchar(512)
print '@name='+@name
print '--------------------------------------'
set @cmd = 'RESTORE DATABASE [planograms'+@name+']
FROM DISK= N''H:\MSSQL\Backup\etalon_planograms_20150112.bak''
WITH FILE = 1
,MOVE N''Planograms'' TO N''H:\MSSQL\Data\planograms'+@name+'.mdf''
,MOVE N''Planograms_log'' TO N''H:\MSSQL\Data\planograms'+@name+'_1.ldf''
, NOUNLOAD
, STATS = 10
'
print 'будет выполнен скрипт'
print '|'
print '|'
print 'V'
print ''
print @cmd
print ''
print '--------------------------------------'
print 'результат выполнения скрипта'
print '|'
print '|'
print 'V'
print ''
exec (@cmd)
end
END
Similar Posts: