Back up database to another server

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)

Back up database to another server

Author

Topic

mona770118
Starting Member

1 Post

Posted - 2005-03-17 : 00:59:52

Hello all
I need to create a job which backs the database to a remote server

I have set the folder on the remote server sharing option for everyone to have full control and set the web sharing, however to access it user id and password needs to be entered

my script in the database as follows
declare @FilePath varchar(300)
set @FilePath = '\\ServerName\SharedFolder\DatabaseName.Bak'

BACKUP DATABASE DatabaseName TO DISK = @FilePath WITH FORMAT

the error message I got was

Server: Msg 3201, Level 16, State 1, Line 3
Cannot open backup device '\\ServerName\SharedFolder\DatabaseName.Bak'
. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 3

I know for sure the path name is correct but I also know to access this folder from the database server I need to use user id and password too access it...

The question is how can i put the user id and password in the back up script

many thanks in advance for your help

BACKUP DATABASE is terminating abnormally.

Kristen
Test

22859 Posts

Posted - 2005-03-17 : 04:23:16

I don't think you can backup to a network share - but etierh way it would be better to back up to a local disk, and then COPY the file to the share. The backup wil be quicker, so less intrusive on the database being backed up, and if the subsequent COPY fails there is no major grief, whereas a network connection failing in use is likely to take a while to time out - all the time causing the application using it to sit around waiting!

Kristen

kish
Starting Member

45 Posts

Posted - 2005-03-17 : 07:53:14

You can backup to a network share. I have my full and T-log backed up on the network share.
You need to check your MSSQL Server Service and SQL Server Agent Service startup account. They need to have administrative priviliges.
As far as security is concerned, you can impose folder level security, so that only you have full permissions on the folder and others dont have any permissions on the folder.

--Kishore

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources