MS SQL Backup

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

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!

Is there any way to confirm or deny that the SQL server can't path to the web server/share? :-)

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?

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?

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...

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...

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.

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.

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!

Seems you do not access to do that. For which database you taking backup - your one or the master one ?

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.

> 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.

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?

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.

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.

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!

provide a new file name while taking backup and take full backup.

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!

I'll try it and report back.... thanks!

Awesome - the command worked.

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

BACKUP DATABASE databasename
TO DISK='\\networksharename\databasename.bak'

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' 

Select allOpen in new window

Works great, thanks! Still not sure why I couldn't do this with the GUI... but ALL GOOD!