I’m currently working on a PowerShell/SQL set of scripts to ease SQL Server Migrations. A few of the scripts have been posted to Microsoft’s ScriptCenter. Recently, one of the ScriptCenter visitors asked:
Can this script be used to backup all server configuration and restore at a later date. The use case I have is that I have a SQL 2012 Enterprise cluster and need to downgrade it to SQL 2012 Standard. A key requirement is to maintain the same IPs/SQL instance names/virtual names etc. Our method will be to uninstall and reinstall SQL and we need a reliable method to backup and then restore the databases/logins/server configuration.
My response got cut off, so I’m posting it here.
Start-SQLMigration.ps1 can backup key elements of the server configuration, but cannot restore databases from a server that is offline. I’m currently working on a separate script that would likely help in that scenario, but it will be a bit before it’s released.
Fortunately, I just tested the following in my lab and was successful in downgrading my Enterprise Edition cluster to Standard. Here are the steps I took:
- Took note at the exact version of SQL Server, down to the build number
- Made backups of all databases, including the system databases
- Stopped the SQL server role within the Failover Cluster Manager
- Once the disks went offline within Windows, took a storage-level snapshot of the shared disks. I named this snap “ent version”.
- Copied the system databases, and their logs (mdfs and ldfs) to backup directories just in case the snapshot didn’t work (one can never be too safe)
- Used VMware to snapshot both Windows nodes (alternatively, you can shut them down and snap them at the storage level)
- Uninstalled SQL Server from both nodes using the Remove Node from Cluster option. This will delete the system dbs, but not the user dbs.
- Deleted templog.ldf from my system directory (it was the only one that the uninstall didn’t get rid of)
- Reinstalled SQL Server Standard exactly as Enterprise was before on both nodes – down to the IP, service account, name, directory structure, filestream share etc.
- Patched both nodes of SQL Server until I was back at the same build
- Ensured the SQL role was stopped within the cluster and that disks were offline
- Snapshotted the storage again, naming this snap “std version”
- Restored storage back to “ent version”
- Started SQL role within the cluster
- Voila! The version was now SQL Standard and all of my dbs/logins/settings were just as they were.
Of course, you should not do this without testing it in your dev environment first.