| Author |
Topic |
|
adhik
Yak Posting Veteran
66 Posts |
 Posted - 2004-01-12 : 02:01:25
|
| Hi,I have to automate creating database process. This required from my web based application where each autorized/verified cutomer has own database for using my application to seprate out the data.I have main(master) database from which I have to created diff. database where name will be passed as parameter.I am able to create database using sql script, but how can transfer data(total 20 tables in which total 300 records).Can anyone suggest good way of doing it?waiting for reply.....ThanksAdhik |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-01-12 : 07:32:59
|
| There are 2 ways that I can think of:1. Create all of the common tables in the model database. When you create a new database it uses model as the, well, model.2. Create all the common tables in a database, then make a full database backup of it. Restore this database with a new name for each new database. |
 |
|
|
adhik
Yak Posting Veteran
66 Posts |
Posted - 2004-01-13 : 00:21:32
|
| Thanks robvolk,I am sorry for cross post. I found 2nd option better, I tried it with RESTORE COMMAND.But here... for each new db instance i create from standard backup, physical file name for datafile and logfile is different but logical name are same for all. Will it work properly or I do have to change logical names of files (if yes how? coz i didn't find any option with RESTORE COMMAND). I didn't get RESTORE FilesOnly COMMAND.ThanksAdhik |
|
|
|
adhik
Yak Posting Veteran
66 Posts |
Posted - 2004-01-13 : 00:22:50
|
| Thanks robvolk,I am sorry for cross post. I found 2nd option better, I tried it with RESTORE COMMAND.But here... for each new db instance i create from standard backup, physical file name for datafile and logfile is different but logical name are same for all. Will it work properly or I do have to change logical names of files (if yes how? coz i didn't find any option with RESTORE COMMAND). I didn't get RESTORE FilesOnly COMMAND.ThanksAdhik |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-01-13 : 07:39:56
|
| The logical file names will stay the same, the physical files would be changed in the MOVE clause of the RESTORE command. Books Online has an example of RESTORE...WITH MOVE. |
|
|
|
adhik
Yak Posting Veteran
66 Posts |
Posted - 2004-01-17 : 06:45:21
|
| Thanks robvolkBut now here i m getting new problem.Basically, I have to do 4 steps in this transaction(created one procedure).1. Insert customer record in application_master (main) Database of Application.2. Create new database using backup file with DB name as Customer+@@IDENTITY (customerid of from previous insert)3. Insert office address record, in newly created database4. Insert user (Admin/Super User of current created cutomer) record, in newly created database.Here I am not able to create new database using restore command in procedure.Error : Cannot perform a backup or restore operation within a transaction.(Error No. - 3021)then I created seprate procedure for only creating database, still getting same error.Expect your guidence, how should I achive this.RegardsAdhik |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-01-17 : 17:33:32
|
| You're reaching a level where separating the customers into separate databases is probably not worth it, especially considering the database naming convention you're using. Unless security and separation of data are absolutely paramount, I'd suggest consolidating the databases into one. It is a major inconvenience to have multiple databases storing the same data (I'm dealing with such a system at my job now, and it makes me cry from time to time)As far as restoring within a transaction, you can't, so don't. There's no need for a transaction anyway. And you don't need step 4 either, a "super user" or admin account should always have the same name so that the databases are consitent in operation. |
|
|
|
adhik
Yak Posting Veteran
66 Posts |
Posted - 2004-01-18 : 04:23:50
|
| Hi robvolk,I didn't get you exactly what you want to say.I would like to konw what kind of problems in this structure may come, and possibly any other solution if you have.Bascially my application is simple product related to Sales people of organiztion, we have designed it so that it has two different interfaces one desktop interface in VB (Which can be used as intranet for organiztion where DB server is kept in some datacenter) and such multiple desktop interfaces can access this common DB.And 2nd Interface is web based solution, where in small companies who doesn't have capacity to buy own license, we have to create only seprate db on our server for him and our master/main db is used to only to authorize each user and connecte him to resp. db.When we have to create his own instance inside application we only create one user which is super user of that customer, rest users he create himself and through trigger each users login password is brought into to the master db of application.So we dont have any duplication of data except users data and his login password.Now I think u will get much better pic of my application, and could think from my point.With warm regardsAdhik |
|
|
|
adhik
Yak Posting Veteran
66 Posts |
Posted - 2004-01-18 : 09:56:47
|
| Hi,Today I finished with current logic and integrated in application.above created procedure when run in query analyser, works fine. But when I call that procedure through my application (ASP front-end) It give me following reason.ERROR : CREATE DATABASE permission denied in database 'master'.What could be the reason.Need help urgently....Thanks in advanceRegardsAdhik |
|
|
|
|
Importing Data