Copy Database to network drive

[napoleao]

I am trying to put sql server 2000 to copy a database to a network drive, but I cant find a way of doing that, I have tried using the backup feature, but it does not allow me to save to a network drive, the only options I get is a backup device and to save to the C drive, anyone know a way of doing this, all I want it to backup the database daily to a network drive.

 

[mrdenny]

When backing up through Enterprise Manager, you can backup to a networked drive, however you have to type in the path that you want to back up to, and the account that the SQL Service is running under must have writes to that path.

You can also write the backup statement yourself, so you only have to type the path in once.

backup database database_name to disk='\\server\path\to\backup.bak'

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[napoleao]

sorry I dont have much experiencie with SQL2000, where to I put that code?

 

[mrdenny]

You can run the code from Query Analyser.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[napoleao]

sorry once again, and that will make it backup daily?

 

[mrdenny]

no, that will back it up once. If you want to back it up daily, you would need to create a job.

I would recommend setting us a maintenance plan to take care of the backups for you. That way it will delete the old backups that you don't need any more.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[napoleao]

the problem is that using the maintenance plan does not allow me to send the backup to a network drive, only to the C drive or a backup device

 

[mrdenny]

When creating the maintenance plan you have to type in the full path that you want to backup to. It can backup to a network drive, it only shows the local drives in the display.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[napoleao]

working fine, thanks mrdenny

 

[mrdenny]

no problem

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[DebiJo]

I've not had alot of luck backing up databases through SQL to a network drive. If the database gets of any size, the backup tends to only work on occassion. I have read articles at MS that say not to do it across the network. I back up to the local drive, then I have a .cmd file script that moves it to the other server.

Debi

 

[mrdenny]

The problem with backing up to a network drive, is that if the network stops working for even a split second the backup will fail.

SQL has a much much lower tollerance for drive hickups then most apps. That's why Microsoft says to do everything local first. It prevents the slow network link issue from comming up.

Denny

--Anything is possible. All it takes is a little research. (Me)