CREATE Procedure NewDoreh
@Path_DB varchar(500)
As
Declare @CountDattabase int
Declare @Name_DB nvarchar(50)
Declare @Path_Data_File nvarchar(500)
Declare @Path_Log_File nvarchar(500)
Declare @SysName varchar(10)
Declare @DFName varchar(14)
Declare @LFName varchar(14)
SET @SysName='databasename'
select @CountDattabase=count(name) from master.dbo.sysdatabases where name like '%databasename%'
SET @DFName=@SysName+'_Data'
SET @LFName=@SysName+'_Log'
SET @Name_DB=@SysName+ltrim(rtrim(Str(@CountDattabase+1)))
SET @Path_Data_File=rtrim(@Path_DB)+@Name_DB+'.mdf'
SET @Path_Log_File=rtrim(@Path_DB)+@Name_DB+'.ldf'
Declare @SQL_ Nvarchar(4000)
SET @SQL_= N'
Backup DataBase '+@SysName+ltrim(Str(@CountDattabase))+' To Disk='''+@Path_DB+'Bdatabasename.Bak'''
exec sp_executesql @SQL_
SET @SQL_= N'RESTORE DATABASE '+@Name_DB+' FROM DISK ='''+ @Path_DB+'Bdatabasename.bak'' WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY , MOVE '''+@DFName +''' TO '''+@Path_Data_File+''', MOVE '''+@LFName+''' TO '''+ @Path_Log_File+''''
exec sp_executesql @SQL_
select 1
GO