How to script a database? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

How to script a database?

Author

Topic

Tritim
Starting Member

17 Posts

Posted - 2008-05-12 : 12:08:06

Hi all,

I'm using SQL Server 2005 Management Studio. I want to copy exactly the structure of an existing database.

Using Managment Studio, I see that right-clicking on the database, there are two ways:
1. Script Database AS
2. Tasks / Generate Scripts.

Looking at the resulting scripts, they are different.

My question is this:
Does anybody have some recommendations or advice from actual experience scripting a database?

Thanks in advance.

Tritim

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-12 : 12:09:58

when you choose second options , you can script everything in database.

Tritim
Starting Member

17 Posts

Posted - 2008-05-12 : 12:20:59

Thanks sodeep.

What is the purpose of the first option, Script Database AS, then?

In the second option, Generate Scripts, why are several of the settings set to Off by default? Do you usually accept the defaults when you use Generate Scripts to copy a database?

Thanks.

Tritim

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-12 : 12:27:54

First option will only script only database and its default properties.

Second option: Depends on what you want.

Tritim
Starting Member

17 Posts

Posted - 2008-05-12 : 12:47:57

Thanks sodeep.

I'm looking for some practical insight and advice about scripting (copying) a database with Management Studio.

Anybody?

Thanks in advance.

Tritim

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-12 : 13:14:22

Did you try by urself?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-12 : 13:25:49

If you just want to copy the database, then why don't you use BACKUP/RESTORE? Could you explain in better detail what you want? Is it just the structure or do you also want data? How about users, permissions, ...?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Tritim
Starting Member

17 Posts

Posted - 2008-05-12 : 13:29:39

sodeep,

The database has about 30 tables, many stored procedures, triggers, indexes, permissions, etc.

If I script it as experiment, it will be difficult to ascertain what got copied vs what didn't without spending much labor and time inspecting the results, to learn lessons.

I come here to SQLTeam to find people who already have experience doing things like scripting, hoping to find someone who can give practical advice, thereby saving me having to learn by mistakes the hard way.

Likewise, if I happen to have experience in some other topic, I hope to give my advice if someone asks, and save them some work.

Still looking for practical advice on scripting a database.

Anybody?

Tritim

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-12 : 13:34:04

quote:
Originally posted by Tritim

Anybody?

Umm, yes. Did you read my post?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Tritim
Starting Member

17 Posts

Posted - 2008-05-12 : 13:35:20

Hi Tara,

My real need is to copy a production database down to my development machine, but unfortunately I need SysAdmin rights on the production server, which they are unwilling to give, understandably, because there are other important databases running there.

So, this thread is about scripting a database structure cleanly, as an alternative. I'm wondering if anybody out there at SQLTeam scripts (copies) databases often enough to have any insights, practical lessons on scripting.

Any thoughts?

Thanks very much.

Tritim

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-12 : 13:38:05

I have plenty of experience with this, but you need to tell us if you want the data as well.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Tritim
Starting Member

17 Posts

Posted - 2008-05-12 : 13:46:23

Tara,

Yes, I want the data as well. I was planning on a 3-step process using SQL Server 2005 Management Studio:

1. Generat a script of the database on the production server.
2. Execute the script on my development machine.
3. Use Import/Export to import the data.

But I'm wanting whatever is the best way.

Any thoughts? Thanks.

Tritim

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-12 : 13:51:23

You should be using BACKUP/RESTORE since you want the data as well. You do not need sysadmin permissions to perform these.

Do you have a production DBA that can help you set this up? It is a fairly common task for a production DBA to automate copying a production database to a dev/test environment.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-05-12 : 13:55:09

Tara,

Here is his real problem
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102579

Tritim
Starting Member

17 Posts

Posted - 2008-05-12 : 14:02:57

Tara,

I guess I'll do that then. Get the production DBA to make a BACKUP package for me.

I was pursuing this, scripting the structure, as an alternative. And a nice skill to have.

I'm guessing now, by the responses, that scripting a database is not a simple idea, that it has a few wrinkles to consider.

But I think I'll follow your advice, and hope to get the production DBA on board.

Thanks.

Tritim

Tritim
Starting Member

17 Posts

Posted - 2008-06-19 : 20:39:00

Here's a good solution, posted by Scott Guthrie of Microsoft, using the Database Publishing Wizard:

http://weblogs.asp.net/scottgu/archive/2006/12/22/recipe-deploying-a-sql-database-to-a-remote-hosting-environment-part-1.aspx

Tritim

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-06-19 : 21:38:47

there's really no reason to use the publishing wizard if you can do backup/restore.

ignore Tara at your peril.

elsasoft.org

mdubey
Posting Yak Master

133 Posts

Posted - 2008-06-20 : 10:15:14

Why Cann't you useImport/Expor if you do not need to restore the backup file.

Manoj
MCP, MCTS

Tritim
Starting Member

17 Posts

Posted - 2008-06-20 : 10:58:08

There seems to be a lot of interest in the Database Publishing Wizard.

I think that's because it addresses a situation like mine, where I'm responsible for developing an application that uses a database residing on a production SQL Server machine where I have no sa rights.

(I think many of the members here at SQL Team are use to being DBA, where they have wide rights to do as they need.)

For example, without sa type rights, Manoj and Tara, I can't do import/export, nor backup/restore from the production machine down to my local machine.

But with the Database Publishing Wizard, I can accomplish the same end result pretty much.

That's why people are so interested in it I think.

Tritim

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-06-20 : 11:40:53

you could also just generate table structure and the insert statements for all tables and run that on whatever server you want.

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-20 : 12:40:47

quote:
Originally posted by Tritim

For example, without sa type rights, Manoj and Tara, I can't do import/export, nor backup/restore from the production machine down to my local machine.

Import/export does not require sa. Neither does backup/restore, although it does require elevated permissions.

So what permissions do you have on the server?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources