Upgrade

Upgrade Pre-upgrade Tasks Backup Databases:

sql Copy

-- Backup System Databases
BACKUP DATABASE [master] TO DISK = 'C:\Backup\master.bak';
BACKUP DATABASE [msdb] TO DISK = 'C:\Backup\msdb.bak';
-- Backup User Databases
BACKUP DATABASE [YourDB] TO DISK = 'C:\Backup\YourDB.bak';

Check Compatibility:

sql Copy

-- Run Upgrade Advisor
-- Document Configuration Settings
SELECT *
FROM sys.configurations;

Upgrade Process In-Place Upgrade:

powershell Copy

# Run Setup.exe with upgrade parameters
Setup.exe /Q /IACCEPTSQLSERVERLICENSETERMS /ACTION=Upgrade
  /INSTANCENAME=MSSQLSERVER

Side-by-Side Migration:

sql Copy

-- Generate Scripts for Objects
-- Backup and Restore to New Instance
-- Transfer Logins and Jobs

Post-upgrade Tasks Verify Databases:

sql Copy

-- Check Database Status
SELECT name, state_desc 
FROM sys.databases;
-- Update Statistics
EXEC sp_updatestats;

Rollback Plan

sql Copy

-- Stop SQL Services
NET STOP MSSQLSERVER
-- Restore System Databases
RESTORE DATABASE [master] FROM DISK = 'C:\Backup\master.bak';
RESTORE DATABASE [msdb] FROM DISK = 'C:\Backup\msdb.bak';
-- Restore User Databases
RESTORE DATABASE [YourDB] FROM DISK = 'C:\Backup\YourDB.bak';