Restoring a generic database for each new user

Question

Im building a system using SQL Server 2012, it's a web app containing same data type but its specifical for each user, so we are using one database per user, every single database has the same structure and it's created dinamically and live when the user clicks on the confirm account email.

The problem is the script that creates the databases is restoring a database called DB_GENERIC.bak and its taking more time than i would expect. Im looking for ways to optimize this, and end up with a brand new database created but reducing the time it takes. (right now is using like 15 seconds to create the database), and the Generic database its restoring its small (aprox 5mb, and 600kb compressed) and all the tables are empty.

Is there any other way of accomplishing this instead of restoring a full database backup? can i script the whole generic database on t-sql and execute that changing the database name? is it going to be any faster? Or is it any other way of compressing or generating a less resource-consumer backup, the thing is that i dont need data or anything, just tables, procedures, functions, and schemas.

Thanks in advance

---

UDPATE:

Thanks to everyone that replied to my question, the answers were all great and helpful. I ended up using @Love2Learn answer and i tried that detach-copy-attach method.

Just for you to imagine how this was implemented, i had an store procedure that's the one in charge of copying-attaching the new database from a database template that has the following:

--executing bat that copies the database-1-template.mdf and database-2-template.mdf (as well as log files) to the DATA folder in my sql instance
--and renames them (then they'll be attached)
DECLARE @BATCopy VARCHAR(MAX) = 'C:\helpers\copyMDFAndLogFiles.bat'

--building command for xp_cmdshell with parameters (new databases names)
DECLARE @command varchar(500) = @BATCopy + ' ' + @database1Name + ' ' + @database2Name

--PRINT @command

EXEC xp_cmdshell @command, no_output

--attaching new databases
SET @SQL = ' USE [master] 
CREATE DATABASE ' + @database1Name + '
    ON (FILENAME = ''' + @SQLDATAPath + @database1Name + '.mdf''), 
    (FILENAME = ''' + @SQLDATAPath + @database1Name + '_log.ldf'') 
    FOR ATTACH; 

CREATE DATABASE ' + @database2Name+ '
    ON (FILENAME = ''' + @SQLDATAPath + @database2Name+ '.mdf''), 
    (FILENAME = ''' + @SQLDATAPath+ @database2Name+ '_log.ldf'') 
    FOR ATTACH;'

EXECUTE(@SQL)

--renaming logical files

SET @SQL = '
ALTER DATABASE '+@database1Name +'
MODIFY FILE (NAME = Database1_Generic, NEWNAME = '+@database1Name +')

ALTER DATABASE '+@database1Name +'
MODIFY FILE (NAME = Database1_Generic_Log, NEWNAME = '+@database1Name +'_log)

ALTER DATABASE '+@database2Name+'
MODIFY FILE (NAME = Database2_Generic, NEWNAME = '+@database2Name+')

ALTER DATABASE '+@database2Name+'
MODIFY FILE (NAME = Database2_Generic_Log, NEWNAME = '+@database2Name+'_log)
'

EXECUTE (@SQL)

I hope this helps anyone that needs to do the same thing i needed to do, and thanks you all for your answers.

Julian.

Answer 1

Look at this answer... Best way to copy a database (SQL Server 2008)

The best way is detach-copy-attach. You would want to create a template database and use this as your base for each new database.

Answer 2

...the thing is that i dont need data or anything, just tables, procedures, functions, and schemas.

Step 1 is to split your schema from your data. Use SSMS "Script Database As..." to generate an SQL Script that contains only schema/data you want a new database to have. Make a plan for future schema mods ("basedatabase.sql", "patch01.sql", etc.) because it's going to happen and you're going to have to apply those mods to a lot of databases.

Once you've gotten rid of the data, creating the database from a script will be a lot faster. If that isn't fast enough, back up the empty DB and restore to a new database from that. (SQL Server Azure has a nice CREATE DATABASE_B AS COPY OF DATABASE_A but that is not in regular SQL Server.)

BTW I agree with other posters re: wisdom of multiple databases; if you expect to have 50 customers, this is fine. If you expect to have 10,000 customers you'd be better served by a different approach.

Answer 3

My belief is that you'll be best of by scripting the database and executing the script. This has the advantage that you can easily version the content. Also, for upgrading all tenant databases to a new version you need to script out the delta anyway.

You can create the initial script using the script database wizard. Be sure to change the advanced options because they have crazy defaults. They will just leave out some things like triggers and others.