SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 BCP transfer MDF and LDF files?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tpk
Starting Member

10 Posts

Posted - 03/28/2003 :  17:50:13  Show Profile  Reply with Quote
My web host does not give me access to my MDF and LDF files. Is there a way around this anyone can suggest? I'm looking for an alternative to DTS and copying objects.



Edited by - tpk on 03/28/2003 17:50:46

tkizer
Almighty SQL Goddess

USA
37316 Posts

Posted - 03/28/2003 :  17:54:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
Why would you need access to the MDF and LDF files? There isn't anything you can do with them while the service is started. And what do you mean doesn't give you access to MDF and LDF files? What is it that you want to do with those files? If you just want access to the objects, then script them out and then copy the data using bcp or DTS OR if you want an exact copy of the database and can't do a detach, then just do a backup and a restore.

Do you have access to the database through any tools such as Enterprise Manager or Query Analyzer? If so, then you can do anything that you want provided you have sa or dbo (less powerful than sa) privileges.

Please explain more.

Tara
Go to Top of Page

tpk
Starting Member

10 Posts

Posted - 03/28/2003 :  18:08:13  Show Profile  Reply with Quote
Yes I have access with Enterprise Manager and Query Analyzer. I want to be able to transfer, backup and restore my databases quickly (or at a later date) to different servers. This is in my opinion best done by just moving the MDF and LDF files rather than for example, using DTS and copying all objects. As my hosts don't give access to the files directly, it looks like I'm forced to use DTS to backup my databases, which isn't a very good option.



Edited by - tpk on 03/28/2003 18:08:58
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37316 Posts

Posted - 03/28/2003 :  18:27:32  Show Profile  Visit tkizer's Homepage  Reply with Quote
You don't have to use DTS to move your databases. Just use BACKUP/RESTORE method. To copy the files, use xp_cmdshell, which is assuming you have access to xp_cmdshell. If you don't have access to xp_cmdshell, then you are going to have to use DTS, Copy database wizard, or bcp (I recommend bcp if you are familiar with it).

Tara
Go to Top of Page

tpk
Starting Member

10 Posts

Posted - 03/28/2003 :  20:45:33  Show Profile  Reply with Quote
Thanks tduggan. I realise I don't have to use DTS, but in this instance, it looks like I might as even the backup/restore feature has been disabled by my web host. They've also (understandably) removed access to xp_cmdshell. I'm assuming I have to make do with DTS (copy all objects) or script the tables/sprocs and bcp the data. I was sort of hoping I could somehow use Query Analyzer to just move the db files (without too much code) to somewhere I could get at them for backup purposes, but that doesn't look possible.

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 03/28/2003 :  21:06:37  Show Profile  Visit nr's Homepage  Reply with Quote
You could also maybe use dmo to transfer the objects - easier than dts.

There's an example on www.nigelrivett.com under dmo.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000