Any SQL Server Guru?

Dear SQL Server Gurus:
Your input and advises are greatly appreciated!
We have a SQL Server 2005 database served for Decision Support and Reporting
purposes and it is about 15 G and growing moderately. The source data is
from DB2, an on line transactional database. Everyday, data (existing or
modified tables with data) was transformed into a SQL server staging database
and got backup on that server. Then the backup was ftp to our produciton
server and the restore process was kick off to restore the database. During
that restore process, our production database got refreshed, however, all
views/functions/store procedures created and edited by the developer will be
gone too. To resolve this problem, everyday our developer before going home,
she would generate all scripts (including views/functions/procedures) thru
Server Management studio and run it thru command line from a job scheduler
right after the restore process was done. We have over 300 views, functions
and store procedures. When the developer generates the scripts, she needs
to manually reset value on the Server Management Studio and it is pretty
risky, for if she did not reset value right (for instance, set dependency to
true), she will not get the correct scripts and the restore process will wipe
out the scripts and the reporting programs would fail. I am looking for a
way to streamline the developer's working procedures.
Is there any way to keep the functions/views/procedure stored on the
database when the tables and data got refreshed? or do you have any other
recommendation?
Thanks,
Jenny1) Create a separate DB for the functions and procs.
2) Use 3-part naming for the objects they access.
3) Create a separate DB with data only. Refresh just that DB. The
objects referred to in #2 are in this DB.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
Dear SQL Server Gurus:
Your input and advises are greatly appreciated!
We have a SQL Server 2005 database served for Decision Support and Reporting
purposes and it is about 15 G and growing moderately. The source data is
from DB2, an on line transactional database. Everyday, data (existing or
modified tables with data) was transformed into a SQL server staging
database
and got backup on that server. Then the backup was ftp to our produciton
server and the restore process was kick off to restore the database. During
that restore process, our production database got refreshed, however, all
views/functions/store procedures created and edited by the developer will be
gone too. To resolve this problem, everyday our developer before going
home,
she would generate all scripts (including views/functions/procedures) thru
Server Management studio and run it thru command line from a job scheduler
right after the restore process was done. We have over 300 views, functions
and store procedures. When the developer generates the scripts, she needs
to manually reset value on the Server Management Studio and it is pretty
risky, for if she did not reset value right (for instance, set dependency to
true), she will not get the correct scripts and the restore process will
wipe
out the scripts and the reporting programs would fail. I am looking for a
way to streamline the developer's working procedures.
Is there any way to keep the functions/views/procedure stored on the
database when the tables and data got refreshed? or do you have any other
recommendation?
Thanks,
Jenny|||Sounds like best option is to create another database for all of the
reporting stored procedures/functions/views. It will take a little bit of
work to go through all of the objects and change the object name to indicate
the database where the data is located. (Check Books Online for "External
Data and Transact-SQL".)
But the end result is that you 'refresh' process would not 'wipe out' the
objects.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>|||> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too.
Rather that generate scripts, consider keeping the 'master' scripts under
source control (or at least in the file system). You can then run the
scripts as part of a post-restore process. This is much less risky than
using the database as the source and generating scripts. Keeping scripts
under source control is a Best Practice in SQL Server development.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>|||3-part naming of objects is of the form:
SQL 2000: Database.Owner.ObjectName
SQL 2005: Database.Schema.ObjectName
Thus, you would refer to a table in the MyDB database as:
MyDB.dbo.MyTable
You would have 2 DB's, say, DBFunc and DBData. Place your stored procs and
functions in DBFunc. Place your data in DBData. In the functions/procs,
refer to your tables in the form shown above.
HTH
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:3F2249E6-86F6-454A-9DCA-1C4F194348D4@.microsoft.com...
Hi Tom,
What do you mean in item 2 and 3?
I created a backup db and truncate data, which would fullfilled the first
comments you mentioned. Then what is next? I don't quite understand what
you said on item 2 & 3.
Thanks,
"Tom Moreau" wrote:
> 1) Create a separate DB for the functions and procs.
> 2) Use 3-part naming for the objects they access.
> 3) Create a separate DB with data only. Refresh just that DB. The
> objects referred to in #2 are in this DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Jenny" <Jenny@.discussions.microsoft.com> wrote in message
> news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>