"How do I move the database files to a"
How do I move the database files to a new location once I’ve installed them using the CHAMP install procedure? Summary: When you install the CHAMP server software it automatically installs the SQL databases to your local C: drive without giving you the option to install them anywhere else. You may want to move your database files to another drive to free up space on your C: drive or to improve your SQL server efficiency. Solution: You will need • ServerTools utility, found at http://www.champsoftware.com/support/servertools.html o Version 1.1, Build 245 or greater (view version in Add/Remove Programs) o Instructions (PDF file) also found at the above. • The SA password for the SQL Server Instructions 1) Make sure all charts are checked in (Transfer feature in CHAMP). Changes made to charts on laptops will be lost if not checked in. 2) Ensure that you have recently done a backup of your database. 3) Make sure all users are logged out of CHAMP. 4) Open a command prompt a. Navigate to c:\Program Files\CHAMP\ServerTools b. Run this command: change_schema /d /r /s [ServerName] /p [SA password] Example: change_schema /d /r /s myserver /p mypassword 5) Run these commands to detach your databases. a. osql –S [ServerName] –U sa –P [SA password] –I –Q “EXEC sp_detach_db @dbname = ‘champ_v2’” Note: The very end of the command is a single quote ‘ followed by a double quote “ b. osql –S [ServerName] –U sa –P [SA password] –I –Q “EXEC sp_detach_db @dbname = ‘champ_v2_training’” c. osql –S [ServerName] –U sa –P [SA password] –I –Q “EXEC sp_detach_db @dbname = ‘champ_v2_staging’” * the third command will come back with an error if you don’t have a staging database, that is just fine. Example: osql –S myserver –U sa –P mypassword –Q “EXEC sp_detach_db @dbname = ‘champ_v2’” 6) After all the databases have been detached minimize the command. window. 7) Open windows explorer and navigate to C:\Program Files\Microsoft SQL Server\MSSQL\Data. 8) View file extensions by going to Tools Folder Options, go to the View tab and uncheck “Hide extensions for known file types” click OK. 9) If you have the Champ_v2_staging.mdf and Champ_v2_staging_log.ldf files delete them. Yes I said delete them; they are old database files used from the conversion to HIPAA V2 and are no longer needed. 10) Select these files. a. Champ_v2.mdf b. Champ_v2_log.ldf c. Champ_v2_training.mdf d. Champ_v2_training_log.ldf 11) Copy these files to the drive and location you want to store the databases on. Tip: Storing the MDF and LDF files on separate drives will increase SQL Server efficiency. 12) Restore your command window and run these commands. a. osql –S [ServerName] –U sa –P [SA password] –I –Q “EXEC sp_attach_db @dbname = ‘champ_v2’, @filename1 = ‘[Path to where you moved the MDF files]\champ_v2.mdf’, @filename2 = ‘[Path to where you moved the LDF files]\Champ_v2_log.ldf’” b. osql –S [ServerName] –U sa –P [SA password] –I –Q “EXEC sp_attach_db @dbname = ‘champ_v2_training’, @filename1 = ‘[Path to where you moved the MDF files]\champ_v2_training.mdf’, @filename2 = ‘[Path to where you moved the LDF files]\Champ_v2_training_log.ldf’” Example: osql –S myserver –U sa –P mypassword –I –Q “EXEC sp_attach_db @dbname = ‘champ_v2’, @filename1 = ‘D:\MDFfiles\champ_v2.mdf’, @filename2 = ‘G:\LDFfiles\champ_v2_log.ldf’” 13) If you have laptops that use CHAMP then follow these steps: a. Change to the C:\Program Files\CHAMP\ServerTools directory. b. Type: change_schema /e /f \\[servername]\[sharename]\champwin\snapshot /s [servername] /p [sa password] Where [sharename] is the name of the share that CHAMP uses. Example: change_schema /e /f \\myserver\shared\champwin\snapshot /s myserver /p mypassword c. Once the previous command completes, type: snapshotgen [servername] [sa password] After about 10 seconds a progress bar should appear and create the new snapshots. Example: snapshotgen myserver mypassword You have now successfully moved your database files to a new location. Please contact CHAMP support at 507.388.4141 if you have any problems or questions.