Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
Are the objects always coming from the same name/fileLocation? –  Zane Jan 24 '13 at 15:56
@Zane Yes, we have 2 different .bak files that they will choose from. We are going to be setting up a box with SQL Server 2008 and Server 2012. The 2 backups they will be using will not be changing, they will only be changing the DB's they create with the script. –  Wes Crockett Jan 24 '13 at 16:03
@Zane Also, I found a method online that seems to work well, but I have only tested it with a sample, 3mb database... It restores from a .bak... I am wondering how it will perform with a 1.5gb database... off to testing! :D –  Wes Crockett Jan 24 '13 at 16:03
Script seems solid enough you should be golden. –  Zane Jan 24 '13 at 19:13
add comment

1 Answer

up vote 4 down vote accepted

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.

share|improve this answer
Marian, Thank you... this is what I was expecting. As far as CREATING a new backup, that won't be necessary... I have 2 backups that are going to be our bases. I appreciate your input and I'm going to call this solved. I will post another comment with a link to the script. –  Wes Crockett Jan 24 '13 at 16:16
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.