数据库的创建： BackUp-->Restore方式

--TEST:usp_WebAdmin_SaaSPortal_CustomerCreate 'bpm_622_online','bpm_9309' 
--select physical_name from sys.database_files where name='BPM_622_online' and type=0 
CREATE      procedure dbo.usp_WebAdmin_SaaSPortal_CustomerCreate 
( 
    @primary_database_name varchar(max)='' 
    ,@customer_database_name varchar(max)='' 
 ,@customer_id nvarchar(100) 
 ,@company_name nvarchar(100) 
) 
as 
 
 
declare @modelDBname varchar(100) 
declare @customerDBname varchar(100) 
 
select @modelDBname=@primary_database_name 
select @customerDBname=@customer_database_name 
 
 
declare @nameLogical nvarchar(100) 
SELECT @nameLogical=name FROM sys.master_files WHERE database_id = DB_ID(@primary_database_name) and type_desc='ROWS' 
 
 
---当前数据库的物理路径 
declare @backup_Path nvarchar(100) 
select @backup_Path=substring(physical_name,0,len(physical_name)+2-charindex('\',REVERSE(physical_name))) from sys.database_files where type=0 
 
 
declare @backup_LogicalDeviceName nvarchar(100) 
declare @backup_PhysicalDeviceName nvarchar(100) 
select @backup_LogicalDeviceName = @modelDBname+'_BackUp' 
select @backup_PhysicalDeviceName = @backup_Path+@backup_LogicalDeviceName+'.bak' 
 
 
 
if not exists(select * from msdb..backupmediafamily where logical_device_name=@backup_LogicalDeviceName) 
begin 
 EXEC sp_addumpdevice 'disk',@backup_LogicalDeviceName, @backup_PhysicalDeviceName 
 print 'add disk:'+@backup_LogicalDeviceName 
end 
 
BACKUP DATABASE @modelDBname  
 TO @backup_LogicalDeviceName 
 WITH INIT; 
 
RESTORE FILELISTONLY  
   FROM @backup_LogicalDeviceName ; 
 
if not exists(select * from sys.databases where name=@customerDBname and state=0) 
begin 
 declare @logicalname_data varchar(100)     
 declare @physicalname_data varchar(100)     
 declare @logicalname_log varchar(100)     
 declare @physicalname_log varchar(100)     
 select @logicalname_data=@nameLogical;     
 select @physicalname_data = @backup_Path+@customerDBname+'.mdf';     
 select @logicalname_log=@nameLogical+'_Log'     
 select @physicalname_log = @backup_Path+@customerDBname+'_LOG.LDF';     
 
 
     
 RESTORE DATABASE @customerDBname      
    FROM @backup_LogicalDeviceName      
    WITH REPLACE,MOVE @logicalname_data TO @physicalname_data,     
    MOVE  @logicalname_log TO @physicalname_log;     
     
 
 DELETE FROM CUSTOMER_DATABASE WHERE CUSTOMER_DATABASE=@customer_database_name; 
 INSERT INTO CUSTOMER_DATABASE (CUSTOMER_ID,COMPANY_NAME,CUSTOMER_DATABASE) VALUES(@customer_id,@company_name,@customer_database_name); 
 
 
end