SQL Server Performance

Moving entire SQL Server user databases

Discussion in 'Contribute Your SQL Server Scripts' started by satya, Aug 22, 2003.

  1. satya Moderator

    Came thru this useful script which helped me, and to shre in the forum :

    Satya SKJ
  2. gaurav_bindlish New Member

    There was a need for restroing multiple copies of samedatabase at differnt locations. So I had written a similar script. I comapred the two and I guess mine has more error handling [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<pre>If exists(select * from sysobjects<br />where name = 'dbp_restore_bkup_diff_location'<br />and type = 'P')<br />drop procedure dbp_restore_bkup_diff_location<br />go<br /><br />/*********************************************************************************************<br />*problem --to create multiple databases from a single database backup *<br />*********************************************************************************************/<br /><br />create procedure dbp_restore_bkup_diff_location<br />@i_backupvarchar(8000),--Backup Location<br />@i_db_namesysname,--Name of Database to be Created<br />@i_data_locationvarchar(8000)--Location of backup file<br />as<br /><br />declare <br />@retcode int,<br />@l_bkup_file_nmvarchar(520),<br />@l_bkup_file_locationvarchar(520),<br />@l_indexint,<br />@l_exec_stmtvarchar(8000),<br />@l_Old_file_locationvarchar(520),<br />@l_New_file_locationvarchar(520),<br />@l_file_nmvarchar(520),<br />@l_file_locationvarchar(520),<br />@l_LogicalName varchar(256),<br />@l_exec_stmt_lenint<br /><br />select @l_index = 0,<br />@l_bkup_file_nm = @i_backup<br /><br /><br />if exists (select * from tempdb..sysobjects where name like '#bkup_file_details%' )<br />drop table #bkup_file_details<br /><br />set nocount on<br /><br />create table #bkup_file_details<br />(<br /> LogicalName varchar(256) NULL<br />,Old_PhysicalName varchar(520) NULL<br />,Type varchar(4) NULL<br />,FileGroupName sysname NULL<br />,Size int NULL<br />,MaxSize decimal(20,0) NULL<br />,New_PhysicalName varchar(520) NULL<br />)<br /><br />/* Check if the backupfile Exists */<br /><br />select @l_index = charindex('', @l_bkup_file_nm)<br />while(@l_index &lt;&gt; 0)<br />begin<br />select @l_bkup_file_nm = right(@l_bkup_file_nm, datalength(@l_bkup_file_nm)- @l_index)<br />select @l_index = charindex('', @l_bkup_file_nm)<br />end<br /><br />select @l_bkup_file_location = replace(@i_backup, @l_bkup_file_nm, '')<br /><br />exec @retcode = master..sp_msexists_file @l_bkup_file_location , @l_bkup_file_nm<br /><br />if @retcode &lt;&gt; 1 <br />begin<br />print 'Backup File does not exist'<br />return<br />end<br /><br /><br /><br />/* Check if the Database Exists */<br />if exists(select * from master..sysdatabases<br />where name = @i_db_name)<br />begin<br />print 'Database already Present'<br />return<br />end<br /><br />/*Create Directory for Database Files*/<br /><br />exec ("master..xp_cmdshell 'md " + @i_data_location + "', no_output" )<br /><br /><br />/*determine the number and names of the files in the backup.*/<br /><br />select @l_exec_stmt = "restore filelistonly from disk = '" + @i_backup + "'"<br />insert into #bkup_file_details(LogicalName, Old_PhysicalName, Type, FileGroupName,Size,MaxSize)<br />exec (@l_exec_stmt)<br /><br />/*Update File path with the new Data Location*/<br /><br />select @retcode = 0<br /><br />declare file_details scroll cursor <br />for select Old_PhysicalName<br />from #bkup_file_details<br /><br />open file_details <br /><br />while @retcode = 0<br />begin<br />fetch next from file_details <br />into @l_Old_file_location<br />if(@@fetch_status &lt;&gt; 0)<br />break<br />select @l_file_nm = @l_Old_file_location<br />select @l_index = charindex('', @l_file_nm)<br />while(@l_index &lt;&gt; 0)<br />begin<br />select @l_file_nm = right(@l_file_nm, datalength(@l_file_nm)- @l_index)<br />select @l_index = charindex('', @l_file_nm)<br />end<br />select @l_New_file_location = @i_data_location + "" + @l_file_nm<br />Update #bkup_file_details<br />set New_PhysicalName = @l_New_file_location <br />where Old_PhysicalName = @l_Old_file_location<br />end<br />close file_details <br />deallocate file_details <br /><br />/*restore Database*/<br /><br />select @l_exec_stmt = "restore database " + @i_db_name<br /> + " from disk = '" + @i_backup<br /> + "' with recovery"<br /><br />declare file_details scroll cursor <br />for select LogicalName,<br />New_PhysicalName<br />from #bkup_file_details<br /><br />open file_details <br /><br />while @retcode = 0<br />begin<br />fetch next from file_details <br />into @l_LogicalName,<br />@l_file_location<br /><br />if(@@fetch_status &lt;&gt; 0)<br />break<br />select @l_exec_stmt = @l_exec_stmt + ", move '" + @l_LogicalName + "' to '" + @l_file_location + "'" <br />end<br />close file_details <br />deallocate file_details <br /><br />exec (@l_exec_stmt)</pre><br /><br /><br />Gaurav<br /><i>Moderator<br /><font size="1">Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard</i></font id="size1">
  3. Haywood New Member

    Thanks for the script Gaurav, I needed something like this for a new request. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Hopefully here in a couple days I'll have a port of this to 2005 and will post it when completed.

Share This Page