March 21, 2015 at 9:54 pm
I am creating a program that will take a master database and create separate databases for class room training.
creating my own app to do this since it will have other stuff to do.
i will have a master database that i will need to create multiple copies of.
2-20 copies, it is about 7GB large. it is used in a classroom training course for our company software. it will also copy a folder on the server onto multiple subfolders.
each computer in the classroom will access its own copy of the database/windows folders.
What i am looking for is a fast/reliable way to create the multiple database copies. then when the training class is over and a new one is getting started, we will run my program to reset everything back to start.
Should i detach/copy/attach or create a master backup and restore it 20 times. What kind of user access pitfalls will i need to look out for.
Any info/links will be greatly appreciated.
March 22, 2015 at 2:52 am
roy.tollison (3/21/2015)
I am creating a program that will take a master database and create separate databases for class room training.creating my own app to do this since it will have other stuff to do.
i will have a master database that i will need to create multiple copies of.
2-20 copies, it is about 7GB large. it is used in a classroom training course for our company software. it will also copy a folder on the server onto multiple subfolders.
each computer in the classroom will access its own copy of the database/windows folders.
What i am looking for is a fast/reliable way to create the multiple database copies. then when the training class is over and a new one is getting started, we will run my program to reset everything back to start.
Should i detach/copy/attach or create a master backup and restore it 20 times. What kind of user access pitfalls will i need to look out for.
Any info/links will be greatly appreciated.
Quick suggestion:
1) Use the copy database wizard to create a SSIS package to copy the database on the source server
2) Schedule a job to run the package
3) Add a job-step detaching the copy
4) Add a job-step dropping the databases on the destination servers
5) Add a job-step to copy the files to the destination servers
6) Add a job-step to attach the databases on the destination servers
😎
March 23, 2015 at 12:53 am
Teach the students how to do a restore on the first day.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 23, 2015 at 4:34 am
Depending on your resources you might want to look at using snapshots. Create 20 or so databases using backup/restore and then create a snapshot on each one. If the students are altering a lot of the data then snapshots may not be appropriate but if there's a relatively small number of changes to the database you may find restoring from a snapshot quicker than from a backup. You can also use snapshots during the course so a student put the database back to the way it was at a time of the snapshot if they want to retry something or make a mistake and wipe a table or something. Before of too many snapshots as there is an overhead but maybe worth a look.
March 23, 2015 at 7:20 am
They will not have admin rights/SQL Console. They will only be using our software to learn/experiment.
as for the amount of data changed they will spend anywhere from 2-5 days changing/updating data. Once their training session is over then i will need to reset the data. This is not a mission critical data environment. Just training users how to use software. each PC will be assigned its own login (not by users) so the configurations will remain the same. Just need to be able to create/restore/??? 20 databases. They all need to be based off of our master_database. That way if the instructor makes a change to the our master_database and needs it passed on to all the trainees databases, it will need to be passed on.
1) master_database never changes except when instructor accesses it (got this one created and access in place, it is currently attached and active)
2) when we run my program it will need to create/copy/??? the master_database into 20 databases. if any of those 20 databases exist i will be overwriting them.
3) classroom training begins.
step 2 is my question. if i detach master_database and copy it 20 times and then attach all 21 databases, will that be faster than backup master_database restore the backup 20 times. If i use either of these methods in a threading process will that be issues with file access. I am concerned the detach/copy/attach process will bellyache if i put it in a thread. I do not know if it will like copying the same file to 20 other files at the same time. plus if i attach a database can i attach it as a new database?
I have done the code for backup/restore to new database but only for a single one. i have not added the threading model to it yet.
March 23, 2015 at 10:21 am
Detach can get messy, esp. with the file security changes that occur.
I'd just restore the backup 20 times. You could bundle the entire process into a master job and n sub jobs, where each sub job restored 20 / n dbs.
For example, the main job:
A) backs up the db
B) starts restore jobs 1 thru 4:
msdb..sp_start_job @job_name = 'restore_job1'
msdb..sp_start_job @job_name = 'restore_job2'
...
C) Each of those jobs would restore 5 different dbs.
That way you're not waiting for all 20 restores to occur serially.
Btw, be sure IFI is on so that the data files don't have to be initialized.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply