Sql-server – Can we create a stored procedure to copy detached .mdf and .ldf files from original location to new location

sql server

I am trying to create a script to attach database, move .mdf and .ldf files to new location and then attach the database. I created a stored procedure to detach and attach the database. Can I get a suggestion as to how I can move the files from the script itself?

Use master;
Go
Create procedure User_sp_detach_attach
    @db_name varchar(max),
    @new_mdf_path varchar(max),
    @new_ldf_path varchar(max)
As
Set NoCount On;
declare @db_id smallint;
declare @mdf_file varchar(max);
declare @ldf_file varchar(max);
declare @ori_mdf nvarchar(max);
declare @ori_ldf nvarchar(max);
declare @new_mdf_file nvarchar(max);
declare @new_ldf_file nvarchar(max);
declare @sql varchar(max);
set @db_id=DB_ID(@db_name);
set @mdf_file=@db_name+'.mdf';
set @ldf_file=@db_name+'_log.ldf';
select @ori_mdf=physical_name from sys.master_files where database_id=@db_id and file_id=1;
select @ori_ldf=physical_name from sys.master_files where database_id=@db_id and file_id=2;
set @new_mdf_file=@new_mdf_path+'\'+@mdf_file;
set @new_ldf_file=@new_ldf_path+'\'+@ldf_file;
print 'New location of .mdf file: '+@new_mdf_file;
print 'New location of .ldf file: '+@new_ldf_file;
EXEC sp_detach_db @db_name;
--Move files
Set @sql='CREATE DATABASE ' + @db_name + ' ON (FILENAME=''' + @new_mdf_file + '''), (FILENAME=''' + @new_ldf_file + ''') FOR ATTACH;'
Print @sql;
Exec (@sql);
Print 'Moved and attached successfully';
Go

Best Answer

Well you can use xp_cmdshell, write (or find and use) a CLR routine to do file copies and things like that. For example, the unrecomnended use of xp_cmdshell.

EXEC xp_cmdshell "COPY \\share\path\file.mdf \\othershare\otherpath\file.mdf"

However, you would be better just making a backup (which from SQL Server 2008 R2 supports compressed backups) and you will get a faster transfer than detaching and copying files. Not to mention the risks of detaching a file and not being able to reattach.

Recommendation: Use BACKUP and RESTORE.

Related Question