Migrate SQL database / change location of UC4 Data file (.mdf) to another one

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

Migrate SQL database / change location of UC4 Data file (.mdf) to another one

Environment

Release: AOATAM99000-9.0-Automic-One Automation Tools-Application Manager
Component:

Resolution

Detailed Description and Symptoms

Need to move UC4 data file to another location for migrating purpose 

Investigation

- Use Attach/Detach function of SQL Server. \

- Moving SQL Server data file does not impact UC4 System since connection between them via ODBC 

Solution

- Reference articles: http://support.microsoft.com/kb/224071

Note You can determine the name and the current location of all files that a database uses by using the sp_helpfilestored procedure:

use <database_name>gosp_helpfilego

1. Start SQL Server 2005 Management Studio. To do this, click Start, click All Programs, click Microsoft SQL Server 2005, and then click SQL Server Management Studio.
2. Click New Query, and then detach the database as follows:

   use master   go   sp_detach_db 'mydb'   go

3. Copy the data files and the log files from the current location (D:\Mssql7\Data) to the new location (E:\Sqldata).
4. Reattach the database. Point to the files in the new location as follows:

   use master  go  sp_attach_db 'mydb','E:\Sqldata\mydbdata.mdf','E:\Sqldata\mydblog.ldf'  go

   Verify the change in file locations by using the sp_helpfile stored procedure:

   use mydb   go   sp_helpfile   go

   The filename column values should reflect the new locations.