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;
Create procedure User_sp_detach_attach
    @db_name varchar(max),
    @new_mdf_path varchar(max),
    @new_ldf_path varchar(max)
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';
  • 3
    Is there any reason you are doing detach/attach? Why not just take a backup, copy the files and restore it elsewhere? Assuming you are running 2005 (Enterprise) or higher, you'll get backup compression also, which will help if the DBs are large. Dec 8 '14 at 19:38
  • 1
    Ugh, please listen to @Kris. Use backup and restore. Detach and attach is like changing a flat tire while you're still moving. Dec 8 '14 at 20:35
  • I have to do this when I run out of space on a particular drive. So I add a new drive on the same server, detach the database, move the files to the new drive and attach the database.
    – Dipan Shah
    Dec 8 '14 at 21:05
  • If you are doing this that frequently you need to seriously reconsider how you allocate drive space. This should be a once a GREAT while, not something you do so frequently you need a procedure for.
    – Sean Lange
    Dec 8 '14 at 21:37
  • We have many servers. Each server with hundreds of database. Attach and detach is the fastest way for us to manage this move. I just wanted to know if it is possible to incorporate all the steps in a single sp/batch file/powershell script.
    – Dipan Shah
    Dec 8 '14 at 21:44

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.

  • 2
    Alternatively, he could use MIRROR TO DISK = 'C:\backupsgohere' in his backup syntax as xp_cmdshell does open up a few security holes. Dec 8 '14 at 19:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.