SQLServerCentral / Discuss Content Posted by Jon Reade / Article Discussions / Article Discussions by Author / How to Schedule a SQL Server Database Creation Script / Latest Posts

RE: How to Schedule a SQL Server Database Creation Script

jsheck59 — Wed, 29 Aug 2012 12:39:34 GMT

Great article. Does this same option exist in SQL 2008 R2? It is not located in the same area as mentioned in the article and I would like to do something like this in SQL 2008? Or is there a better way in 2008 for automatically generating database creation scripts rather than EM?Thanks,John

RE: How to Schedule a SQL Server Database Creation Script

lewcrippen12 — Mon, 12 Jul 2010 05:48:36 GMT

Hello,Hey....It's really interesting article & I found something new procedure for working.ThanksRegardsLew[url=http://www.innovativeoutsource.com/database_cre.html] Database Creations[/url]

RE: How to Schedule a SQL Server Database Creation Script

lewcrippen12 — Mon, 12 Jul 2010 05:46:51 GMT

Hello,Hey....It's really interesting article & I found something new procedure for working.ThanksRegardsLew[url=http://www.innovativeoutsource.com/database_cre.html] Database Creations[/url]

RE: How to Schedule a SQL Server Database Creation Script

bijayanix24 — Thu, 25 Mar 2010 06:55:45 GMT

SQL Server Management Objects (SMO) are objects designed for programmatic management of Microsoft SQL Server. We can integrate SMO into any .NET based applications.SMO is also compatible with SQL Server version 7.0, SQL Server 2000, and SQL Server 2005, which makes it easy to manage a multi-version [url=http://www.mindfiresolutions.com/Run-the-existing-SQL-Script-programmatically-with-SMO-526.php]environment[/url].Following code is used to run the Microsoft SQL Server Query files(script files) kept inapplications bin\Debug folder, with SMO.

RE: How to Schedule a SQL Server Database Creation Script

BobMcC — Fri, 24 Jul 2009 13:05:44 GMT

I'm sorry to resurrect this guy from the dead, but I'm curious as to what the conventional wisdom is for the SCPTXFR tool for sql 2008?I know I can right click in SSMS and generate scripts for the entire database. What if I want to regularly create and back up those scripts?

RE: How to Schedule a SQL Server Database Creation Script

nirazeminem-763096 — Thu, 29 May 2008 04:09:22 GMT

Hi guys,i need to create a database.. i don't how to script it create the database dynamically..if anyone could help it will be greatly appreciated..by the way i am using sql server 2005...i have already tried the following script..it doesn't work though....USE masterGOCREATE DATABASE StudentON( NAME = Student_dat, FILENAME = 'c:\program files\microsoft sql server\mssql\data\Studentdat.mdf', SIZE = 20MB, MAXSIZE = 70MB, FILEGROWTH = 5MB )LOG ON( NAME = 'Student_log', FILENAME = 'c:\program files\microsoft sql server\mssql\data\Student.ldf', SIZE = 10MB, MAXSIZE = 40MB, FILEGROWTH = 5MB )GOcheers.....

RE: How to Schedule a SQL Server Database Creation Script

ourdai — Tue, 30 Oct 2007 06:37:33 GMT

Is it me ?I get all sorts of spurious errors if the command string is not all on one line.

RE: How to Schedule a SQL Server Database Creation Script

mark blakey-271739 — Fri, 17 Nov 2006 03:31:00 GMT

I'm trying to use the sql 2000 version of scptxfr on sql 2005 however the script seems to keep running for days in databases where there are a lot of dependencies. Has anyone else experienced this or knows of a workaround?

RE: How to Schedule a SQL Server Database Creation Script

Rod Weir — Wed, 01 Nov 2006 20:52:00 GMT

ApexSQL Script is the best for doing this type of this. Correct dependency order, scripting, custom tags + much more. Great product. Great support.http://www.apexsql.com/sql_tools_script.aspRod

RE: How to Schedule a SQL Server Database Creation Script

Charles Kincaid — Fri, 13 Oct 2006 09:44:00 GMT

You said "although it appears to be missing in SQL Server 2005 beta." While that might be true it seems to be missing from the release version as well. Could this be because it is not compatible with some of the new objects?

RE: How to Schedule a SQL Server Database Creation Script

Simon Shutter — Fri, 13 Oct 2006 09:20:00 GMT

Any idea why Enterprise Manager generates extra blank lines when generating scripts? After several cycles of scripting and rebuilding a db, there is a lot of white space in the script.

RE: How to Schedule a SQL Server Database Creation Script

J Schafer Wilson — Fri, 13 Oct 2006 06:21:00 GMT

Thank you for the article; very helpful.

Also a minor discrepancy: In the introduction to Real World Examples it states "NB: All examples use a database server called DEVSVR." but some examples use svdb3

RE: How to Schedule a SQL Server Database Creation Script

Jonr — Fri, 13 Oct 2006 01:42:00 GMT

I don't unfortunately Chris. However, from memory, I think it's replacing the name of the primary key with its objectid. Quite why MS did this I don't know.

Jon.

RE: How to Schedule a SQL Server Database Creation Script

ChrisMoix-87856 — Tue, 19 Apr 2005 15:17:00 GMT

One thing I have noticed with SCPTXFR is that it changes the name of most of my Primary Keys. Example:

Source database PK Index: PK_tblBox_BoxID

Dest database PK index: PK__tblBox__1A69E950

I'd like to retain the descriptive names. Does anyone know a way to accomplish this?

Chris

RE: How to Schedule a SQL Server Database Creation Script

Malcolm Leach — Mon, 18 Apr 2005 06:38:00 GMT

I guess the issue here is 'you get what you pay for' - this is a great article and provides an excellent (and free!) way to version your databases. You could even do a little more scripting and push the scripts into a SourceSafe project incrementally every night. i.e check everything out and then overlay the scripts on disk with the newly scripted DB. Then check it all back in, add and new scripts and delete any that are now missing and you have an audit trail of what changed on your development/test/production database that day.

However, this won't tell you who changed things and, crucially, why.

For a full change management process for SQL Server code I recommend you take a look at DB Ghost (www.dbghost.com). It can compare databases, build databases from drop/create scripts under source control (with no dependency issues) and can script out entire databases (including permissions!). All this can be run from the command line as well as the UI to acheive a fully automated process.

Having the developers modify the drop/create scripts in source control means that the DB Ghost Process is perfectly aligned with configuration management best principles (who changed what, why and when) and is therefore also a huge step towards Sarbanes Oxley / HIPAA / BASEL II compliance etc.

Once you've used it you'll wonder why no one thought of it before.

- Malcolm

RE: How to Schedule a SQL Server Database Creation Script

iadams — Thu, 14 Apr 2005 13:28:00 GMT

I was thinking in terms of using DTS to transfer and manage all the files created by the scripting process.

RE: How to Schedule a SQL Server Database Creation Script

jeffrey yao — Thu, 14 Apr 2005 11:44:00 GMT

DTS is not a good answer in terms of convenience and team cooperation. Yes, DTS has a task call "Copy SQL Server Objects" that can generate most of db objects' scripts, but I do not like it at all, I will answer it in my article in future.

RE: How to Schedule a SQL Server Database Creation Script

iadams — Thu, 14 Apr 2005 11:17:00 GMT

I guess you can use DTS to create a central repository for all these scripts. Great article, Thanks!

RE: How to Schedule a SQL Server Database Creation Script

jeffrey yao — Thu, 14 Apr 2005 09:53:00 GMT

Intersting article, and actually, I just finished one similar tool with a set of different features.

I have been facing migrating database objects (several hundered sometimes)from tens of databases in different environments to some other environments.

I will probably send my article to Steve sometime next weekend to share with all others.

RE: How to Schedule a SQL Server Database Creation Script

Jonr — Thu, 14 Apr 2005 08:33:00 GMT

Thanks for the comments guys, useful feedback like this is always much appreciated.

Unfortunately, yes, scripting of the table & stored proc security permissions does seem to be a limitation of this method, and was something I spotted only after submitting this article. It did make me wonder why it had not been included by MS - I'd have expected to see them applied after each object definition, or perhaps in a separate file.Other than manually scripting them I'm afraid I can't offer a reasonable way around this at present.

With regard to the object order, probably the best way to deal with that is to do it with a number of passes, and not drop the objects which have already been created between each run of the script.It's messy as you'll get lots of 'object already exists' errors, but at least it does eventually solve the creation order dependency problem.How MS get around this in the upgrade scripts is something of a mystery, but it might explain why they take so long to run.

RE: How to Schedule a SQL Server Database Creation Script

ashishnaik1 — Thu, 14 Apr 2005 06:33:00 GMT

Good Article

RE: How to Schedule a SQL Server Database Creation Script

Robin Coode — Thu, 14 Apr 2005 05:42:00 GMT

This only works for simple databases as it does not take account of dependencies between objects and can create objects in the wrong order.

RE: How to Schedule a SQL Server Database Creation Script

Brian Knight — Thu, 14 Apr 2005 05:04:00 GMT

Wow, learn something new everyday. Great article.

RE: How to Schedule a SQL Server Database Creation Script

b_avery@yahoo.com — Thu, 14 Apr 2005 04:02:00 GMT

Take back my comments, as View have permissions set but Tables and Stored Procedures don't

RE: How to Schedule a SQL Server Database Creation Script

b_avery@yahoo.com — Thu, 14 Apr 2005 04:00:00 GMT

Great article, makes the creation on scripts much easier.

The only thing that is missing from the process is the permissions for each object they are not created, and as a result I have to create these manually.

How to Schedule a SQL Server Database Creation Script

Jonr — Fri, 08 Apr 2005 13:19:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jreade/howtoscheduleasqlserverdatabasecreationscript.asp