Moving entire SQL Server user databases

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">

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.