about using osql BACKUP/RESTORE commands

Hi!
I'm using MSDE 2000 SP4 on local computer.
I backed up database into local hard drive using osql command like...
OSQL -E -Q "BACKUP DATABASE MYDB TO DISK = 'C:\MYDB.bak'" >
LogBackupDatabase.log
...and it's working fine. But when I try to save backup file directly
into network drive, it's not working. "LogBackupDatabase.log"-file
contains error message...
"Msg 3201, Level 16, State 1, Server MYLOCALCOMPUTER, Line 1
Cannot open backup device 'Z:\MyFolder\MYDB.bak'. Device error or device
off-line. See the SQL Server error log for more details.
Msg 3013, Level 16, State 1, Server MYLOCALCOMPUTER, Line 1
BACKUP DATABASE is terminating abnormally."
Z: is mapped network drive.
Is it possible to save backup file into network drive somehow? Maybe my
osql is not correctly formed to save into backup file into network
drive, or am I missing something?
Also restore command using osql is not working if I try to restore
backup file from network drive.
Thanks in advance!
Mika
Hi,
Use UNC (REMOTESERVER\SHARE\PATH\) path along with Backup or Restore
commands.
There are Few Pre requisites to do backup remotely;
1. You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharenameX\dbname.bak' with
init
Perform the same for Restore Database
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
Thanks
Hari
SQL Server MVP
"Mika M" <mahmik_removethis@.luukku.com> wrote in message
news:OASY$mStFHA.3452@.TK2MSFTNGP14.phx.gbl...
> Hi!
> I'm using MSDE 2000 SP4 on local computer.
> I backed up database into local hard drive using osql command like...
> OSQL -E -Q "BACKUP DATABASE MYDB TO DISK = 'C:\MYDB.bak'" >
> LogBackupDatabase.log
> ...and it's working fine. But when I try to save backup file directly into
> network drive, it's not working. "LogBackupDatabase.log"-file contains
> error message...
> "Msg 3201, Level 16, State 1, Server MYLOCALCOMPUTER, Line 1
> Cannot open backup device 'Z:\MyFolder\MYDB.bak'. Device error or device
> off-line. See the SQL Server error log for more details.
> Msg 3013, Level 16, State 1, Server MYLOCALCOMPUTER, Line 1
> BACKUP DATABASE is terminating abnormally."
> Z: is mapped network drive.
> Is it possible to save backup file into network drive somehow? Maybe my
> osql is not correctly formed to save into backup file into network drive,
> or am I missing something?
> Also restore command using osql is not working if I try to restore backup
> file from network drive.
> --
> Thanks in advance!
> Mika
|||hi,
Mika M wrote:
in addition to Hari valuable hint, please have a look at
http://support.microsoft.com/?id=283811 in order to modify the account SQL
Server and SQL Server Agent are running on...
unfortunately this article is no longer alligned with modifications applied
with sp4.. and you will no longer be able to start and run SQL Server and
the Agent only using the reported info..
I'm still trying troubleshooting it..
try "propagating" file permissions to all sub folders as described, as long
as assigning registry permissions as
HKLM\Software\Microsoft\MSSQLServer\Setup (READ)
HKLM\Software\Microsoft\MSSQLServer\MSSQLServer (FULL CONTROL)
for the account running SQL Server and
HKLM\Software\Microsoft\MSSQLServer\SQLSERVERAGENT (FULL CONTROL)
HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSo cketNetLib\LastConnect
(FULL CONTROL)
HKLM\Software\Description\Microsoft\Rpc\UuidTempor aryData (FULL CONTROL)
HKLM\Software\Microsoft\MSSQLServer\Setup (READ)
HKLM\Software\ODBC\ODBC.INI (FULL CONTROL)
for the account running the Agent...
make those accounts member of the local sysadmins WinNT role
but i definitevely hope kb article 283811 get updated..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply