1

I have some problems moving a database to another computer having tried both manual-file-copy with attach in SSMS and backup/restore in SSMS (Moving SQL Server 2019 database to another computer).

However the entire database is only a few tables and 15 megabytes.

So why not simply export to pure SQL I am thinking... That could work for me in this case. So in SSMS I right click database, tasks and select export data...

It appears the closest I can get in SSMS is picking "flatfile" which is essentially a CSV file which can contain single table... I want a SQL file that can create database, tables, add data etc.

What am I overlooking? Is this not possible in SSMS?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tom
  • 3,587
  • 9
  • 69
  • 124
  • 1
    You can get SSMS to script out the database objects as text fikei, but moving data is tricky as there may be referential integrity issues (foreign key constaints, inserting Identity column values etc ) it may be better to give us more details of why the backup-restore is not working; possibly diff between std and dev edition, collation or compatability level – Aaron Reese Apr 15 '22 at 07:31
  • 1
    Have a look at this: https://stackoverflow.com/q/6183139/87698 It's about moving a DB to an older SQL Server, but the same technique should work for moving it to another PC. The script solution you want is mentioned in the second answer, but there are other approaches mentioned as well that could work for you. – Heinzi Apr 15 '22 at 07:47
  • @Aaron See https://stackoverflow.com/questions/71878684/moving-sql-server-2019-database-to-another-computer backup/restore = sector size error. Attach freezes/crashes MSSQL server 2019. Thus I am left with... trying pure .SQL file. The good thing is that it is a simple database with only two tables. (Customers/Sales for own use so no overly fancy stuff) – Tom Apr 15 '22 at 08:39
  • 1
    @Heinzi Sounds promising generating script and select schema plus data in advanced options. Sounds like the solution I am looking for. It generates a 35MB file which is executing right now on my new machine. I will report back later if it works... – Tom Apr 15 '22 at 08:59
  • 1
    Sector size error is most likely due to running it on Windows 11 (or Server 2022) and a different hard disk sector size. Has been documented as an issue already, and creating a new database on the same drive will not help. See https://dba.stackexchange.com/questions/303823/error-starting-localdb-on-windows-11 – Charlieface Apr 15 '22 at 13:03
  • @Charlieface You appear to be correct. I can not create a database myself either on the disk... – Tom Apr 17 '22 at 12:58
  • See https://stackoverflow.com/questions/70949657/sql-server-2019-installation-windows-11-wait-on-the-database-engine-recovery-ha – Charlieface Apr 18 '22 at 01:31

0 Answers0