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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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 |
Check original database files.
|
1 2 3 |
SELECT [name], [physical_name], [type_desc] FROM [AdventureWorks2008R2].sys.database_files; GO |
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.
|
1 2 3 4 |
BACKUP DATABASE [AdventureWorks2008R2] TO DISK = N'C:\Temp\Backups\PL_AdventureWorks2008R2_Data.bak' WITH INIT; GO |
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.
|
1 2 3 4 5 6 |
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 |
Check cloned database files.
|
1 2 3 |
SELECT [name], [physical_name], [type_desc] FROM [PL_AdventureWorks2008R2].sys.database_files; GO |
Rename.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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 |
Check cloned database files again.
|
1 2 3 |
SELECT [name], [physical_name], [type_desc] FROM [PL_AdventureWorks2008R2].sys.database_files; GO |
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 :-)