Page Comparison

...

1. Disable SQL Agent service to prevent backups and other scheduled jobs from interrupting
2. Shrink databases + log

   Shrinking is optional, but reducing database sizes will make backups smaller and faster. Do this for each database of significant size or interest.

   Code Block
   language sql
   DBCC SHRINKFILE (N'logicalfilenamefordata' , 100) GO DBCC SHRINKFILE (N'logicalfilenameforlog' , 100) GO

   
   

3. Backup all non-system databases
4. Backup msdb
5. Backup master
6. Backup model
7. Stash backups away for safety - eg: into a c:\backups folder
8. Stop SQL Services via services.msc
9. Uninstall SQL Server and associated components - Take note of installed features (so when install of x64 is done, we match features - eg: reporting services, et al). Note the databases in C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.<instance name>\MSSQL will stay where they are - the new x64 SQL will still use these.
10. Reboot
11. Install SQL x64 - Make sure to set mixed mode authentication and provide the sa password. Also make sure you match the features from step 9.
12. Reboot
13. Restore system databases
    1. Put server into single user mode
       1. Stop SQL server agent
       2. Stop the SQL Service using services.msc

       3. Using an elevated command prompt, start in single user mode 

          Code Block
          language sql
          CD C:\Program Files\Microsoft SQL Server\MSSQL11.<instance name>\MSSQL\Binn sqlservr.exe -m -s<instance name>

          
          

    2. Restore Master database from backup by running SQLCMD.exe from an elevated command prompt

       Code Block
       language sql
       CD C:\Program Files\Microsoft SQL Server\110\Tools\Binn SQLCMD.EXE -S .\<instance name> -U sa -P <sa password> 1>RESTORE DATABASE master FROM DISK = 'C:\backups\master.bak' WITH REPLACE 2>GO

       
       

    3. Copy model database + log (model.mdf and modellog.ldf) from new x64 folder back  to old x86 folder - ie: 
       FROM 
       C:\Program Files\Microsoft SQL Server\MSSQL11.<instance name>\MSSQL\DATA 
       TO 
       C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.<instance name>\MSSQL\DATA 
    4. Copy MSDB database + log (MSDBData.mdf and MSDBLog.ldf)  from x64 back to old x86 folder - ie: 
       FROM 
       C:\Program Files\Microsoft SQL Server\MSSQL11.<instance name>\MSSQL\DATA 
       TO 
       C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.<instance name>\MSSQL\DATA
    5. Start SQL Service from services.msc, make sure SQL Agent stopped
    6. Login using Management Studio
    7. Right click msdb, choose properties - Options and change "Restrict Access" option to "SINGLE_USER"

    8. Restore msdb using new query in management studio:

       Code Block
       language sql
       USE master GO RESTORE DATABASE [msdb] FROM DISK = N'C:\backups\msdb.bak' WITH REPLACE GO

       
       

    9. msdb should automatically go back to "Restrict Access" option value of MULTI_USER when restored.
    10. Start SQL Agent using services.msc
14. Rebuild indexes and update statistics for all non system databases - see Improve database performance
15. Verify applications
    
    1. Should be able to login to jiwa databases using the Jiwa application with no issues
    2. Other applications using other database should also work
16. Verify SQL jobs (eg: backups) are present and enabled.

...