sql server 2012 express - create identical mutiple databases

Posted on 2012-12-25
Last Modified: 2013-01-29
For testing I would like to create twenty identical databases in the same instance.  db1,db2,db3,etc.
I have the original database and backup to use to create these.
What is the best way to go about this?

Also,  I would like to create unique username and passwords for each database.
Is there a way to automate this also?
Question by:bmsjeff
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 250 total points
ID: 38721459
If you have a backup, you could restore the database 20 times, using different names each time. If you have access to SQL Management Studio, you could generate a script by opening the "Restore Database..." dialog, getting everything configured the way you want it, but then instead of clicking "OK" at the bottom to restore your database, click the "Script..." button in the title bar of the window. This will generate a script to a new query window that will restore your database.

From there, just find the "RESTORE DATABASE" command you want to duplicate, and make copies of it, changing the parameters as needed (database name, file location, etc).

You can do the same thing for your user if you want - use the "New Login" dialog, configure it the way you want, and then script it and modify it as needed to generate 20 different CREATE LOGIN commands and grant them all the desired permissions.
LVL 38

Accepted Solution

Jim P. earned 250 total points
ID: 38722468
Here's some aircode that can probably do what you need.

declare		@I			as	integer
declare		@SQL			as	nvarchar(4000)
declare		@Misc1			as	nvarchar(500)
declare		@Misc2			as	nvarchar(500)
declare		@RestDB			as	nvarchar(50)
declare		@DbNm			as	nvarchar(50)
declare		@MDFile			as	nvarchar(100)
declare		@LDFile			as	nvarchar(100)
declare		@MDFPath		as	nvarchar(100)
declare		@LDFPath		as	nvarchar(100)
declare		@Move1			as	nvarchar(500)
declare		@Move2			as	nvarchar(500)

select	@I =1
select	@RestDB		=	'RESTORE DATABASE ['
select	@Misc1		=	'] from disk = ' + CHAR(39) + 'C:\Filepath\FileName.BAK' + CHAR(39) + ' '
select	@Misc2		=	' with replace, recover, stats = 4, '
select	@Move1		=	'Move DataFileNm TO ' + CHAR(39)    -- The logical datafile and log names
select	@Move2		=	'Move LogFileNm TO ' + CHAR(39)     -- can not be changed during the restore
select	@MDFPath	=	'C:\SQLServerMDFPath\'
select	@LDFPath	=	'C:\SQLServerLDFPath\'

select	@DbNm		=	'BaseDBNm'

while @I <20
	select	@DbNm		=	@DbNm + right('00' + cast(@I as nvarchar(2)), 2) -- pad the DB number with a 0
	select	@MDFile	=	@MDFPath + @DbNm + '.mdf' + CHAR(39)
	select	@LDFile	=	@MDFPath + @DbNm + '.ldf' + CHAR(39)
	select	@SQL = @RestDB + @DbNm + @Misc1 + @Misc2 + @Move1 + @MDFPath + ', ' +  @Move2 + @LDFPath
	exec (@SQL)
	select @I = @I + 1

Open in new window


Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

What to do when Windows Update is not working correctly? What tools can I use to detect the cause of the malfunction problem? What does this numeric error code mean? These and other questions that you have been asking in the past are answered here (…
Resolve DNS query failed errors for Exchange
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now