May 4, 2018 at 9:05 am
Hello All,
1st time poster who's banging my head against the wall with the scenario mentioned in the subject. I'll first review the scenario, then what I've tried so far in testing and my results, and hopefully you can point me in the right direction.
Scenario
A copy of SQL Server 2012 Express 32-bit was installed as the database for Autodesk Vault. Installing the 32-bit version of SQL Server 2012 Express was a mistake and the folks using Vault are seeing performance issues as a result. I've been tasked with upgrading the installation to SQL Server 2012 Express 64-bit (and then upgrade to a newer version of SQL Server Express). This is installed on a Microsoft Server 2012 R2 box.
Proposed Resolution
I have full .bak backup files of all the user databases (Vault & KnowlegeVaultMaster) and all of the system databases (msdb, model, master). From my research & testing so far, I can install the 64-bit version of SQL Server Express 2012 and restore these backups to that installation.
After restoring the database backups to the new 64-bit installation, along with migrating the logins (as detailed here: https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server ), I would expect to have a working installation. However, I've run into some issues.
Issues
I've been testing this on a non-production system with limited success. Restoring the user databases goes without issue, as does restoring msdb & model. However any attempts to restore the master database (either as the 1st database restored or the final database restored) causes my SQL Server 2012 Express 64-bit installation to break.
Steps:
Take full backups of all databases on the 32-bit install of SQL Server 2012 Express.
Run a script to capture the logins (as linked above)
Uninstall 32-bit version of SQL Server
Install 64-bit version at same revision (2012 Express)
I start SQL Server in single-user mode (by adding -m to the startup parameter & stopping/restarting the SQL Server services).
Then run the query to restore master (RESTORE DATABASE master FROM disk ='pathtofile' WITH REPLACE). The restore goes fine.
I then stop the SQL Server service, remove the -m parameter and attempt to restart the SQL Server service. At this point the service *will not start*.
Looking at the ERRORLOG file, it indicates it can't open up model, msdb, or the user databases because it is looking for them in the old 32-bit location: C:\Program Files (x86)\Microsoft SQL Server\<InstanceName>\MSQL\DATA\. I think the inability to open model is the real show-stopper as there is where the log ends:
spid9s FCB::Open failed: Could not open file C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.<instancename>\MSSQL\DATA\model.mdf for file number 1. OS error: 3(The system cannot find the path specified.).
spid9s Error: 5120, Severity: 16, State: 101.
spid9s Unable to open the physical file "C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.<instancename>\MSSQL\DATA\model.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
spid9s Error: 17207, Severity: 16, State: 1.
spid9s FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.<instancename>\MSSQL\DATA\modellog.ldf'. Diagnose and correct the operating system error, and retry the operation.
spid9s File activation failure. The physical file name "C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.<instancename>\MSSQL\DATA\modellog.ldf" may be incorrect.
spid9s Error: 945, Severity: 14, State: 2.
spid9s Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
spid9s SQL Server shutdown has been initiated
spid9s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
Any thoughts on:
A better way to undertake this migration?
What I might be doing wrong?
How to resolve the inability of SQL Server service to start-up after the master db restore?
Do I actually need to restore the master database - I'm not sure what the implication would be of only restoring the user databses and the logins on the new 64-bit installation?
Would be happy to share any other logs or information. Thanks in advance for any assistance.
Troy
May 4, 2018 at 9:39 am
The master database contains server level objects like logins, linked servers, certificates, a few other things. There isn't a need to restore this if you don't necessarily need those items. Typically this is restored when you need those items and haven't necessarily scripted them in a DR like situation. You do this because your pathing, your naming, everything is the same.
I'd skip master in your case.
May 4, 2018 at 12:16 pm
Steve Jones - SSC Editor - Friday, May 4, 2018 9:39 AMThe master database contains server level objects like logins, linked servers, certificates, a few other things. There isn't a need to restore this if you don't necessarily need those items. Typically this is restored when you need those items and haven't necessarily scripted them in a DR like situation. You do this because your pathing, your naming, everything is the same.I'd skip master in your case.
Thanks for the input, Steve, it is much appreciated. This was the general consensus around my office, however we have no SQL Server "gurus" on staff that could say definitively.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy