SQL 2000 Backup over NETWORK

Question

SQL 2000 Backup over NETWORK

sqldba-294117

Hall of Fame

Points: 3486
More actions
October 2, 2006 at 12:12 pm

#62206

I know this one is very simple question, but any help will be highly appeciated.
I would like to know how to do SQL2000 DB backup over the Network without using any 3rd party tool or NAS.
Something like Shared Network drive or to another server harddisk.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jimmy Jen SSCrazy Points: 2915 More actions · Answer 1

An easy way is to use Database Maintenance Plan Wizard from Enterprise Manager and within the wizard when "Specifying Backup Disk Directory", you can use UNC name to specify the server and folder you want to backup to.

The wizard will automatically creates maintenance plan as well as job under the Jobs in the SQL Server Agent.

Another way is to use the BACKUP DATABASE command and schedule it to run at the time you like.

Jimmy

Russell Shilling SSCarpal Tunnel Points: 4349 More actions · Answer 2

It is not a recommended practice to backup to a network share, though I did that for a couple of years without problems.

The method I use now is to schedule backups on the local machine drive, and have a scheduled task that runs a small batch file to copy backups to the network share (offsite).

The server is set to retain only a couple of days of backups, but the network share retains them indefinitely.

Here is a sample of the batch file:

echo ------------------------------- >>"C:\Tasks\backup.log"

date /t >>"C:\Tasks\backup.log"

time /t >>"C:\Tasks\backup.log"

xcopy "D:\MSSQL\Backup\*.*" "\\server\backupshare\*.*" /D /S /Y /C >>"C:\Tasks\backup.log"

Schedule the task to run a few minutes after each backup. The switches on XCOPY copy only changed files, and recurse into each subdirectory.

So long, and thanks for all the fish,

Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3

noeld SSC Guru Points: 96590 More actions · Answer 3

I also believe is not the recommended way but I have done it successfully for a *long* time.

backup database to disk='\\ServerName\ShareName\Yourbackupfile.bak' with stats=10

Cheers,

* Noel

Pankaj Khanna Say Hey Kid Points: 707 More actions · Answer 4

Ue sp_addumpdevice stored procedure to add a network dump device. Then simply refer this backup device in your BACKUP DATABASE command

Pankaj Khanna
Database Administrator - SQL Server 2000

Keep hope to keep you intact...

sqldba-294117 Hall of Fame Points: 3486 More actions · Answer 5

sqldba-294117

Hall of Fame

Points: 3486

October 4, 2006 at 9:24 am

#664121

thanks for your all help & suggestions.

AlexGreen Right there with Babe Points: 763 More actions · Answer 6

You can save your backups to a mapped drive like this:

BACKUP DATABASE MyDB to DISK = 'D:\Backups\MyDB.bak' as long as you have the proper permissions on the remote server.

Even though you don't want to use a 3rd. party tool, in this case this one SQL Backup and FTP could be very helpful, because it lets you save your backups in remote in your LAN or FTP sites. The tool can also compress and encrypt your files before save them.