May 7, 2005 at 6:58 pm
I've searched high and low for the answer to my predicament, and have come up pretty much empty. Hopefully someone here can help me out.
I'm an end user of MSSQL (I'm using a hosting company for my DB provider) and am trying to find a method of making a local backup of the database that company is hosting. I have the Client Tools for MSSQL installed, and can connect through EM to the database. Heh, I even found what appears to be their path to the data and log files (mdf and ldf) by rightclicking my DB's name and selecting Properties, along with the appropriate tabs after that.
My problem is that using the Backup Database option in EM doesn't seem to work (as I'm unsure the destination to use, and I can't create a new backup device), and using DTS is rather confusing (I'd export the DB to a text file, but it seems to want to do it one table at a time...and I *want* the whole thing...heh).
I absolutely despise asking for help, especially when I *think* I'm supposed to be able to figure it out on my own. But here I am, asking for your assistance. If I can find it here, that'd be awesome.
Thanks in advance for whatever help you can provide.
Jeff B.
May 9, 2005 at 3:25 am
you could create a share on your client and specify UNC path when choosing location for your backup. it should work without problems under one condition - that sql server is running under domain account that can access to share on your computer (i.e. it's not running under LocalSystem account)
May 9, 2005 at 6:51 am
If your SQL DB provider is anything like the one I use, I doubt Martin's suggestion will be feasible. I too was in the same predicament as you in that I couldn't do my own backups. Never really found a "good" suggestion. What I ended up doing was installing SQL Server Personal Edition on my PC (I bet MSDE would work too) and then creating a database locally and copying all the objects from my "live" database on the hosting provider to my local DB. Works pretty well but fortunately my DB is pretty small. Hope this helps a bit... Steve
May 9, 2005 at 9:04 am
It sounds as if your hands are tied. Maybe it's time to look for another more 'dba' friendly hosting service ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
May 9, 2005 at 9:09 am
I am way more of a SQL developer than a DBA, but I believe the root problem lies in the fact that thru EM the only backup devices you can use are those that exist on the actual SQL Server machine -- hard drives, mapped drives, tape drives, etc. Therefore, thru EM I have no way to make a backup of a DB on the SQL Server onto my client PC. Just doesn't seem to be a way. As a result it's more a limitation in the MS tools than it is with the DB hosting provider.
May 9, 2005 at 9:13 am
Could you not make a request of the hosting provider to carve you out a slice of disk space ? That's what I mistakenly assumed you had requested and the hosting provider not provide for you in the first place. As for using UNC's for backup, it should work (providing firewall's do not get into the way or other security measures).
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
May 9, 2005 at 12:33 pm
You can only get to devices the provider knows about. All backups are relative to the Server, and not the EM Client Host.
Does the provider allow FTP access to a server? If so, backup to that machine and then use FTP to get a copy.
May 9, 2005 at 6:28 pm
The gentleman who suggested creating a local DB and then copying the DB objects to the local db through EM is the best option. This also provides you a working copy of the db on your client for development, reporting, etc...
I don't believe you can use the Backup command to backup a database to anything other than locally attached storage devices on the server. This means tape drives and other disk storage (C:, E:, etc...).
SQL Server is very specific about this point so using EM, right-click on your database, select Tasks and export. The export wizard will follow you through copying the db objects to a local database. The wizard will also create the objects if they don't already exist so you can start with an empty database.
Good luck and vaya con dios..
May 11, 2005 at 7:56 am
Thanks for the replies, folks. Just a quick update.
The old host wanted $25.00 to provide a zipped copy of the database in my FTP directory (basically, they knew I was leaving and wanted to try milking me for one last fee before I left).
I ended up downloading a copy of EMS MSSQL Manager and exporting the data to a .SQL file, which I then imported via the new host's online web-based tools to the new host. After spending a few hours checking various sections of the site that accessed different tables of the DB, everything works perfectly normal (with the exception of some changes I need to make in the .ASP coding here and there).
In the end, I think I basically lucked out in my approach, but I still think that the old host could have easily provided a zipped copy of the DB for FTP download for no charge (simply based on the fact that they provided it at no charge the LAST time I asked for it, which was over a year ago).
I'm archiving all of your suggestions in case I need them again, but given the fact that I'm already impressed with various aspects of the new host's customer support, I doubt I'll run into this problem again. Thanks again for all of your suggestions.
May 11, 2005 at 10:58 am
Just curious... what is the new host you went to? Always good to hear about a good service provider out there, since as we all know, there are a lot of crappy ones out there...
May 12, 2005 at 12:59 pm
http://www.3essentials.com is the host that I chose. So far, they've been more than excellent in offering advice regarding both the MSSQL porting as well as converting some of the older mail-sending code to CDO format.
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply