SP for Restoring Database and performing other tasks on it

Question

I am creating a stored procedure for our QA dept that will do the following: Accept specific inputs to generate a consistent name, restore a database (giving it the generated name,) set the db up for replication (three merge publications), and then initialize the snapshots of these three publications.

Don't worry about the replication portion of this, I have it handled so far. What I am wondering is:

What will be the most effective/eficient method of restoring the database? Will it be restoring from a backup (the 2 DB's that they will be restoring from are 1.5gb and 599mb) or would it be another way? If it is not from the .bak files, what other way of restoring do you think would be appropriate?

:: This needs to happen, strictly, with TSQL ::

Thank you, Wes

---

Here is the script I used

Answer 1

The only way to really backup a database is to create a backup of the database (using BACKUP DATABASE). All the other methods (scripting database schema and content, copying files around, OS file snapshots) are workarounds, not database backups.

So, as I see it, the best way to have a database copied from one place to another is to restore it from a backup (RESTORE DATABASE). There's no other effective and more efficient way as I see it. That's my 2 cents, anyway.

Now, in order to do all that preparation, I'd make different stored procedures (using dynamic sql to build proper names, paths..etc) that will:

• restore databases from a specific location;
• prepare specific permissions -> maybe different users..etc;
• prepare replication objects;

PS: if it's a possibility, make sure to enable backup compression, as it will really help with size and speed.

PS2: taking into considerations your backup file sizes I wouldn't worry too much about the duration of the operation. Should be fast even on a usual developer box.