Search code examples
sql-serverdatabase-restore

What is the fastest method to create a new database from a template?

We are creating databases on demand and the databases can be created from different templates. All templates have the same structure but different data. The data contained by the templates is small.

What is the fastest way to create a copy of the database:

  1. Backup/Restore
    1. Using T-SQL ?
    2. Using SMO ?
  2. Create a new database from a scripted version of the template and then fill in the little data required ?
  3. Other ?
Solution

  • Possibly detach / attach.

    • Preparae master database.
    • Detach
    • Copy to new names, attach with proper syntax to redirect files.
    • Resize to proper size (I hate autoexpand for normal operations).

    This makes everything a file copy, which is IIRC the fastest way. Not that there is a lot of difference, actually.