MS SQL

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

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Mar 2008

Posts: 10

Reputation: -mk- is an unknown quantity at this point

Solved Threads: 0

-mk-

Offline

Newbie Poster

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

Mar 7th, 2008

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..

Last edited by -mk-; Mar 7th, 2008 at 7:04 pm.

•

Join Date: Aug 2006

Posts: 2,065

Reputation: Ramy Mahrous is just really nice

Solved Threads: 256

Ramy Mahrous Ramy Mahrous is offline

Offline

Postaholic

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

Mar 8th, 2008

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

BI Developer | LINKdotNET
B.Sc Computer Science, Helwan University
Technical blog | http://ramymahrous.wordpress.com
LinkedIn | http://www.linkedin.com/in/ramymahrous

•

Join Date: Nov 2007

Posts: 2,641

Reputation: Jx_Man is a glorious beacon of light

Solved Threads: 245

Jx_Man

Offline

Posting Maven

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

Mar 8th, 2008

•

Originally Posted by RamyMahrous

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.

Never tried = Never Know
So, Please do something before post your thread.
* PM Asking will be ignored *

•

Join Date: Nov 2007

Posts: 2,641

Reputation: Jx_Man is a glorious beacon of light

Solved Threads: 245

Jx_Man

Offline

Posting Maven

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

Mar 8th, 2008

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

Never tried = Never Know
So, Please do something before post your thread.
* PM Asking will be ignored *

•

Join Date: Mar 2008

Posts: 10

Reputation: -mk- is an unknown quantity at this point

Solved Threads: 0

-mk-

Offline

Newbie Poster

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

Mar 9th, 2008

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.

Last edited by -mk-; Mar 9th, 2008 at 9:21 am.

•

Join Date: Dec 2007

Posts: 31

Reputation: AaronASterling is an unknown quantity at this point

Solved Threads: 5

AaronASterling AaronASterling is offline

Offline

Light Poster

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

Mar 9th, 2008

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

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
EXEC sp_addumpdevice 'DISK', 'MyBackupToFTPland', 'DirectoryYouCanFTPFrom\backup.bak'
 
BACKUP DATABASE yourdatabase
TO MyBackupToFTPLand
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

Aaron Sterling