Welcome to Database School

SQL Server Database Cloning and Backup Automation

Industry: Healthcare IT

Platform: Microsoft SQL Server

  1. Project Title:
Automated T-SQL Script to Clone SQL Server Database and Relocate MDF/LDF Files

  1. Objective:
To create a reliable, script-based solution for cloning the emr4_newcustomer SQL Server database and preparing a new database instance for onboarding a new client, with controlled file placement for mdf and ldf.

  1. Project Description:
The client requested a T-SQL script that would:
  1. a) Clone an existing EMR (Electronic Medical Records) database.
  2. b) Allow new .mdf and .ldf files to be stored in a custom directory (G:\data).
  3. c) Use an existing .bak file located in (G:\backups\full).
  4. d) Ensure that the database schema and data (if needed) were transferred.
  After evaluating the task:
  1. a) An initial script using DBCC CLONEDATABASE was provided (schema-only copy).
  2. b) Upon further request, a second script using BACKUP and RESTORE DATABASEwas written to include data
  1. c) Instructions and a Loom video were also shared for GUI-based options using SQL Server Management Studio.

  1. Tools & Technologies Used:
  2. a) SQL Server Management Studio (SSMS)
  3. b) T-SQL Scripting
  4. c) DBCC CLONEDATABASE
  5. d) BACKUP / RESTORE DATABASE
  6. e) Windows File System for MDF/LDF location control
   
  1. Key Features Implemented:
  2. a) T-SQL script for cloning an existing database structure.
  3. b) Enhanced version with full database data cloning using backup-restore method.
  4. c) Manual guidance for file relocation (.mdf/.ldf) post-restore.
  5. d) Supportive video tutorial for GUI-based method.
 
  1. Challenges Faced & Solutions:
Challenge Solution
Client wanted full data duplication, not just schema Delivered updated backup-restore-based T-SQL script
Requested file redirection to a custom path (G:\data) Explained SQL Server limitations for automatic path change; offered manual step
Client preferred not to share server access Delivered verified code to run independently with instructions
 
  1. Outcome & Benefits:
  2. a) Client successfully cloned the production EMR database.
  3. b) New client database instance was created with expected structure and data.
  4. c) Client became confident in managing future clones using the script or SSMS.
  5. d) Maintained data security and operational independence for the client.
   
  1. Client Testimonial (Summary):
“It works well to clone the db. I can even do it from my software.”