SQLServerPedia

I would backup the database and restore it using the WITH MOVE option. For the sake of illustration however, you could use SQLCMD to run a script as follows (assuming you are working in an environment that allows xp_cmdshell to be run, AND assuming that the SQL Server service is running under an account with the rights to move the files on the operating system (big IFs in most places))…

Script file contents:

declare @cmd varchar(2000);
select 'drop connected users'
while (select count(*) from [master].[sys].[dm_exec_requests] where [session_id]!=@@SPID and [database_id]=DB_ID('$(database)'))>0
begin
select @cmd='kill '+CAST(spid as varchar(5)) from [[master].[sys].[dm_exec_requests] where [session_id]!=@@SPID and [database_id]=DB_ID('$(database)');
exec [dbo].[sp_executesql] @cmd;
end
select 'setting database to SINGLE_USER mode'
alter database $(database) set SINGLE_USER;
select 'updating database settings to move file'
alter database $(database) modify file (name='$(logfilename)', FILENAME='$(newlogpath)');
select 'detaching database'
exec [master].[dbo].[sp_detach_db] @dbname = N'$(database)', @keepfulltextindexfile=N'true';
select 'moving file'
exec [dbo].[xp_cmdshell] 'move "$(originallogpath)" "$(newlogpath)"';
select 're-attaching database'
create database [$(database)] on (filename = N'$(datafilepath)'), (filename = N'$(newlogpath)') for attach;
go

Execute syntax:

sqlcmd
-S <instance name>
-d master
-E
-i <script name where the code above was saved>
-v database ="<database>" logfilename="<current log file>" newlogpath ="<new log file path>" originallogpath ="<original log file path>" datafilepath ="<data file path>"
-o <output file>

Tags: Programming