July 11, 2016 at 7:43 pm
I have been trying to move a SQL database from one PC to another PC without success. Any help or suggestion will be greatly appreciated.
Here are the descriptions of the problems I encountered.
Source PC: Windows 10
SQL Server: Microsoft SQL Server 2012 (SP3) - 11.0.6020.0 (X64); developer edition
DB size: < 1GB
Target PC: Windows 7 Pro
SQL Server: exactly same as SQL version on source PC
I tried 2 different methods:
1) Create full backup from source server & restore it to target server
2) Take source DB offline and copy .mdf & .ldf files to target PC. Try to setup database on target using "attach" option.
Unfortunately, both methods were failed. I didn’t try “copy database” because 2 PCs are not connected to the same network.
When using "backup & restart" method, I manually created the db on target PC with same name and similar size. I copied the .bak file to c:\temp folder on target PC. I also give everyone full control permission to the c:\temp folder. I tried to avoid any file access permission problem. After I selected “device” and .bak file as Source, the “No backupset selected to be restored” warning was displayed near the top of the “Restart Database” window. I selected newly created DB as destination. The “OK” button remained as disabled. I don’t think the .bak file was corrupted because I was able to use it for restoring the same db on source server.
When using “copy .mdf & .ldf” method, I first deleted the DB I created, coped .mdf & ldf files into the SQL Data directory. The next step was select “attach” from the “Tasks” menu and selected the .mdf file. The error message was displayed immediately:
----------------------------------------------------------------------------------------------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
Additional information
An exception occurred while executing a Transact-SQL statement or batch
(Microsft.SqlServer.ConnectionInfo)
C:\temp\data\bioope.mdf is not a primary database file. (Microsoft SQL server, error 5171)
--------------------------------------------------------------------------------------------------------------------------
What did I do wrong when using these 2 methods? Please help!!!
July 11, 2016 at 9:05 pm
Did you ensure a valid path was listed for the files? The backup file looks for the original path for the files.
If you went to restore over an existing database, is the "Take tail log backup" still checked?
There's no need to create the db. Just click restore, select the .bak. You can check the "move files to new location" on the options tab. Make sure you have a backup checked in the lower part of the main dialog. The list of backups inside the .bak file appear, and you need to select one.
July 12, 2016 at 12:11 am
take a look at this link: https://support.microsoft.com/en-us/kb/314546
July 12, 2016 at 10:11 am
thanks for the advice.
Today, I found the root cause of the DB restore failure. The .bak file generated on the source PC was valid. The file was corrupted when being copied from the source PC to the thumb drive. The file properties showed the slight difference between file size and file size on disk.
Once the .bak file correctly copied to the target PC, the restore was completed successfully.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply