How to: ? have the built-in backup write to a network share ? 1

[TopRung]

Very simple I am sure, but I don't see it...

I need to back up a SQL db file to another server. The destination folder is shared.

In the backup setup, I only see local drives. I tried to manually change the path in the TSQL script and test in the query analyzer, buti get and error:

"Device error or device off-line"

any suggestions? BTW, using console version 2.0, and Standard Edition.

 

[JayKusch]

you need to use the UNC naming convention. So if you have a target server you are trying to back files to it would be something like ...

\\TargetServer\TargetDir

Sometimes you have to use the IP of the target server like ... ( and this is only a demo IP)

\\10.224.3.106\TargetDir

You could create a backup device(dump device) with the example shares above in Enterprise Manager or create it w/ a T-SQL command.

Then your T-SQL, Stored Procedure can use the device to write the backup to. May want to take a look at BOL (Books OnLine) for some examples and further help.

Thanks

J. Kusch

 

[TopRung]

Well, using the TSQL, i can write to one shared folder, but not another. The only difference between the folders is:

The ones I can write to have their parent folder shared (Folder A). All subfolders beneath this parent can be written to.


The one I can't write to is a shared subfolder (Folder B) that is in another folder that is not shared.


Both folders (A and B) have equal credentials.

 

[JayKusch]

Are both shares on the same server?

Thanks

J. Kusch

 

[TopRung]

yes, on same server. Weird huh?

 

[JayKusch]

Have you tried to create backup devices(dump devices) to each of the shares and see what errors, if any, are produced?

Thanks

J. Kusch

 

[TopRung]

Hmm, no, i am not sure if I follow. This is all I have done on the backup thus far-- T-SQL below:

Successful backup to Folder A (Folder A being a shared Parent):

BACKUP DATABASE [Nexus] TO DISK = N'\\NetServ1\Folder A\Anysubfolder\SQLSERV.Atlas backup.BAK' WITH INIT , NOUNLOAD , NAME = N'DBSERV1.Nexus backup', SKIP , STATS = 10, NOFORMAT



Unsuccessful to Folder B (folder B is a shared subfolder of and unshared parent):

BACKUP DATABASE [Nexus] TO DISK = N'\\NetServ1\Folder B\SQLSERV.Atlas backup.BAK' WITH INIT , NOUNLOAD , NAME = N'DBSERV1.Nexus backup', SKIP , STATS = 10, NOFORMAT



I hope I am not making this more complicated than it should be, but it seemed a rather straight forward approach.

BTW, I am on Windows 2000 Server SP3 for the destination, and SQL is on Server 2003.

 

[JayKusch]

And you say that the "Anysubfolder" has the correct permissions on it. Have you given this share full rights just to see if it will create the file? If so ... you could then back off on full rights until the level of security fits your needs.

Thanks

J. Kusch

 

[TopRung]

yes, I have given full rights. Both folders have identical rights, and the only difference is that the one that I can't write to has a parent that is not shared.

I will keep playing with the shares, but if I can't get it, I will just use an XCOPY to get it from one folder to another.

 

[JayKusch]

Just for fun ... yeah right - LOL, try this lets take a look at the SQL Server error log when it is done. It may point you to your issue a little quicker.

In Enterprise Manager(EM), under Management..Backup right mouse click on the Backup icon and choose "New Backup Device". Put in a name for the device like Test1 and then the path of the GOOD share. This should take and create you the device.

Do the same thing as before but lets call this one Test2 and put in the BAD path. SHOULD FAIL ...

Lets now go in EM right below the backup icon and open up your SQL Server log. Click on the one that says Current and look for the error message about creating the bad backup device. May have some info to help us out.

Now you can use the backup devices in you T-SQL code or you can just delete them once you are done testing.

Thanks

J. Kusch

 

[TopRung]

JayKusch... When setting up the Backup Device Location, the share(s) do not show - only local drives appear. (Same thing as my original post mentioned.) I will try to manually type the path in the Filename field. Is that correct?

 

[JayKusch]

That is correct! You will only be given LOCAL shares to pick from by default.

This is a "VERY" standard practice amongst DBAs to create backup devices pointing to remote shares. We then usually have some 3rd party backup utility on the remote server to backup our "disc backups" to tape for storage off-site.

Thanks

J. Kusch

 

[TopRung]

Okay, sorry my response has taken so long:

Using your method of a backup device versus just the t-sql, I am sure that the problem I have is--> How I shared the folder and the folders location.

The folder I am trying to share happens to be a subfolder nested in the "program files" folder of the target server. It is actually the default BACKUP folder for the standard SQL installation.

Is this the problem. I figured I can share anything I want, so what is the problem here? Maybe I should be posting this to another group?? If so, which one?

Thanks man!!

 

[TopRung]

Oh, i am sorry, let me tell you why I figure it is the share:

I created TEST1 as you suggested, and it works just fine.

But when trying to create Test2, i get an message "Unable to verify the existence of the backup file location."

It can't see the share at all. Test1 points to a shard folder that is a mapped drive on all clients - Test2 to the subfolder in the unshared "program files".

WHy didnt I just say that at the beginning - newb!!

 

[JayKusch]

SO you have it figured out now?

Thanks

J. Kusch

 

[TopRung]

well no, not the share part. I want to make that directory writtable to the backup. Can't see the error of my ways. But if I do, I will surely post it here.

 

[TopRung]

ALL WORKING NOW.... using just the T-SQL script fails, but once I created the backup device and scheduled a job to run to that device, it works!!

I APPRECIATE ALL OF YOUR TIME AND HELP!! THANK YOU>



"May the best day of your past be the worst day of your future!!