I'm confused on the process or maybe I'm just doing it wrong.
I right clicked a db using management studio selected backup. On the dialogue window I choose type Full and checked the box, Copy-only backup (I'm moving to a new hardware
I clicked okay and wound up with mybackup.bak
On my new hardware I created a new db with the same name as my backup, right clicked Databases node in my instance and chose restore files and groups. I picked To database from the drop down and from Device and pointed to my .bak file clicked ok but then
I immediately got an error
TITLE: Microsoft SQL Server Management Studio
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
I was logged in using windows authentication on both machines and with an admin user. Which permissions should I check exactly?
If I simply copy the backup file in the same folder and choose the copy file during the restore I do not get the error after choosing it but I'll get an error when I try to click OK to proceed with the import (after having checked the box in the restore
column). And then I get a different error
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'SIMPLYEMAIL'. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The backup set holds a backup of a database other than the existing 'wombo_combo' database.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3154)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=3154&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Is there a way when I create the backup in the first place I can have an option to create the DB as well?
So this time I created a backup from the same server and compared the permissions of a file it creates vs what I'm trying to restore from. So I see 2 users are not in my .bak files so I add the same exact ones as in the other file and when I attempt to
restore the backup I get this error
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'SIMPLYEMAIL'. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.WOMBOCOMBO\MSSQL\DATA\my-file.mdf'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
This is really frustrating, I've even gone as far as uninstall reinstalling.
I did a reinstall for the 3rd time. This time I changed the backup dir to d:\mybups instead of to the default isntall path of program files etc so forths
Try focus on backup file it selft, Check at the file properties, right click the backup file, and look at the security tab. Check that it can be accessed to your user-id, You may change also the owner property of yor file. Normally file from Windows Server
has limited access. But you can change it using with Admin previldge.
Hope this can help
Jannen Siahaan
Indonesian Humanitarian Foundation
Marked as answer by Ming Xu - MSFT on Apr 30, 2012 02:48 PM
Robert Black...
Member
31 Points
15 Posts
backing up and restoring
Apr 20, 2012 07:36 PM|LINK
I'm confused on the process or maybe I'm just doing it wrong.
I right clicked a db using management studio selected backup. On the dialogue window I choose type Full and checked the box, Copy-only backup (I'm moving to a new hardware
I clicked okay and wound up with mybackup.bak
On my new hardware I created a new db with the same name as my backup, right clicked Databases node in my instance and chose restore files and groups. I picked To database from the drop down and from Device and pointed to my .bak file clicked ok but then I immediately got an error
TITLE: Microsoft SQL Server Management Studio
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.WOMBOCOMBO\MSSQL\Backup\wombo_combo.bak'. Operating system error 5(Access is denied.).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=3201&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
What am I doing wrong here?
Thanks in advance
P.S. I forgot to mention, I'm using Express 2008 R2
limno
All-Star
117426 Points
8030 Posts
Moderator
MVP
Re: backing up and restoring
Apr 20, 2012 07:44 PM|LINK
1.Check your permission;
2. Create a bak file with a new name and try.
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
Robert Black...
Member
31 Points
15 Posts
Re: backing up and restoring
Apr 20, 2012 07:51 PM|LINK
I was logged in using windows authentication on both machines and with an admin user. Which permissions should I check exactly?
If I simply copy the backup file in the same folder and choose the copy file during the restore I do not get the error after choosing it but I'll get an error when I try to click OK to proceed with the import (after having checked the box in the restore column). And then I get a different error
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'SIMPLYEMAIL'. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The backup set holds a backup of a database other than the existing 'wombo_combo' database.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3154)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=3154&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Is there a way when I create the backup in the first place I can have an option to create the DB as well?
jsiahaan
Contributor
2550 Points
645 Posts
Re: backing up and restoring
Apr 20, 2012 10:04 PM|LINK
Hi,
You do not need to create a new database before you restore.
Delete the new database, and then restore without a new name. It will automatically using the same name.
My test:
All worked fine, hope can help
Indonesian Humanitarian Foundation
Robert Black...
Member
31 Points
15 Posts
Re: backing up and restoring
Apr 23, 2012 03:46 PM|LINK
Still unable to restore a backup. This time I tried creating the backup without checking the box that said full copy.
When I try to restore from file I get a slightly different error this time:
TITLE: Microsoft SQL Server Management Studio
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.WOMBOCOMBO\MSSQL\Backup\MY_FILE.bak'. Operating system error 5(Access is denied.).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=3201&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
I'm trying to restore from
C:\Program Files\Microsoft SQL Server\MSSQL10_50.WOMBOCOMBO\MSSQL\Backup
So this time I created a backup from the same server and compared the permissions of a file it creates vs what I'm trying to restore from. So I see 2 users are not in my .bak files so I add the same exact ones as in the other file and when I attempt to restore the backup I get this error
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'SIMPLYEMAIL'. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'c:\Program Files\Microsoft SQL Server\MSSQL10_50.WOMBOCOMBO\MSSQL\DATA\my-file.mdf'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
This is really frustrating, I've even gone as far as uninstall reinstalling.
limno
All-Star
117426 Points
8030 Posts
Moderator
MVP
Re: backing up and restoring
Apr 23, 2012 04:00 PM|LINK
You should be able to follow this thread to give the permission of your SQL Server Service Account to access your backup folder.
Read this long thread:
http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/thread/021c7aa5-4a8a-4bbb-8ff0-fe6b03920aae/
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
Robert Black...
Member
31 Points
15 Posts
Re: backing up and restoring
Apr 23, 2012 08:56 PM|LINK
I did a reinstall for the 3rd time. This time I changed the backup dir to d:\mybups instead of to the default isntall path of program files etc so forths
No problems now, this was insane.
jsiahaan
Contributor
2550 Points
645 Posts
Re: backing up and restoring
Apr 23, 2012 10:19 PM|LINK
Hi,
Try focus on backup file it selft, Check at the file properties, right click the backup file, and look at the security tab. Check that it can be accessed to your user-id, You may change also the owner property of yor file. Normally file from Windows Server has limited access. But you can change it using with Admin previldge.
Hope this can help
Indonesian Humanitarian Foundation