Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql server 2012 express - create identical mutiple databases

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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windows Server 2003 introduced persistent Volume Shadow Copies and made 2003 a must-do upgrade.  Since then, it's been a must-implement feature for all servers doing any kind of file sharing.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

824 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