generate scripts from management studio

Hello all,
Our database gets replaced every day with new tables and data. Basically
the routine job is to retore database from other source database, in which we
get fresh data and tables. However, it will also wipe out all views,
procedures and functions created by developer. So, every day, our developer
will need to generate the scripts before the database get replaced and rerun
the scripts for all views/procedures/functions. The way we generate the
script is from the SQL Server Manager Studio, the problem is, the developer
will need to click the necessary options for the scripts, for instance, she
needs to reset the dependency as true every time she generates the script,
and in some degree, it is riskey, because if she forgot reset certain options
then it will not generate the correct scripts. Is there a way to set the
options on Server Management Studio permanantly? or any other better ideas to
get the functions/views/procedures scripts?
Thanks,Why would you first drop objects if you then restore the database? In case I misunderstood you:
Write a small app for the script generation. All the script generation liven in the SMO programming
API, and is really easy to use. See http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:1BFD57F7-1C08-484F-9773-216419ECE254@.microsoft.com...
> Hello all,
> Our database gets replaced every day with new tables and data. Basically
> the routine job is to retore database from other source database, in which we
> get fresh data and tables. However, it will also wipe out all views,
> procedures and functions created by developer. So, every day, our developer
> will need to generate the scripts before the database get replaced and rerun
> the scripts for all views/procedures/functions. The way we generate the
> script is from the SQL Server Manager Studio, the problem is, the developer
> will need to click the necessary options for the scripts, for instance, she
> needs to reset the dependency as true every time she generates the script,
> and in some degree, it is riskey, because if she forgot reset certain options
> then it will not generate the correct scripts. Is there a way to set the
> options on Server Management Studio permanantly? or any other better ideas to
> get the functions/views/procedures scripts?
>
> Thanks,|||Hi Jenny
There are several ways you may want to get around this. If your stored
procedures, views and functions and are in a source code control system, they
could be extracted and run against the database. This would be better than
storing the source code on disc and just running each of the files using
SQLCMD. If you wish to execute all file in a give directory with osql with a
command like:
for %i in (*.sql) do SQLCMD -E -d Mydb -i %i -o %i.out
An alternative approach would be to rename the existing database, restore
the backup, then use a SSIS package to copy them.
A third approach would be to use SMO instead of using the batch files or
wizards.
John
"Jenny" wrote:
> Hello all,
> Our database gets replaced every day with new tables and data. Basically
> the routine job is to retore database from other source database, in which we
> get fresh data and tables. However, it will also wipe out all views,
> procedures and functions created by developer. So, every day, our developer
> will need to generate the scripts before the database get replaced and rerun
> the scripts for all views/procedures/functions. The way we generate the
> script is from the SQL Server Manager Studio, the problem is, the developer
> will need to click the necessary options for the scripts, for instance, she
> needs to reset the dependency as true every time she generates the script,
> and in some degree, it is riskey, because if she forgot reset certain options
> then it will not generate the correct scripts. Is there a way to set the
> options on Server Management Studio permanantly? or any other better ideas to
> get the functions/views/procedures scripts?
>
> Thanks,|||Hi John,
I am not quite sure if our developer using source code control system. I
know she is using Visual Studio. So the first way might not work.
The second way you mentioned - rename the database and restore the backup
and then use SSIS package to copy it. It could work tempory, since I will
need to have 2 database capacity. Our database is around 15 G and it is
growing, I will need to watch the space very carefully.
The third one you mentioned maybe will work better, but I don't know how to
use SMO. Do you have a quick way to do the SMO?
Thanks,
Jenny
"John Bell" wrote:
> Hi Jenny
> There are several ways you may want to get around this. If your stored
> procedures, views and functions and are in a source code control system, they
> could be extracted and run against the database. This would be better than
> storing the source code on disc and just running each of the files using
> SQLCMD. If you wish to execute all file in a give directory with osql with a
> command like:
> for %i in (*.sql) do SQLCMD -E -d Mydb -i %i -o %i.out
> An alternative approach would be to rename the existing database, restore
> the backup, then use a SSIS package to copy them.
> A third approach would be to use SMO instead of using the batch files or
> wizards.
> John
> "Jenny" wrote:
> > Hello all,
> >
> > Our database gets replaced every day with new tables and data. Basically
> > the routine job is to retore database from other source database, in which we
> > get fresh data and tables. However, it will also wipe out all views,
> > procedures and functions created by developer. So, every day, our developer
> > will need to generate the scripts before the database get replaced and rerun
> > the scripts for all views/procedures/functions. The way we generate the
> > script is from the SQL Server Manager Studio, the problem is, the developer
> > will need to click the necessary options for the scripts, for instance, she
> > needs to reset the dependency as true every time she generates the script,
> > and in some degree, it is riskey, because if she forgot reset certain options
> > then it will not generate the correct scripts. Is there a way to set the
> > options on Server Management Studio permanantly? or any other better ideas to
> > get the functions/views/procedures scripts?
> >
> >
> >
> > Thanks,|||The source data actually is from a DB2 database. All tables and data are map
to a sql server database (a transition database). Every day the transition
database get backup and then the bakcup is ftp to our server. And then the
retore process get started.
"Tibor Karaszi" wrote:
> Why would you first drop objects if you then restore the database? In case I misunderstood you:
> Write a small app for the script generation. All the script generation liven in the SMO programming
> API, and is really easy to use. See http://www.karaszi.com/SQLServer/info_generate_script.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jenny" <Jenny@.discussions.microsoft.com> wrote in message
> news:1BFD57F7-1C08-484F-9773-216419ECE254@.microsoft.com...
> > Hello all,
> >
> > Our database gets replaced every day with new tables and data. Basically
> > the routine job is to retore database from other source database, in which we
> > get fresh data and tables. However, it will also wipe out all views,
> > procedures and functions created by developer. So, every day, our developer
> > will need to generate the scripts before the database get replaced and rerun
> > the scripts for all views/procedures/functions. The way we generate the
> > script is from the SQL Server Manager Studio, the problem is, the developer
> > will need to click the necessary options for the scripts, for instance, she
> > needs to reset the dependency as true every time she generates the script,
> > and in some degree, it is riskey, because if she forgot reset certain options
> > then it will not generate the correct scripts. Is there a way to set the
> > options on Server Management Studio permanantly? or any other better ideas to
> > get the functions/views/procedures scripts?
> >
> >
> >
> > Thanks,
>|||Hi Jenny
If you are not using version control, you will not have a process of rolling
back any changes to a known version. Visual Studio has close integration with
source code control systems, particularly Visual Source Safe, therefore it
would not require any effort for you developer to use it other than the
initial configuration.
If you want to limit the space used by the current database, backup the
current database before renaming it, then truncate all the tables and shrink
the data file.
John
John
"Jenny" wrote:
> Hi John,
> I am not quite sure if our developer using source code control system. I
> know she is using Visual Studio. So the first way might not work.
> The second way you mentioned - rename the database and restore the backup
> and then use SSIS package to copy it. It could work tempory, since I will
> need to have 2 database capacity. Our database is around 15 G and it is
> growing, I will need to watch the space very carefully.
> The third one you mentioned maybe will work better, but I don't know how to
> use SMO. Do you have a quick way to do the SMO?
>
> Thanks,
> Jenny
> "John Bell" wrote:
> > Hi Jenny
> >
> > There are several ways you may want to get around this. If your stored
> > procedures, views and functions and are in a source code control system, they
> > could be extracted and run against the database. This would be better than
> > storing the source code on disc and just running each of the files using
> > SQLCMD. If you wish to execute all file in a give directory with osql with a
> > command like:
> > for %i in (*.sql) do SQLCMD -E -d Mydb -i %i -o %i.out
> >
> > An alternative approach would be to rename the existing database, restore
> > the backup, then use a SSIS package to copy them.
> >
> > A third approach would be to use SMO instead of using the batch files or
> > wizards.
> >
> > John
> >
> > "Jenny" wrote:
> >
> > > Hello all,
> > >
> > > Our database gets replaced every day with new tables and data. Basically
> > > the routine job is to retore database from other source database, in which we
> > > get fresh data and tables. However, it will also wipe out all views,
> > > procedures and functions created by developer. So, every day, our developer
> > > will need to generate the scripts before the database get replaced and rerun
> > > the scripts for all views/procedures/functions. The way we generate the
> > > script is from the SQL Server Manager Studio, the problem is, the developer
> > > will need to click the necessary options for the scripts, for instance, she
> > > needs to reset the dependency as true every time she generates the script,
> > > and in some degree, it is riskey, because if she forgot reset certain options
> > > then it will not generate the correct scripts. Is there a way to set the
> > > options on Server Management Studio permanantly? or any other better ideas to
> > > get the functions/views/procedures scripts?
> > >
> > >
> > >
> > > Thanks,