Steps involved in moving the Spotlight Statistics Repository and/or Playback Databases to a new instance. (4218320)

What are the steps involved in moving the Spotlight Statistics Repository (SSR) and/or Playback Databases from one server to another?

1. Detach the Playback and SSR databases from the old server and then reattach them to new server:

a. In SQL Server Management Studio (SSMS), open database folder
b. Right click on a database (Playback or SSR) go to Tasks | Detach
c. Copy the data and log files to the destination server
d. On the destination server SSMS, right click on Databases and select Attach
e. On the Attach Databases dialog, click on Add and specify the file to the database data file.
f. Click Ok | Ok

2. Open your Spotlight on SQL Server Enterprise client (SoSSE) and reconfigure it to store Playback and SSR data on the new location:

a. In Spotlight client application go to Configure | Diagnostic Server | Playback database or Statistics Repository database option
b. Reconfigure Spotlight to point to the new Playback and SSR databases on the new destination server

You can also use the below script to detach, copy and attach your databases on the new destination server

----Step 1 : Detach Database using following script
USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = Ndatabase_name,
@keepfulltextindexfile = Ntrue
GO

----Step 2 : Move Data files and Log files to new location

Step 3 : Attach Database using following script
USE [master]
GO
CREATE DATABASE [database_name] ON
( FILENAME = NC:\Database_name_Data.mdf ),
( FILENAME = NC:\Database_name_Log.ldf )
FOR ATTACH
GO
IF EXISTS ( SELECT name
FROM master.sys.databases sd
WHERE name = Ndatabase_name
AND SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )
EXEC [Database_name].dbo.sp_changedbowner @loginame=Nsa,
@map=false
GO

Refer tohttps://support.quest.com/SUPPORT/index?page=solution&id=SOL33904&pmv=trueandhttps://support.quest.com/SUPPORT/index?page=solution&id=SOL37968&pmv=trueon moving diagnostic server data.