duplicate sql databases in bulk

This method can be used to create backups and restore databases (with a new name). This is usefull when you need to quickly setup a test version of multiple databases or in case you want to intigrate it as a build step during development. I’ll be using it to setup copies of sitecore databases before upgrading to a new version.

First make sure the logical file names of your databases are the same as the database name. For instance the Sitecore_Analytics database should have logical name Sitecore_Analytics for the mdf file and logical name Sitecore_Analytics_log for the ldf file. This allows the stored procedure to find the files to copy.

Check the logical filenames in a backup:

1
restore filelistonly from disk = 'D:/Data/backup/Sitecore_Analytics.bak'

Stored procedure

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE DuplicateDatabase
@sourceDb nvarchar(50),
@removeChars nvarchar(45),
@postfix nvarchar(5),
@backupPath nvarchar(400),
@sqlServerDbFolder nvarchar(100)
AS
BEGIN
DECLARE @sourceDb_log nvarchar(50);

DECLARE @destDb nvarchar(50);
DECLARE @destMdf nvarchar(100);
DECLARE @destLdf nvarchar(100);

SET @sourceDb_log = Replace(@sourceDb, @removeChars,'') + '_log'
SET @backupPath = @backupPath + @sourceDb + '.bak' --ATTENTION: file must already exist and SQL Server must have access to it

SET @destDb = @sourceDb + @postfix
SET @destMdf = @sqlServerDbFolder + @destDb + '.mdf'
SET @destLdf = @sqlServerDbFolder + @destDb + '_log' + '.ldf'

BACKUP DATABASE @sourceDb TO DISK = @backupPath

RESTORE DATABASE @destDb FROM DISK = @backupPath
WITH REPLACE,
MOVE @sourceDb TO @destMdf,
MOVE @sourceDb_log TO @destLdf
END
GO

Execute stored procedure for each DB

1
2
3
4
5
6
exec DuplicateDatabase 
'Sitecore_Analytics' -- db name
,'_V6.5' -- chars to remove from original db name
,'_V6.6' -- postfix for new db name
,'D:\data\backup\' -- backup location
,'D:\data\' -- new db files location

You can use the second parameter to remove characters from the original database name. This allows you to copy a xxx_V6.6 database as a xxx_V6.7 database. To create the next version all you need to to is replace the postfix and the database names. Since this may take a few minutes per DB, I would recommended to execute this one DB at a time to avoid any timeouts and more easily detect errors.