October 29, 2014 at 12:30 pm
Hi All
We had our backups backing up to the server where the databases reside. Now I modified the backups to backup to a file share. Now when we try to restore from the file share the restore fails, so we have to copy the backup to a drive on the server and recover for there. Should I be able to restore directly from the file share (using the gui)? Do I need to change something else to modify the default backup drive? Any input?
Thanks
Kathy
October 29, 2014 at 12:37 pm
What error are you getting when you try to restore?
October 29, 2014 at 12:44 pm
Restore of database 'dbname' failed. System.Data.SqlError: Cannot open backup device 'default drive name' (The system cannot find the file specified.), (Microsoft.SqlServer.SmoExtended).
This is the error I am receiving.
Thanks
Kathy
October 29, 2014 at 12:54 pm
When you say you modified the backups to back up to a file share, did you actually change the backup job to point to a file share or did you move the existing backups to a file share? The reason I'm asking is to determine if the necessary permissions exist to access the file share.
Take a look at the security context under which the SQL Server service is running. Does that domain account have permission to the file share where you're trying to restore from?
October 29, 2014 at 1:04 pm
I did request the security on this fileshare but now I'm looking at it, I don't see the security I will need. I will request this again and see what happens.
Thanks a lot!
Kathy
October 29, 2014 at 1:34 pm
No problem. Glad I could help.
October 31, 2014 at 12:30 pm
Ok now I have permissions setup on the fileshare folder. When I try and do a restore using the ssms gui and choose source from Device my fileshare is not listed in the Backup media. Does anyone know why this is happening?
Thanks
Kathy
November 3, 2014 at 7:10 am
Enter the full unc path for the remote location in the path section of the GUI. I.e.
\\RemoteServerName\ShareLocation\BackupFileName.bak
If the permissions are setup correctly, then SQL Server should be able to restore the file.
November 4, 2014 at 1:04 pm
What do you do when you have to restore a full backup and then a differential and a log? I am able to restore the full backup but I end up breaking the backup chain.
Thanks
Kathy
November 4, 2014 at 2:58 pm
You restore the full with no recovery, then the diff with no recovery, then the logs, all with no recovery except the last one.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 5, 2014 at 10:34 am
Thanks for the information! I have responsibility of sql server 6.5, 7.0, 2000, 2005, 2008R2, and 2012. I have been able to get the 7.0 backups onto the fileshare. I have been trying to restore a 2000 database from the fileshare with no luck. Do you know if I have to restore to 2005 and then create schema of db on 2000 and 7.0 and try to import/export the data? I know this is 2012 so forgive me.
Thanks
Kathy
November 6, 2014 at 2:18 am
Is the SQL 2000 instance running with a domain account and does this domain account have read access to the file share where the backup file is?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply