Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jayram
Starting Member
47 Posts |
 Posted - 2012-06-21 : 13:57:22
|
Hello,
I want to copy a mdf file to a new location
i believe these are the steps
1. detach or take offline
2. copy the mdf file from source location to target location
3. bring online
i do this through enterprise manager but can it all be automated in a script
e.g:
for step 1, i would
-- Take the Database Offline
ALTER DATABASE [myDB] SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO
but i do not know how to copy the .mdf file? is there some copy or xcopy command that can be used?
thanks
Jay |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-21 : 14:09:19
|
you can use copy command in xp_cmdshell.
Might want to look at powershell
You should also take a backup first (you could also do a drop and restore with move).
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
 |
|
|
jayram
Starting Member
47 Posts |
Posted - 2012-06-21 : 14:15:24
|
Thanks Nigel.
would the below work!!
--Take offline
ALTER DATABASE testDB SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO
--Dynamically copy
declare @cmd varchar(4000)
set @cmd = 'xcopy F:\MSSQL\Data\testDB.mdf' + cast(getdate() as varchar(10) + '>> \\RemoteServer\Share\Folder\'
exec (@cmd)
-- bring the Database Online
ALTER DATABASE zBRSDATA_2012_6_14 SET ONLINE
GO |
|
|
|
|
|
|
copy only the MDF file to new location