Solved

MS SQL Backup

Posted on 2016-11-07
24
69 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:edalzell01
  • 12
  • 5
  • 4
  • +2
24 Comments
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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:edalzell01
Comment Utility
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:edalzell01
Comment Utility
Is there any way to confirm or deny that the SQL server can't path to the web server/share? :-)
0
 
LVL 29

Expert Comment

by:Rich Weissler
Comment Utility
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 29

Expert Comment

by:Rich Weissler
Comment Utility
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:edalzell01
Comment Utility
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 29

Expert Comment

by:Rich Weissler
Comment Utility
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 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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:edalzell01
Comment Utility
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:edalzell01
Comment Utility
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 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Seems you do not access to do that. For which database you taking backup - your one or the master one ?
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 29

Expert Comment

by:Rich Weissler
Comment Utility
> 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:edalzell01
Comment Utility
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 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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:edalzell01
Comment Utility
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:edalzell01
Comment Utility
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 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
provide a new file name while taking backup and take full backup.
0
 

Author Comment

by:edalzell01
Comment Utility
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 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
Comment Utility
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:edalzell01
Comment Utility
I'll try it and report back.... thanks!
0
 

Author Comment

by:edalzell01
Comment Utility
Awesome - the command worked.

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

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

Expert Comment

by:Vitor Montalvão
Comment Utility
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:edalzell01
Comment Utility
Works great, thanks! Still not sure why I couldn't do this with the GUI... but ALL GOOD!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now