Backup to an IP address

[Warlocks]

Does anyone know if you can back up to an IP address as opposed to an actual drive?

I have tried creating a mapped drive to the other server but the drive doesn't appear in the valid drives list suggesting that you can only back up to local drive on that server.

I know you can set up a job to copy the backup when done but I would like to avoid this if necessary.

We are on SQLServer 2000.

Cheers,

Woody.

 

[Corran007]

you are not able to set it up using enteprise manager to do it to a mapped drive/shared drive, however you can manully write it out. you could then put this into a job.

This is an example backup to a shared network drive. it seems to work fine. look in bol for more information

BACKUP DATABASE [dbName] TO DISK = N'\\server1\D\MSSQL7\BACKUP\Backup.bak' WITH INIT , NOUNLOAD , NAME = N'dbName backup', SKIP , STATS = 10, NOFORMAT DECLARE @i INT
select @i = position from msdb..backupset where database_name='dbName'and type!='F' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='dbName')
RESTORE VERIFYONLY FROM DISK = N'\\server1\D\MSSQL7\BACKUP\Backup.bak' WITH FILE = @i

 

[paulosinuk]

Hi,

I have a very similar problem in trying to get a backup to work to another server.

I keep receiving the error message:

Cannot open backup device '\\idc179\d\cemonbackup.bak'. Device error or device off-line.

I've checked and recheck this path and have ensured that the d drive is shared for all users.

Any thoughts?

 

[SQLBill]

Something to try if you are having problems with seeing a part of a network. Have your sysadmin do a PING command from your machine to the one you want to 'reach'. The PING will tell the sysadmin whether or not you can see it from your machine. If the PING returns properly, then the problem is with your SQL connection method. If the PING doesn't return properly, then there is a problem with the way the two machines are trying to connect.

-SQLBill

 

[Corran007]

Go ahead and post your backup procedure. Have you ever been able to backup to that location, or is it just something that has currently changed. Also is cemonbackup.bak a sql backup device or is it just a backup file.


O and it does appear you can backup using enterprise manager to a another server. You need to map a drive, then create a backup device to a location on that drive. Then you create the backup to the device. This gives the ability to backup to another server with gui. It just could create a number of backup devices you may not need.

 

[paulosinuk]

Hi Guys,

Thanks for the answers, things have progressed a little since then.

I'm trying to backup two db servers to one file server and then allow the tape backup to pickup on the files. I'm aiming for a complete backup each week night to a different file name. Therefore I end up with five complete backups a week on the file server and the lot on tape each night as well.

Enough of the procedure, onto the issue. First of all I thought I had a syntax issue with the backup statement but it turns out that it was due to the permisisons of the MSSQLManager service on the db servers. Having read a M/S FAQ on the error, it turns out that the MSSQLManager service has to be started with a domain account rather than a local account. This has worked fine on one server but on the second when re-starting the service with the domain account I get the error message :

Error 1053 : The service did not respond to the start or control request in a timely manager.

I've checked and both servers have identical service packs, and builds. There simply is not reason for it to work on one but not the other!

Have even tried setting up a non sql service using the domain account and encounter the same issue.

Have looked up 1053 on MSDN and the faq just told me to keep persisting with the start of services! Suffice it to say that didn't work.

Sorry this is now going outside the realms of SQL and onto the realms of service management and if I should try this post in a different forum please let me know which one.

 

[SQLBill]

You may have already done this, but check the service on both machines. Are they set to start the SAME way? For example, if the one that is working is set to start automatically, is the one that isn't working set that way?


-SQLBill

 

[Corran007]

Some things to try. change the one that is not working back to the default account. see if it starts. If it does, proced with next.

also are you doing this through enterprise manager, or on the os services area? You should always change the account therough enterprise manager because changing it through the OS does not update everything that needs to be done.

And make sure the account has the correct permissions. Throw it in the adminstrators group for now to see if it changes anything. It may be that the machines are not set up quite the same.

Check your registrys make sure they look the same.