copy only the MDF file to new location

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

copy only the MDF file to new location

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources