[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

how to generate database scripts through command prompt in sql server 2005

Posted on 2009-02-13
28
Medium Priority
?
882 Views
Last Modified: 2012-05-06
Hi,

Can we generate the database scripts (Not all at a time, only few stored procedures/UDFs/Views) using command prompt in sql server 2005 ?
Or any work around for performing the above task?
0
Comment
Question by:arvapal
  • 10
  • 4
  • 3
  • +5
25 Comments
 
LVL 25

Expert Comment

by:reb73
ID: 23635711
You can use SMO (SQL Server Management Object ) library to create scripts, a sample with code which can be adapted is available in the following link -

http://forloveofsoftware.blogspot.com/2008/11/scripting-sql-server-database-objects.html
0
 
LVL 25

Expert Comment

by:reb73
ID: 23635751
The easiest way is to use the Database Properties (right click on database in SMS) and Task -> Generate SQL Scripts which will give you a lot of options to customise the objects required and script options..
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23640192
Here is an excelent example doing exactly what you want. It says it is for Express, but will work on any edition. Also includes a VBS script to clean up "old" versions.

http://www.mssqltips.com/tip.asp?tip=1486
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
LVL 51

Expert Comment

by:Mark Wills
ID: 23640211
Sorry about that, thought I saw a comment about script doing backup...
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23640280
So, yes, all in T-SQL cerate a script file and use osql or sqlcmd to execute the script file :



DECLARE @cmd varchar(255)
DECLARE @object int
DECLARE @result int
 
SET NOCOUNT ON
EXEC @result = sp_OACreate 'SQLDMO.SQLServer', @object OUT
 
-- EXEC @result = sp_OASetProperty @object, 'LoginSecure', TRUE    -- used for integrated windows security
 
EXEC @result = sp_OASetProperty @object, 'Login', 'sa'
EXEC @result = sp_OASetProperty @object, 'password', 'sapassword'
 
SET @cmd = 'Connect(myservername)'
EXEC @result = sp_OAMethod @object,@cmd
 
SET @cmd = 'Databases("mydbname").Views("vw_myviewname").script(5,"c:\ee\vw_myviewname.sql")'
EXEC @result = sp_OAMethod @object, @cmd
 
-- Views, Tables, StoredProcedures, Functions pretty much use the same format as above e.g. databases("dbname").Tables("tablename")
-- indexes, triggers, checks, keys you need databases("dbname").Tables("tablename").indexes("idxname")
 
EXEC @result = sp_OADestroy @object
GO

Open in new window

0
 

Author Comment

by:arvapal
ID: 23671021
Hi Mark,

Thank you very much for the quick solution.

The above sql script is working fine. My requirement is to generate the required tables/SPs/Views/UDFs in a single .sql file.

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23676490
Thought it was for "Not all at a time, only few stored procedures/UDFs/Views"

Do you have access to SSMS ? You can use the generate scripts wizard and select which objects you want.

Right click on the database, go into Tasks, then Generate scripts. The scripting wizard appears. If you get the banner page, press next.
Then select a database (the one you right clicked on should be selected), press next
Then in "Choose Script Options" scroll down and select things like Indexes etc... press next
Then select the types of objects, press next
Then for each of those objects (seperate screen at a time) select the individual items you want scripted
Repeat for next object type
then fill in export options and finish.

Using the "manual" / "code" method, how are you going to provide a list of items to script ? Can get a single file by doing a copy this1+this2+this3 that123 after the script has run and you are back in command line mode.
0
 

Author Comment

by:arvapal
ID: 23678074
Hi Mark,

I am familiar with the Generate scripts using SSMS, but i need to automate that task through command prompt and task scheduler.

About the list of requied items, i will be running the below sql command.

select distinct [schema], object from Admin.DatabaseLog where PostTime between convert(datetime, '18/02/2009', 103) and convert(datetime, '19/02/2009 23:59:59', 103)

Once after executing the above script, i will get all the modified objects during the given time.

Now my requirement is to generate one sql script file which contains the above modified objects only.


0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23678131
Any indication as to object type ? will they be unique names ?  any indication of dependancies ? or just straight scripts concatenated together as they occure from the query...

Basic approach will be to :

1) create a script file
1.a) probably create a cursor to get each object
1.b) use script similar to above to generate scripts
2) create a batch file
2.a) tidy up a "work area"
2.b) run script (osql or sqlcmd)
2.c) copy concatenate individual scripts from "work area" to destination with datetime as part of name
2.d) remove work area files

Sound OK ?

0
 
LVL 38

Expert Comment

by:Jim P.
ID: 23739154
Try this: SQL Server Database Publishing Wizard. It's designed for transferring whatever you need between your local database and say a hosted website that you can't directly insert data. It should be what you need.


Microsoft SQL Server Database Publishing Wizard 1.1
http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23741449
I take it you did not like the approach in  ID:23678131
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 23741789
Ok.. Just create a procedure given here:

http://vyaskn.tripod.com/code/generate_inserts.txt

Once you have created it, then you can execute that sql from command prompt using sqlcmd and then create the Database Scripts. Hope this helps you out.
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 23746043
The following should create a single file with all changed tables with their dependent objects (indexes, PK, FK, default, ...). If you have to differenciate for Views aso., you need to check the type of the object and execute e.g. Database(...).Views(...) accordingly.

For the strange integer constant of script method, see BOL, Index SQLDMO_SCRIPT_TYPE.





DECLARE @cmd varchar(255)
DECLARE @dmo int
DECLARE @result int
DECLARE @object varchar(100);
DECLARE @schema varchar( 25);
 
SET NOCOUNT ON
EXEC @result = sp_OACreate 'SQLDMO.SQLServer', @dmo OUT
 
-- EXEC @result = sp_OASetProperty @object, 'LoginSecure', TRUE    -- used for integrated windows security
 
EXEC @result = sp_OASetProperty @dmo, 'Login', 'sa'
EXEC @result = sp_OASetProperty @dmo, 'password', 'sapassword'
 
SET @cmd = 'Connect(myservername)'
EXEC @result = sp_OAMethod @dmo,@cmd
 
declare csr cursor for select distinct [schema], object from Admin.DatabaseLog where PostTime between convert(datetime, '18/02/2009', 103) and convert(datetime, '19/02/2009 23:59:59', 103) for read only;
open csr
fetch csr into @schema, @object
while @@fetch_status = 0
begin
   set @cmd = 'Databases("mydbname").Tables("'+@object+'","'+@schema+'").script(256+73736+469762048+16777216+33554432+67108864,"c:\ee\new_objects.sql")'
   EXEC @result = sp_OAMethod @object, @cmd
   fetch csr into @schema, @object
end
deallocate csr
 
EXEC @result = sp_OADestroy @object
go

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23746369
@qlemo, nice work, I was still wanting to hear back what was wrong with the basic approach. Yes, you can use script options to append, but wasn't too sure if it was going to pan out that way - you show it can.

This magic number are part of the script object : http://msdn.microsoft.com/en-us/library/ms142610.aspx (there is also a 2005 link) to understand what the options are.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23746588
Ummmm, some of those combo's might not work in conjunction with some of the object types - fine for tables, but is there anything else in the list of objects ?

Would be inclined to have a case on the type of object, and use the 256 which says append, and forget the rest... or use 532676608 for all DRI options (linked as "or")

But it really does depend on what we get from ADMIN.Databaselog
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 23746864
Mark and Qlemo,

Have you looked at the publishing wizard? It is just sweet. Makes it really easy without having to code it yourself.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23747507
Yep, I have seen it before - actually used it. It is very good, no questions.
The only reason why I went to code was the selective nature of the objects to be altered - thought it would be easier (go figure).
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 23747766
From the nature of the question -- "Not all at a time, only few stored..." It sounds like he's trying to do some sort of update to distribute with a new version of an application.

0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 23747873
if only
stored procedures/UDFs/Views
you can use

select o.name,c.text from syscomments c inner join sysobjects o on c.id=o.id
where o.xtype in ('P','FN','V')
 
and via osql\sqlcmd just send the result into file from cmd (xp_cmdshell).etc
---
if more objects you need to use .NET SMO code
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 23747964
like this SMO based  cmd apps
How to Script Out SQL Server Objects
By ozkar garcia
 
http://www.codeproject.com/KB/vbscript/0g_SqlExtract.aspx
 
just in case
SQL Server objects in SourceSafe using SMO and Visual SourceSafe automation
By Levent Soyalp
http://www.codeproject.com/KB/database/DBScriptSMO.aspx 
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 23748104
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23748343
@eugeneZ - whats wrong with the scripts here ?
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 25487016
I'm thinking PAQ-Refund. A bunch of valuable info, but no real standout answer.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 25488162
Many scripts were provided here..
arvapal can try it out and can list how many works out so that it can be closed accordingly.
0
 

Accepted Solution

by:
ee_auto earned 0 total points
ID: 25578374
Question PAQ'd, 500 points not refunded, and stored in the solution database.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

830 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