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.

USE [master];
GO

IF (DB_ID(N'PL_AdventureWorks2008R2') IS NOT NULL)
	BEGIN
		ALTER DATABASE [PL_AdventureWorks2008R2] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE [PL_AdventureWorks2008R2];
	END
GO

SELECT [name]
FROM sys.databases
WHERE [name] = N'PL_AdventureWorks2008R2'
GO

USE [master];

IF (DB_ID(N'PL_AdventureWorks2008R2') IS NOT NULL)

BEGIN

ALTER DATABASE [PL_AdventureWorks2008R2] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE [PL_AdventureWorks2008R2];

END

SELECT [name]

FROM sys.databases

WHERE [name] = N'PL_AdventureWorks2008R2'

Check original database files.

SELECT [name], [physical_name], [type_desc]
FROM [AdventureWorks2008R2].sys.database_files;
GO

SELECT [name], [physical_name], [type_desc]

FROM [AdventureWorks2008R2].sys.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 DATABASE [AdventureWorks2008R2]
	TO DISK = N'C:\Temp\Backups\PL_AdventureWorks2008R2_Data.bak'
	WITH INIT;
GO

BACKUP DATABASE [AdventureWorks2008R2]

TO DISK = N'C:\Temp\Backups\PL_AdventureWorks2008R2_Data.bak'

WITH INIT;

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 DATABASE [PL_AdventureWorks2008R2]
FROM DISK = N'C:\Temp\Backups\PL_AdventureWorks2008R2_Data.bak'
	WITH RECOVERY
		, MOVE 'AdventureWorks2008R2_Data' TO 'C:\Temp\Databases\PL_AdventureWorks2008R2_Data.mdf'
		, MOVE 'AdventureWorks2008R2_Log' TO 'C:\Temp\Databases\PL_AdventureWorks2008R2_Log.ldf'
GO

RESTORE DATABASE [PL_AdventureWorks2008R2]

FROM DISK = N'C:\Temp\Backups\PL_AdventureWorks2008R2_Data.bak'

WITH RECOVERY

, MOVE 'AdventureWorks2008R2_Data' TO 'C:\Temp\Databases\PL_AdventureWorks2008R2_Data.mdf'

, MOVE 'AdventureWorks2008R2_Log' TO 'C:\Temp\Databases\PL_AdventureWorks2008R2_Log.ldf'

Check cloned database files.

SELECT [name], [physical_name], [type_desc]
FROM [PL_AdventureWorks2008R2].sys.database_files;
GO

SELECT [name], [physical_name], [type_desc]

FROM [PL_AdventureWorks2008R2].sys.database_files;

Rename.

ALTER DATABASE [PL_AdventureWorks2008R2]
MODIFY FILE
 (
	 NAME = N'AdventureWorks2008R2_Data'
	 , NEWNAME = N'PL_AdventureWorks2008R2_Data'
 );
GO
 
ALTER DATABASE [PL_AdventureWorks2008R2]
MODIFY FILE
 (
	 NAME = N'AdventureWorks2008R2_Log'
	 , NEWNAME = N'PL_AdventureWorks2008R2_Log'
 );
GO

ALTER DATABASE [PL_AdventureWorks2008R2]

MODIFY FILE

(

NAME = N'AdventureWorks2008R2_Data'

, NEWNAME = N'PL_AdventureWorks2008R2_Data'

);

ALTER DATABASE [PL_AdventureWorks2008R2]

MODIFY FILE

(

NAME = N'AdventureWorks2008R2_Log'

, NEWNAME = N'PL_AdventureWorks2008R2_Log'

);

Check cloned database files again.

SELECT [name], [physical_name], [type_desc]
FROM [PL_AdventureWorks2008R2].sys.database_files;
GO

SELECT [name], [physical_name], [type_desc]

FROM [PL_AdventureWorks2008R2].sys.database_files;

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 :-)