How do I Backup the MS SQL server 2000 database file (mdf) to my local PC ?

[-mk-]

Hi, currently I have a shared web hosting with MS SQL server 2000 database.

I have already implemented the database, with all the data inside the database, on the server.

Now I wish to copy the whole database (.mdf) down to my PC, in case I wanna change hosting or in case the server broke down, and keep it as a safe backup.

I already created a backup of it, the .bak file, but it is all stored in the web hosting database server.

How can I actually copy the whole thing down to my pc ?

I am using Microsoft SQL Server Management Studio Express.

Thanks alot..

[Ramy Mahrous]

Manually you can stop the server copy the data files and then restart the server..

[Jx_Man]

Manually you can stop the server copy the data files and then restart the server..

yeah, i do this way if i want to backup my database. i think this is a simple way.:)

[Jx_Man]

and don't forget to stop a server for a while when you want to backup data cause it will give you an error.

[-mk-]

sorry but it is a shared-hosting service. I don't have the access to the server files and such.
Only able to connect to their SQL Server.

FTP accounts only able to access db and wwwroot folders.

[AaronASterling]

Give this a shot from within sqlcmd or osql....

EXEC sp_addumpdevice 'DISK', 'MyBackupToFTPland', 'DirectoryYouCanFTPFrom\backup.bak'

BACKUP DATABASE yourdatabase
TO MyBackupToFTPLand

Then you should be able to FTP it...assuming of course that SQL Server has write access to someplace that you have FTP read access too.... could get complicated. Check the BOL for more options. the DISK keyword is necessary but you should be able to get away without the other wacky names i've chosen. hope that helps