0

I have a working database consisting of a .mdf file and .log file on one computer.

I try to move it to another using the exact same setup, disks and paths.

On the new computer I start Microsoft SQL Server Management Studio and connect.

Here I try to "Attach" database and select the .mdf file in the dialog, but then I get an error saying:

An error occurred when attaching the database(s). click the hyperlink in the Message column for details

It even seems to crash the server. And it appears I need to reboot. Here are two screenshots. One before and after clicking "OK" and opening the message hyperlink which I had not seen before:

Before:

enter image description here

Error:

enter image description here

Note: The 2008 refers when I created the database. But it is running in SQL Server 2019 Express on the old computer.

... ALTERNATIVE METHOD ...

I figured I would try use backup/restore, but then I get an error about sector size being different.

I am using SSMS for this. But I have tried a few script adjustments SSMS generated. Here is what it generates by default when clicking "Script" button:

BACKUP DATABASE [SharewareSalesMS] 
TO DISK = N'W:\b-databases\MsSql\2019-SSMS-BACKUP-NEWEST\SharewareSalesMS.bak' 
WITH NOFORMAT, NOINIT,  
     NAME = N'SharewareSalesMS-Full Database Backup', 
     SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
Tom
  • 3,587
  • 9
  • 69
  • 124
  • Please share a screenshot of where you see the error message. – Dai Apr 14 '22 at 23:53
  • Are you using the Backup/Restore dialogs in SSMS or are you using T-SQL's backup/restore commands directly? If you're using the dialogs, then don't click "OK", instead click the "Generate scripts" button and then that will generate T-SQL you can run - please share that script with us as well as showing us **all** output you get. – Dai Apr 14 '22 at 23:54
  • @Dai I have added screenshots. It seems I had missed I could see the full error. That said not sure it makes much sense. As mentioned it appears the server crashes. The rror message kinda hints that. But I am not sure at all how to interpret this. – Tom Apr 15 '22 at 00:23
  • @Dai I have included the SQL executed to generated the backup (for the backup/restore method instead of copying database files and using attach) – Tom Apr 15 '22 at 00:24
  • How **big** was the original database (size of the `.mdf` file) before you took the backup? I'm asking because SQL Server Express has a 10 GB limitation .... I would think you should get a different error if that were the problem - but still worth checking .... – marc_s Apr 15 '22 at 05:26
  • @marc_d SharewareSalesMS_Data.MDF = 14.5 megabyte and SharewareSalesMS_Log.LDF = 10,0 megabyte sop size should not be an issue. .... Maybe I should try export as a pure SQL backup and import/execute all SQL? – Tom Apr 15 '22 at 06:55
  • (I am creating a separate SO about exporting to pure .SQL file in SSMS because that apparently is also not possible. https://stackoverflow.com/questions/71881152/export-ms-sql-server-2019-database-to-pure-sql-utf8) – Tom Apr 15 '22 at 07:19
  • What are the logs for that crash (as it seems to be), check both SQL Server logs and Windows logs (Application) – Charlieface Apr 15 '22 at 13:09
  • I believe I figured it out... I am using a new samsung m.2 ssd disk that apparent reports a huge sector size to Windows. And MS SQL 2019 does not work on large sector sizes... – Tom Apr 17 '22 at 12:57
  • I would like to answer this question myself but not allowed to. But reason is MySql2019 crashes because disk reports wrong sector size. – Tom Apr 18 '22 at 16:12

0 Answers0