Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql server 2012 express - create identical mutiple databases

Posted on 2012-12-25
2
Medium Priority
?
393 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 1000 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 1000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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 shrink a transaction log file down to a reasonable size.

688 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