Remote Intanet SQL DOS Batch Backup?

Question

Remote Intanet SQL DOS Batch Backup?

Kevin Snyder

Ten Centuries

Points: 1082
More actions
September 25, 2005 at 8:30 am

#111785

I use the following DOS Batch code to successfully backup our MS SQL 2000 database on the same machine the SQL Server is installed.
NET STOP MSSQLServer
xcopy /s /e /y C:\"Program Files"\"Microsoft SQL Server"\MSSQL\Data\*.* D:\SqlBak\
NET START MSSQLServer
Q. How do backup the database onto a different machine that's executing the actual DOS batch-file?
\\websvr mssqlserver stop?

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

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

David le Quesne SSCertifiable Points: 5229 More actions · Answer 1

Hi,

if you are trying to backup the file to a folder on another server then I suggest you create a shared folder on that other server, and give the account you are using to do the backup visibility and access rights to that folder.

Using your backup account, map a network drive to the shared folder, and then use XCOPY in the normal way to copy to the mapped drive.

I was wondering though, why you are stopping the server to backup the database files, rather than using SQL Servers maintenance plans to create an online backup of the database and copy that to the other server. Do you mean to restore the file to the other server, or are you backing it up to tape...

David

If it ain't broke, don't fix it...

Vikas Vashishth Newbie Points: 2 More actions · Answer 2

be alys aware while backup though xcopy command your SQL server is not running. So it shoukd alys be closed before taking backup through xcopy.

philcart SSC-Forever Points: 47796 More actions · Answer 3

I think the problem here is that you're circumventing the backup/restore process by copying the mdf and ldf files directly. XCOPY is not a backup tool, otherwise it would be called XBACKUP

If you take a backup of the database, as per normal operating practices, then you can transfer the backup files without stopping the SQL Services. It doesn't matter whether you use XCOPY, FTP, ROBOCOPY or any other file transfer tool.

--------------------
Colt 45 - the original point and click interface

Jeff Gray SSChampion Points: 10667 More actions · Answer 4

So, you want to stop/start SQL from a remote machine. Have a look at the following:

http://www.sysinternals.com/Utilities/PsExec.html

The utility will allow you to run programs on remote computers without installing any remote software. The ability to do it is built into windows, but this utility provides a relatively easy interface.

Also.... What everybody else says about the *right* way to back up SQL.... You might want to evaluate whether a live backup would be a better solution for you.

HTH

jg

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

I think a quick read of a section of BOL (Books Online) may be in order here.

Contents tab

Administering SQL Server

Backing Up and Restoring Databases

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."