Backup & Recovery

greenspun.com : LUSENET : SQL Server Database Administration : One Thread

I'm trying to backup a database to remote server first i did add a device using USE master

EXEC sp_addumpdevice 'disk', 'NetworkDevice', '\\cabaan\pubs.bak'

then

BACKUP DATABASE pubs TO NetworkDevice

it gives me an error saying

Server: Msg 3201, Level 16, State 1, Line 1 Cannot open backup device 'NetworkDevice'. 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 or restore operation terminating abnormally.

I don't really understand what's goin.

basically i need to synchronize my production database so what am i planning is to take a full backup on that server everynight and then restore it back.

Can some one guide me to take a backup as well as restore on the remoteserver with syntax and what does it mean to add a device...i could not understand.

Is there any other process that's much faster to implement.

i appriciate your support and help .

-- Anonymous, January 29, 2001

Answers

Mellisa,

Microsoft's online troubleshooter looks very helpful for a problem like this one. It can be found at:

http://support.microsoft.com/support/tshoot/tshooterlist.asp? TPR=sql&SD=gn&LN=EN-US

(This must be typed all on one line with no spaces.)

I started with the "SQL Server 2000: Backup and Restore Troubleshooter". (You probably are not on SQL Server 2000, but this will still give you a lot of insight.)

When I ran it, I answered the questions thusly:

I'm having problems when I try to use BACKUP.

I have a problem with a device or media set.

3201 (Cannot open backup device.)

From this point, you will have to look in the SQL Server error log to get further with the troubleshooter. (The last page even has a link that explains how to check the error log.)

Good Luck,

Eric

-- Anonymous, January 31, 2001


Hi Mellisa, The same problem i was faced from last one month onwards. error is "device offline" about the backup. But,today i have solved this problem.

1.Under control panel services start MSsqlserver and sqlserveragent under your account.I mean select This account option on both these services.In this option give your NT account usename and PWD. The same procedure on the remote server also.One thing you should remember.Your NT account should have admin priviliges on both these servers.

2.cheack the permissions on the database you are doing backups.select permission tab on these database BackupDB and BackupLOG.

3.edit registration properties on this server.check sql server authentication.Logon as "sa" or any other pwd you selected.

4.select database, right click,select backupdatabase

5. go to add file

6.under file name box give this path

\\remoteservername\shsredfolder

7.select o.k

-- Anonymous, February 15, 2001


I also had this problem. What I had to do was delete the destination item. Then I browsed to the same folder and named the backup differently with no file extension.

That made it happy. So for the Destination have something like this: C:\program files\microsoft sql server\MSSQL\BACKUP\testBackup

-- Anonymous, September 08, 2004


Moderation questions? read the FAQ