I am assisting a client who has recently retired from his original business and is setting up a smaller professional property management partnership.

He has been instrumental in setting up a tailored CRM system in the original business, originally in access, and then with a large table running in SQL Server 2017. It has been suggested that he continue development in his new business, using SQL Express.

I have created a backup of the 2017 database, and installed SQL Express 2022. To restore the backup to this installation, I need to start Express in single-user mode. I have not succeeded - there are a number of 'system' accounts, eg SQLWriter, Winmanagement, and I can only guess hat one of these manages to log in as the 'single user' before I can log in with my client's local account?

Can I temporarily set the various 'system' accounts in some way to enable a restore, and then put them back to their original staus?

  • 1
    Accordingly to your description you try to restore an application database into SQL Server express. Why exactly do you need a single user mode for it? Mar 8, 2023 at 12:06
  • Also, how big is the current database coming from SQL Server 2017?...it's worth noting SQL Server Express Edition has a limit of 10 GB per database, which is obviously not a lot these days.
    – J.D.
    Mar 8, 2023 at 13:05
  • You don't need to start it in single user mode unless you are restoring master. To just restore a user database you can set that to single-user ALTER DATABASE YourDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;. At a pinch, you could just disable remote connections or firewall it off. Mar 8, 2023 at 14:39
  • Thanks, all - I should confess to having very little SQL Server experience. I actually only need 1 table transferred - it is the major data store for a CRM system originally developed in Access. It outgrew Access some time ago, but the remainder of the system is still in Access. The table (3.5 Gb) was created (perhaps wrongly?) in Master - is it possible just to export the table, and import it into SQL Express? A Backup/Restore of Master was the only route I could find.
    – Frank Kay
    Mar 8, 2023 at 16:52
  • @FrankKay, master is a very basic system database, if you replace it (in your case) your SQL server will not start anymore. It should not contain user data. You can restore the master database in SQL Express as a user database using alternate name, create 2nd empty database and copy the data from the restored database into a new one. This is the easiest way in this case. Mar 9, 2023 at 7:59


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.