SP Backup Database Via network

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SP Backup Database Via network

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-09-27 : 12:26:06

I have been messing around with this off and on for awhile and can't seem to get it to click. maybee someone has an better idea.

I need to create a SP to Back up a DB "ACTDB" to a file folder on the network \\servertwo\SQLbackup\Current and to replace any other ACTDB.BAK in same.

Can this even be done this way? Unfortunatlly on this particular instance of SQL I cannot modify any tables or create jobs so I need a secondary method.

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-27 : 13:59:33

You use WITH INIT to replace files. You'll use TO DISK = '\\PathAndFileNameGoHere'

Example:

BACKUP DATABASE ACTDB
TO DISK = '\\servertwo\SQLbackup\Current\ACTDB.BAK'
WITH INIT

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-09-27 : 15:27:51

Even though it is a valid path it does not like the Network path.

Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\servertwo\SQLbackup\Current\ACTDB.BAK'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-27 : 15:32:19

From the database server, go to Start..Run..type in \\servertwo\SQLbackup\Current\. Does the directory pull up fine?

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-09-27 : 16:17:49

I overlooked this. Its is Strange and new to our 2003 Server.
Even though the folder \\servertwo\sqlbackup is a full share, the sub folder does not show as valid share even with atrubutes inherited from the Sqlbackup folder.

now I can tata.

I made a new share \\servertwo\current and repathed the sp

But I still get the same error.

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-27 : 16:21:58

Does the new path and share pull up fine from the database server (not from your machine, but actually at the db server)?

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-09-27 : 16:28:17

yep I went to the server console just to be shure the remote desktop was not screwing me up.

I can copy paste and have full access to that folder from run.

Jim
Users <> Logic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-09-27 : 16:29:16

if i go to a folder on the local disk it works fine.

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-27 : 16:31:45

Ok, so does the MSSQLSERVER service account have access to that share?

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-09-27 : 16:41:40

You found it Tara

This instance was set to run under the system account.

While I can change this for me, it will present a problem for others.

This solves my probem tara.

(note If you have not figured it out by now I am tearing into ACT 2005 their new SQL version)

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources