T-SQL: Clone a Database to be Used in a Development Playground


In the development process and on my playground I need a cloned database to play safely and delete in the end.

Create folders:

  • C:\Temp\
  • C:\Temp\Backups\
  • C:\Temp\Databases\

Initial cleanup.

Check original database files.

Check original database files

During the development and on my playground I use a folder C:\Temp\. Inside I have (for this exercise) two folders, where I store the backups and the databases. On the production machine these folders may vary (storing the physical filed on different disk drives), but for playing C:\Temp\ is OK.

Backup the database, that needs to be cloned.

Backup

An error may occur:
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘C:\Temp\Backups\PL_OriginalDB.bak’. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

This means that the user, running the SQL Server Service needs permissions to read/write in the destination folder (C:\Temp\Backups\) .

1. Start –> Run –> services.msc
2. Name: SQL Server (SQLEXPRESS) –> (right click) Properties –> (tab) Log On –> This Account
3. (right click) C:\Temp\Backups\ (Databases) –> Properties –> (tab) Security –> Add the user… Read and Write permissions.

Restore the backup into a cloned database and move the files to the databases location.

Restore

Check cloned database files.

Check cloned database files 1

Rename.

Rename

Check cloned database files again.

Check cloned database files 2

Run the initial clean up at the bottom of the article and cleanup/delete C:\Temp\ –> Backups and Databases.

A stored procedure in master database of the development server may be created with the database names and paths as parameters.

Keep it simple :-)

Leave a comment

Your email address will not be published. Required fields are marked *