1. Home
  2. Point-Of-Sale Software
  3. Database Backup Error

Database Backup Error

I was trying to do a backup of the database, but I got the following message after naming the backup file and selecting an external device as the backup location.

Cannot open backup device C:\Documents and Settings\backroom\Desktop\b.bck. Device error or device off-line. See the SQL Server error log for more details. BACKUP DATABASE is terminating abnormally.

Is it possible that the this could be caused by the USB in the PC not being a USB 2.0?

Reply to
Tom
Loading thread data ...

HI Tom, The first reason is that the MSSQLSERVER service is running under a separate set of NT credentials - all services are related to an NT account. It doesn't matter who YOU are logged on as (after all SQL runs quite happily when no-one is logged on locally to the server doesn't it). Therefore your logon account and any mapped drives are irrelevant. It is SQL Server doing the backup, not you. This is the same for backups done via SQL Executive/SQL Agent - they just pass the TSQL to SQL Server to run, so it's still MSSQLSERVER doing the backup/restore.

For this reason the backup gui does not show you mapped drives or allow a UNC path to be typed in. You have to use raw TSQL commands to do the backup.

The default set of NT credentials used by MSSQLSERVER is the Localsystem account. You can check what userid that MSSQLSERVER is running under by looking at control panel/services highlighting MSSQLSERVER and choosing the start-up option.

The Localsystem account has no access to shares on the network as it isn't an authenticated network account. Therefore SQL Server running under this account cannot backup to a normal network share.

So, if you want to backup to a network share you have two choices :-

  1. Change the account the MSSQLSERVER service runs under to a user account with the relevant network rights. OR
  2. Amend the following registry value on the TARGET server and add the sharename you want to dump to - the share does not then authenticate who is coming in and so a Localsystem account will work. The server service on the target server must be re-started before the change takes effect. Note that this effectively removes ALL security on that share, so you're letting anyone/anything have access. Which is probably not something you want to do with production business data.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters\NullSessionShares

Whichever method you use, you MUST also use a UNC name to reference the file required and not a drive letter.

If solve the problem please rate me. "Tom" wrote:

Reply to
Akber Alwani

Or even easier, have a simple command file that does the backup first to the local hard drive, and then copy that backup to your external drive.

We actually have a icon on the desktop (life raft) that invokes such a command file and actually works to keep a 2 day rolling backup going and also copies to 2 different networked systems (just in case we have a "big" failure).

Reply to
John M.

We do the same thing John does. Using a script from an icon on the desktop, I get the DAY from the system date and then copy the DB backup to a network PC, into a folder based on the day's name. This way you have a weeks worth of backups.

Marc

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters\NullSessionShares

Reply to
Marc

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.