|
|
|
Rank: Member
Groups: Registered
Joined: 1/6/2012
Posts: 13
Location: Michigan
|
Hello,
We recently installed Sql Backup and FTP on our SQL 2008 server and have the database backups working properly. I just attempted to preform a back up the SQL 2000 server located on our network and received the following error:
ERROR: Backup of Attendance failed: Cannot open backup device '\\our-network-share\OurDatabase201201061132.bak'. Device error or device off-line. See the SQL Server error log for more details.
BACKUP DATABASE is terminating abnormally.
Taking a look at the Event Log on the SQL Server I find the following entry:
"Operating system error = 5(Access is denied)."
I see on the home page that under the compatibility list there is "SQL Server 2000 (only local backups, not remote)". I'm guessing that this is probably what I'm encountering since I'm attempting to preform the backup of the SQL 2000 server databases from the SQL 2008 machine. If this is the case, would I be better off moving the installing of SQL Back Up and FTP from the SQL 2008 machine to the SQL 2000 machine and then handling the backups operations from there. However, if this error isn't related to a SQL version compatibility isuue, then I'll look into ensuring my user rights are properly set.
Version Information
Both servers - Windows Server 2003
SqlBackUpAndFtp - 6.4.3.30062
Thank you very much for your help. Have a great day.
Best Regards,
Corey
|
|
|
|
Rank: Administration
Groups: Administrators
Joined: 8/10/2009
Posts: 368
|
Dear Corey,
The simplest is just to install SBF on the SQL Server 2000 directly and run it from there.
In the latest (6.4) version you can run it from a different server on the network and still get native backups, but you have to configure permissions properly and use a shared network folder as a temporary folder (in Advanced settings).
The "SQL Server 2000 (only local backups, not remote)" limitation is about remote/script/non-native/*.sql backups - not applicable in your case.
|
|
|
|
Rank: Guest
Groups:
Joined: 8/10/2009
Posts: 733
|
Good Evening Ruslan,
Thank you for your response. After talking about this I believe we'll go with the temporary folder solution as we have 2 instances of SQL Server 2000 along with the SQL Server 2008 installation. I'll work with another member of our staff in the morning about setting up the necessary permissions. I appreciate your insight; have a great night.
Best Regards,
Corey
|
|
|
|
Rank: Administration
Groups: Administrators
Joined: 8/10/2009
Posts: 368
|
My pleasure. Here's the relevant quote from http://sqlbackupandftp.com/remote/Network servers SQLBackupAndFTP can use a standard BACKUP DATABASE command for the servers on the same network as long as both the SQL server and SQLBackupAndFTP have access to the same temporary folder. To use this feature, specify a UNC path (like \\myserver\temp\) for the Temporary folder field in advanced settings. If you get permission errors - you need to assign read/write rights to this temporary network folder for (network) SQL server instance account and for account under which SQLBackupAndFTP is running. It is simpler if SQLBackupAndFTP is running under your personal account (in Advanced settings set "Run scheduled jobs as" to your personal account)
|
|
|
|
Rank: Member
Groups: Registered
Joined: 1/6/2012
Posts: 13
Location: Michigan
|
Thank you for the link and the information. I believe I have everything set up correctly but I seem to be receiving the following error:
ERROR: Job execution failed: Access to the path '\\MyServer\MySharedFolder\killme.tmp' is denied.
* Under the advanced settings I set the network access to run the scheduled job as a user who I gave full access to the share ('\\MyServer\MySharedFolder').
* For "Large Database backups - Temporary folder" I set the shared folder path to '\\MyServer\MySharedFolder\'.
* In the SQL Server instance I'm attempting to backup, I gave the user 'db_backupoperater' and 'public' access to the target database.
Would I need to change the 'SqlBackUpAndFtp Service' from the 'Local System' user to the one that has the access to the other server(s)? Please let me know if you have any questions. Thank you for your assistance.
|
|
|
|
Rank: Administration
Groups: Administrators, Registered
Joined: 12/1/2011
Posts: 180
|
Please check what account do you use in option "Run scheduled jods as" at "Advanced settings" window. You can also find information about job execution in windows event log.
|
|
|
|
Rank: Member
Groups: Registered
Joined: 1/6/2012
Posts: 13
Location: Michigan
|
Good Morning,
For the "Run scheduled jobs as" at "Advanced settings" I have this set to a user who has full permissions on the share. However, after taking a look at the event log on the SQL Server I'm attempting to backup across the network I see the following error:
BackupDiskFile::CreateMedia: Backup device '\\myServer\mySharedFolder\database.bak' failed to create. Operating system error = 5(Access is denied.).
Even though the share is set up on the remote SQL Server, I'm assuming the reason for this error is that the SQL Server is set to run as a "Local System" account so it has no access to the network. Would I need to change this to a domain user account in order to get the backups to work?
Also, when attempted to have SqlBackUpAndFtp run the job using scripting instead of the network temporary folder there was an error accessing the master databases view as the program expected "sys.databases" but since this is a SQL 2000 instance it is the "sysdatabases" table.
SUMMARY:
Database MyDatabase: Backup - Failure
DETAILED LOG:
1/16/2012 9:34:18 AM Creating backup of MyDatabase to D:\SQL Backup and FTP\Temp (using scripting)
1/16/2012 9:34:18 AM Creating MyDatabase1.sql
1/16/2012 9:34:18 AM Scripting database creation
1/16/2012 9:34:18 AM ERROR: Database scripting failed: An error occured while getting database information. >> System.Data.SqlClient.SqlException: Invalid object name 'sys.databases'.
|
|
|
|
Rank: Administration
Groups: Administrators, Registered
Joined: 12/1/2011
Posts: 180
|
Quote:Even though the share is set up on the remote SQL Server, I'm assuming the reason for this error is that the SQL Server is set to run as a "Local System" account so it has no access to the network. Would I need to change this to a domain user account in order to get the backups to work? Yes, if you are going to backup to a shared folder, please make sure that the account of SQL Server instance has access to this folder. Quote:Also, when attempted to have SqlBackUpAndFtp run the job using scripting instead of the network temporary folder there was an error accessing the master databases view as the program expected "sys.databases" but since this is a SQL 2000 instance it is the "sysdatabases" table. Scripting backup is in permanent beta.
|
|
|
|
|
