Solved

sql server 2012 express - create identical mutiple databases

Posted on 2012-12-25
2
390 Views
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?
0
Comment
Question by:bmsjeff
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
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.
0
 
LVL 38

Accepted Solution

by:
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
begin
	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
end

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The following article is comprised of the pearls we have garnered deploying virtualization solutions since Virtual Server 2005 and subsequent 2008 RTM+ Hyper-V in standalone and clustered environments.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

734 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