Solved

SQL Express - Define my database as MODEL and create new databases from it

Posted on 2009-05-13
6
494 Views
Last Modified: 2013-12-25
Hello everybody again,

We have a framwork from which we devise websites for customers and the framework runs on top of a SQL Server Express database.

I'd like to know what would be the quickest way to define my database as a Model database and create duplicates of this database to deploy other websites.

As of now I run a .sql file which populates an empty db and inserts the needed data to it.
Recently we made some upgrades to our framework and the ammount of data to be inserted on the db grew quite a lot, thats why I think theres gotta be an easier and faster way of doing this, considering that I have administrator's access to the database server instance.

Thanks

Eder
0
Comment
Question by:Ederwainer
  • 4
  • 2
6 Comments
 
LVL 5

Accepted Solution

by:
Aanvik earned 500 total points
ID: 24378769
You can do backup/restore which could be really fast, only if you are not interested in running sql script.
0
 

Author Comment

by:Ederwainer
ID: 24379193
I was successful at creating a .bak file from our model database.
However, when I create a new database it asks me who is the owner, and for each database we create we also need to create a unique owner (user).

How do I create the user prior to creating the database, so I can point to him as the owner when I create the new database?

Thanks!

Eder
0
 
LVL 5

Expert Comment

by:Aanvik
ID: 24379224
Its simple. Create a user with a SQL statement in the Target database and while restore you should be able to use that user.
You should be able to restore with SQL statement as well... as long as .bak file is in predefined location.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:Ederwainer
ID: 24379270
Ok,

I found where I can create a new user. Ill go trhough the process and see how it goes.

Thank you so far!

Eder
0
 

Author Comment

by:Ederwainer
ID: 24385867
Im having some trouble with it and I could use some further guidance.

I made a .bak file from our oficial database and stored it on the server.
Then I create a new user, create a new database  and go to Tasks -> Restore, choose device as the source and point to the .bak file I created.

Then I come across an error that implies that the .bak file can be restored only the the database from which it was originated. Is that so?

If that is so, this might not be the best method, as what I need to do is to created databases that are duplicate of our official database, however with difeerent names, to be owned by different logins and be used in different websites.

Thanks

Eder
0
 

Author Comment

by:Ederwainer
ID: 24391525
I got over the problem described above. However I came across a new difficulty,

When I generate the .bak file, the default schema is exported along with it. However for security purpose I dont want to use the same schema for all databases and I can accomplish that by populating the db from a .sql query, as it will apply the schema based on the name of the user that owns the newly created database.

How can I have a unique schema for each database if when I restore from .bak it will apply the existing schema instead of using the schema of the database being restored to?

Thanks again.

Eder Wainer
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
2 Gateways (bandwidth) - One domain 7 74
ISDATE() not working properly on my table? Any suggestions. 7 28
Cisco 3560 switches not seeing VTP V3 12 52
QoS for Voip 7 39
When you try to share a printer , you may receive one of the following error messages. Error message when you use the Add Printer Wizard to share a printer: Windows could not share your printer. Operation could not be completed (Error 0x000006…
For many of us, the  holiday season kindles the natural urge to give back to our friends, family members and communities. While it's easy for friends to notice the impact of such deeds, understanding the contributions of businesses and enterprises i…
Viewers will learn how to connect to a wireless network using the network security key. They will also learn how to access the IP address and DNS server for connections that must be done manually. After setting up a router, find the network security…
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question