How to fully recreate database in MS SQL server 2016 from exported data Recently I get many consistency errors very often in my ~40Gb database. Of course I use DBCC CHECKDB, even with REPAIR_ALLOW_DATA_LOSS (when it asks), but then again I get errors, even after read-only operations.

Database itself is stored on SSD drive which is ok, so I suspect there are some hidden errors in database file.

So, the full cycle: I have MSSQL 2016 on local PC, with only 1 user attached. Every day I get data (~1.5M records) in custom format, backup base and insert data to the base (non-bulk inserts, indexes on). During that operation no one touches the base. Later from time to time I make high load queries (different statistics). And often I get consistency errors, check sum errors, index errors (don't have exact output stored in the moment). Index errors can be corrected by rebuilding indexes; others can only be fixed with DBCC CHECKDB using the REPAIR_ALLOW_DATA_LOSS option. After that, it works again - for a little while.

I don't believe the problem is with my SSD drive. I have tried different drives on this PC over the past few years, with no change in behavior.

I suspect that, even after running DBCC CHECKDB successfully, there are some hidden errors in file structure which lead to new errors later. So I want extract only the data to linear file and then import it. Standard Export wizard exports hundreds of lines and then stopped without error.

Is there a correct and robust way to export the whole DB to text/binary file(s) (but not mssql) and then recreate database without data losses from those file(s)? I tried backup-restore, but it looks like database consistency problems persist through restoration.
This is only a summary of some vague assumptions as a lot of details that should be added to the question were either discussed in a chat or have not yet been asked.


** Before you continue**

Create a database backup (WITH CHECKSUM) and store this database in a safe location. Even better: Seeing as your disk might be faulty, backup the database (WITH CHECKSUM) to an external drive and store in a safe location.

Possible reasons for data corruption on data growth




Your SSD disk has corrupt areas. DBCC CHECKDB... corrects the data (albeit with REPAIR_ALLOW_DATA_LOSS). Adding data places it in the corrupt areas of the SSD even though the SSD controller unit should avoid corrupt areas (once marked).


Solution: Check SSD S.M.A.R.T. parameters for errors and/or replace SSD.  

Reference: How to See If Your Hard Drive Is Dying with S.M.A.R.T. (How-To Geek Site)




Your motherboard's disk controller (SATA interface) is having issues handling the large amount of data. 


Solution: Replace disk controller and/or update disk controller driver.




The disk and/or disk controller is set to use a WRITE CACHE mechanism which is unable to keep up with the import of large amounts of data. The corruption is occurring on flushing. 


Solution: Turn off (disk) cache mechanisms.  

Reference: Disable Windows Disk Write Cache for Data Integrity and Better Performance (Long White Virtual Clouds)



General Reference: Data corruption (Wikipedia)