Solved

sql server 2012 express - create identical mutiple databases

Posted on 2012-12-25
2
384 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 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
WINDOWS 2012 RDS -disappearing printers 2 22
Import a txt file into 2012 DNS server 2 24
Powershell 3 19
Test a query 23 13
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

707 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

12 Experts available now in Live!

Get 1:1 Help Now