Things to be done after Cloning SQL Server... Taking OS Level Image of SQL Server...

Sometimes you may get this requirement... Windows team may clone the SQL Server and ask you to bring up SQL Server in new cloned server since old server will retire soon... then? Below steps will help you

1) After cloning when you start the SQL Server you may get “Cannot find the path specified”. Incase you get this go to SystemàCurrentControlSetàServiceàMSSQLSERVER and change the directory.

2) Now when you start the SQL Server wherever the transaction logs are inconsistent those databases will go into suspect mode. So run below query to bring those databases into Emergency mode so that when you restart SQL Server it will not check for log files.

Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysdatabases set status = 32768 where name ='msdb'
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO

3) Now stop the Service and then renamed the LDF of all the databases which have gone into suspect mode. Start SQL Server service and run below commands to create new LDF files for those databases.

DBCC REBUILD_LOG(msdb,'D:\MSSQL\Data\msdblog.ldf')

You will get below output
Warning: The log for database 'msdb' has been rebuilt. Transactional consistency has been lost. DBCC CHECKDB should be run to validate physical consistency. Database options will have to be reset, and extra log files may need to be deleted.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

4) Now after repeating above steps for all suspect databases, reset the status to online by running below command.

Execute sp_resetstatus msdb

You will get below output
Prior to updating sysdatabases entry for database 'msdb', mode = 0 and status = 2048 (status suspect_bit = 0).No row in sysdatabases was updated because mode and status are already correctly reset. No error and no changes made.

5) Now we need to change the @@SERVERNAME to point to new server. But you cannot run the sp_dropserver and sp_addserver incase you have this as publisher in replication. In this case you have to update system tables using below command. You need to do this in all the columns needed in srvid=0 for local server and srvid=1 for distributor server.

Sp_configure "allow updates", 1
go
Reconfigure with override
GO
Update sysservers set datasource = ABCDEF-1' where srvid=1
go
Sp_configure "allow updates", 0
go
Reconfigure with override
GO

6) Now also you have to update MSdistpublishers table in the msdb where the distributor server name will be stored.

7) Now you need to update the originating_server column in msdb which will have reference to old server

8) Now Restart SQL Server and everything should work fine…