Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS SQL Backup

Posted on 2016-11-07
24
Medium Priority
?
155 Views
Last Modified: 2016-11-18
I have access to the SQL box, via management studio only. I have access to a web server (that hosts the app), with management studio on it, residing in the same subnet. I'd like to run a backup from management studio on the web server, to backup the DB to the web server.

I have created a shared directory on the web server to backup to - when I try to add this as a destination, I get the following alert - cannot verify the existence of the backup file location. If I accept and try to run I get the following error - backup to mirrored media sets requires all mirrors to append.

Any ideas? Not sure what I am missing. :-)

Thanks.
0
Comment
Question by:Frank Grimes
  • 12
  • 5
  • 4
  • +2
24 Comments
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41877409
Hi,

Path you are entering for backup is valid ?
Also your SQL Server Service account has permission to write on it <<Remote Location>> ?

This may help you out on this -<<step by step approach>>  - https://www.mssqltips.com/sqlservertip/1126/sql-server-backup-and-restore-to-network-drive/

Regards,
Pawan
0
 

Author Comment

by:Frank Grimes
ID: 41877444
I can path to the location, but can't confirm the SQL box can path to it.  (It should, as it's on the same local network and there are no permissions required... the everyone account has R+W)

When I try to run the BU - I get the following - backup to mirrored media sets requires all mirrors to append. Not sure what this means?

Thanks for your help!
0
 

Author Comment

by:Frank Grimes
ID: 41877450
Is there any way to confirm or deny that the SQL server can't path to the web server/share? :-)
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 30

Expert Comment

by:Rich Weissler
ID: 41877491
You _should_ be able to backup to a share (I'm assuming your using SQL 2012+ Enterprise edition).  I've never used mirror media sets, so that's a bit of a wrinkle.  How are you performing the backup?  You aren't specifying a mirror in the backup, are you?
0
 
LVL 30

Expert Comment

by:Rich Weissler
ID: 41877508
And just to make certain we're on the same page -- the SQL Server itself is running under a domain user account... not a local account (or NT Service\*) right?
0
 

Author Comment

by:Frank Grimes
ID: 41877509
Yes, it's SQL 2012.
I am right clicking on the DB,  selecting TASKS and Backup. Selecting the required DB, FULL BU, destination TO DISK, then trying to add a path.
Media options are set to Overwrite Media, Backup to existing media set, append to the backup set.

Besides that, everything else appears normal.

To be honest, I have no idea what the mirrored media sets is referring to - I could ask the DB Admin?

Just no sure how to approach this...
0
 
LVL 30

Expert Comment

by:Rich Weissler
ID: 41877538
Run the query "select servicename,service_account from sys.dm_server_services" -- just to confirm that the service account is one that should be able to reach the share...
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41878160
Try this...

Right click on the DB,  selecting TASKS and Backup. Selecting the required DB, FULL BU, destination TO DISK, then trying to add a LOCAL machine's path.

Then from the local path copy the bak file manually to the other machine or server. I think you cannot directly map other machines path.
0
 

Author Comment

by:Frank Grimes
ID: 41879399
When I try to run the query, I get...

Msg 300, Level 14, State 1, Line 1
VIEW SERVER STATE permission was denied on object 'server', database 'master'.
Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.
0
 

Author Comment

by:Frank Grimes
ID: 41879403
If I try to add the SQL server's local path, it gives me the same error - backup to mirrored media sets requires all mirrors to append.
Same thing if I add the webserver's local path. :-(

Has the DB Admin missed something here? :-)

Thanks guys!
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41879754
Seems you do not access to do that. For which database you taking backup - your one or the master one ?
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 41880726
Try this:

In the Management Studio on the machine where the SQL server is start a backup wizard: Right click on the database name > Tasks > Back Up... > in the backup window click on Add button to the right of the bottom panel > in the new window click on ... button > in the Locate Database Files window in the Selected path text box you will paste the exact network location together with the backup file name, i.e. \\webservername\backup_folder\mydatabase.bak and than hit OK and OK.

If the backup starts everything is fine but if you get access error you will have to make sure that you give permission to the SQL server domain account under it runs for that network location. You will have to add the domain to that location with full permissions.
0
 
LVL 30

Expert Comment

by:Rich Weissler
ID: 41882068
> Has the DB Admin missed something here?

Not necessarily, but you will need to coordinate your efforts to backup to a network location with the DB Admin.
0
 

Author Comment

by:Frank Grimes
ID: 41888196
Sorry for the delay guys....

Q: Seems you do not access to do that. For which database you taking backup - your one or the master one ?
A: I am trying to backup my DB, not the master.

Q: 'Try this' suggestion - A: still get the same 'mirrored media sets' error.

I assume the SQL server is simply unable to write or path to my location?
Any thoughts?
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41889121
I assume the SQL server is simply unable to write or path to my location?
Any thoughts?

Is that your local file path. If yes the you should or you do not have access to write it there. Check the folder properties and remove read only from it.
0
 

Author Comment

by:Frank Grimes
ID: 41889631
On the web server (Management box) I have created a local share, with 'everyone' r + w access.
The only thing I can't confirm is.... why can't the SQL box write to this directory?

That being said, I don't fully understand the error message....  Here's the full error message.

Backup failed for Server 'x.x.x.x'.  (Microsoft.SqlServer.SmoExtended)
System.Data.SqlClient.SqlError Backup to mirrored media sets required to append. Provide all members of the set, or reformat a new media set.
0
 

Author Comment

by:Frank Grimes
ID: 41889638
Could this have to do with the 'Back up to the existing backup sets' and 'Append to the existing backup sets' options being checked?
I assume so... ??

If so, how do I create a *new* backup without erasing all existing backups?

Thanks!
0
 
LVL 35

Expert Comment

by:Pawan Kumar
ID: 41889688
provide a new file name while taking backup and take full backup.
0
 

Author Comment

by:Frank Grimes
ID: 41889787
So, just to confirm....if I select Back up to a new media set, and erase all existing backup sets - this won't affect their other jobs? Meaning, it won't erase any existing backup sets? Thanks again!
0
 
LVL 52

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 41889870
You're only using the GUI for the backup, right?
Did you try to run the backup command manually? Just open a new query window in SSMS and run the following:
BACKUP DATABASE databasename
TO DISK='\\networksharename\databasename.bak'

Open in new window

Any error returned should be more explicit than the one you're getting from the GUI.
0
 

Author Comment

by:Frank Grimes
ID: 41890090
I'll try it and report back.... thanks!
0
 

Author Comment

by:Frank Grimes
ID: 41892083
Awesome - the command worked.

Going to confirm it restores OK... more to follow!

BACKUP DATABASE databasename
TO DISK='\\networksharename\databasename.bak'
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 41892584
Awesome - the command worked
That's one of the reasons that I never use the GUI to perform database tasks. There are limitations and one is SSMS can't see network shares unless you map them as a drive.

Going to confirm it restores OK... more to follow!
Restore should be as simple as:
RESTORE DATABASE databasename
FROM DISK='\\networksharename\databasename.bak' 

Open in new window

0
 

Author Comment

by:Frank Grimes
ID: 41893620
Works great, thanks! Still not sure why I couldn't do this with the GUI... but ALL GOOD!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question