I don't think my initial solution will work with just changing the instance in the database connection file. I believe the database connection information is stored within the layer within the MXD. You could use the following script to update your MXDs with the new database connection to the new SQL Server instance:
import arcpy, os
from arcpy import env
from arcpy import mapping
env.workspace = r"D:\Temp\Python\MXDs"
newDataSource = r"Database Connections\SQLSERVER - DBO@GIS.sde"
env.overwriteOutput = True
for (path, dirs, files) in os.walk(env.workspace):
for file in files:
if ".mxd" in file.lower():
mxd = os.path.join(path, file)
print(mxd + " is being processed")
mxd = mapping.MapDocument(mxd)
for df in mapping.ListDataFrames(mxd, "*"):
for lyr in mapping.ListLayers(mxd, "*", df):
try:
if lyr.supports("DATASOURCE"):
if lyr.serviceProperties['Database']:
print("\tUpdating " + str(lyr))
lyr.replaceDataSource(newDataSource, "SDE_WORKSPACE", "")
print("\tSuccessfully updated data source")
except AttributeError:
pass
mxd.save()
del mxd
The services will have to be re-published afterwards as well.