adminbd

Записи по mssql,oracle,windows и linux

Have a Question?

If you have any question you can ask below or enter what you are looking for!

Создание n баз из одного бэкапа под разными именами

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

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>