[ANSWERED] How Do I Backup MS SQL to a UNC path?

[link470]

Hi everyone,

 

Got a question here on MS SQL. I have 2 MS SQL servers, both the 2005 edition and running the SQL Server Management Studio. One of the servers is the full software suite, the other is the express suite due to not having a second license. Both work great. So here's the issue, I've been programming proper backup schedules over the summer. Everything runs at it's specified time, all files etc. and important items on the network are now backed up automagically, and the only part I'm wondering about now is these two SQL servers and how to back them up.

 

I started with a Maintenance plan in SQL Server 2005 [Full] and thought I could program a backup there, but it didn't like that. I'm trying to backup to a UNC path. However, when I enter in a UNC path the backup errors out and doesn't like it. When I was originally configuring the backup, it took me about 12 clicks of the OK button at the bottom before it finally accepted the UNC path, it kept saying it couldn't resolve the path I had placed in there for where the backup was to be sent. Somehow I had the great idea of continuously clicking ok, and it accepted and allowed it. Now if I go in and edit it, place in the same UNC path, it accepts it [weird]. So anyway, I tried to run the backup and again I get an error. I checked the logs and it said access is denied when trying to create the backup at that location. I've been backing up with an account for backups only, and my domain administrator account, those are the only 2 users who have access to the backups, no other users can read them or write to those shares. So I thought ok, MAYBE it's because I'm using the SA account as SQL authentication and not my integrated Windows login. So I tried logging in as integrated while logged into my domain administrator account and to verify I had access, I entered in the UNC path into the run command in windows and made a new folder in the share when it came up. So far so good, nothing wrong with the share.

 

So the only issue now is getting MS SQL to actually write to the share. Am I doing anything wrong that you can see?

 

As an alternative, I know SQL Express isn't going to allow me to do that since there's no automated backups. Is it possible for these two database servers, to just backup the database folder in program files that holds the actual databases using NTBackup? Or is that a bad idea?

 

Thanks!

 

Edit: Just changed the backup location to my desktop in a new folder and it works perfectly there.

Edited September 2, 2009 by link470

[timbo343]

I have a couple of scripts that run and backup the files to a location that gets backed up to our backup server and also gets taken off site.

[AXE]

You can script backups:

save the following code in a file

 

USE master;

BACKUP DATABASE  TO DISK='';

 

then from a command prompt or batch file (flags are case sensitive):

sqlcmd -S  -i 

you can toggle Trusted Connection using the -E flag
 

You CAN backup the files in the database directory, but you have to shut down the SQL Server services first.
 

Usually the account the service is running under (either SQL Server or SQL Server Agent) needs to have permission to access the folder you're backing up to.

[link470]

 

My bad, but what's a "server instance"? How would I write the server and then instance there? Thanks!

[Edu-IT]

Such as servername\database I think?

[link470]

Such as servername\database I think?

 

I was wondering about that but I'm not sure if that would work, it looks like you call the database in the first part of the script up top. Then down below in the bat file you have to do servername and something else for "instance". How is that written? Thanks!

[AXE]

To clarify:

Refers to the server.

You can have more than one 'copy' of the SQL Server Software on that server, each 'copy' (instance) installed after the first must be given a name.

 

The name of the database to back up. Don't change 'master' in the script.

 

For example:

A basic server with one instance of SQL Server and the Server name SQL1 would look like:

sqlcmd -S SQL1 -i

 

A server with more than one instance of SQL Server (say ABC and XYZ) and the Server name SQL1 would look like:

sqlcmd -S SQL1\ABC -i

or

sqlcmd -S SQL1\XYZ -i

[link470]

To clarify:

Refers to the server.

You can have more than one 'copy' of the SQL Server Software on that server, each 'copy' (instance) installed after the first must be given a name.

 

The name of the database to back up. Don't change 'master' in the script.

 

For example:

A basic server with one instance of SQL Server and the Server name SQL1 would look like:

sqlcmd -S SQL1 -i

 

A server with more than one instance of SQL Server (say ABC and XYZ) and the Server name SQL1 would look like:

sqlcmd -S SQL1\ABC -i

or

sqlcmd -S SQL1\XYZ -i

 

Thank you very much for taking the time to clarify that. I'm going to give er a try today and see what I can do. Thanks so much! I'll let you know how it goes if I remember.

 

Edit:

I think I have everything right with the syntax. I've got the script outputting a log. The backup doesn't want to take place because I'm still receiving the access is denied message. I researched on Microsoft's website and found that the SQL Express service has to be started with an account that has access to that share instead of the local service. So I created a new account and made the changes to the share so the new account could access the share. Then I edited the services to start with the new account I had created. It worked, fired up the service great. SQL's running correctly. Now I go to run the backup script again, and it fails with the same access denied message. Is there anything else I'm obviously doing wrong? Thanks!

Edited September 2, 2009 by link470

[AXE]

If all else fails, you could try backing up to a folder on the SQL Server, then copy the backup from there.

[link470]

If all else fails, you could try backing up to a folder on the SQL Server, then copy the backup from there.

 

Ya I think that's what I'm going to do. Run a backup to a local folder and then run ntbackup to send the contents of the local backup folder to the backup server.