Automating a Backup and Restore across Servers

Hi,

I have been experiencing a problem with a job I am trying to automate. My team requires two instances of a db on development. Every week, db1 is detached and reattached as db1_copy, and a new copy of db1 from production is copied over. I have used cursors for the dropusers, addlogins, grantdbaccess, addroles, and addrolemembers aspects of the restore process. Also, we use sql authentication and not windows authentication. The issue is that when the agent encounters a minor issue, ie. a login that already exists in that db, or adding a rolemember to a role that is already there, I get an error. I have ensure that at that step, the job simply proceeds because it is a minor step in the process (it happens during the script execution when done manually but I can override this because I am present and just let it run in one step instead of many). Is there any way to do this without adding a multitude of steps?

Thanks,

MariaHi Maria,
try if putting the statement

SET XACT_ABORT OFF

in the TSQL-stream of the step solves your problem.

Automated testing | How to "force" GETDATE() function to return specific value?

Hello,

Our QA team have running a lot of test scripts (for automated regression
testing), they run them on the different databases (Oracle/MS SQL).
Several of those tests are dependent on the current date/time. In order to
be
able to use them efficiently, we changed the current date/time on the QA
database server to a specific date/time before starting the scripts, so we
are sure the test scripts always run in the same environment.

Resetting the date/time of the database server gives us more and more
problems (OS problems, backup/ virusscan, ...).

It is possible to fix the problem with SYSDATE function on Oracle by setting
FIXED_DATE init parameter.

Is it possible to 'change' the current date/time on 'database' level,
instead of on OS level for MSSQL2000?
Do you know other means to do such things?

Thanks in advance,

Konstantin"Konstantin Zakharenko" <kzakharenko@.infopulse.com.ua> wrote in message
news:bpt1s9$ctp$1@.snoopy.infopulse.com.ua...
> Hello,
> Our QA team have running a lot of test scripts (for automated regression
> testing), they run them on the different databases (Oracle/MS SQL).
> Several of those tests are dependent on the current date/time. In order to
> be
> able to use them efficiently, we changed the current date/time on the QA
> database server to a specific date/time before starting the scripts, so we
> are sure the test scripts always run in the same environment.
> Resetting the date/time of the database server gives us more and more
> problems (OS problems, backup/ virusscan, ...).
> It is possible to fix the problem with SYSDATE function on Oracle by
setting
> FIXED_DATE init parameter.
>
> Is it possible to 'change' the current date/time on 'database' level,
> instead of on OS level for MSSQL2000?
> Do you know other means to do such things?
> Thanks in advance,
> Konstantin

I don't know what FIXED_DATE does in Oracle - does it force SYSDATE to
return the same value every time it's called? If so, I don't believe there's
any way to do this in SQL Server. You could create your own function called
dbo.getdate(), and use that, but then it wouldn't be a valid test of your
'real' code.

In any case, it's not clear why you would want to always test with the same
datetime value - you wouldn't prove anything except that your code works
with one particular datetime, and that probably isn't desirable. It might be
better to put some work into establishing what the test results should be
for a given datetime input value, and validate your code that way.

If I've misunderstood, or if this doesn't help, perhaps you can clarify
exactly what you want getdate() to return, and how you want to use it.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:3fc25218$1_3@.news.bluewin.ch...
> "Konstantin Zakharenko" <kzakharenko@.infopulse.com.ua> wrote in message
> news:bpt1s9$ctp$1@.snoopy.infopulse.com.ua...
> > Hello,
> > Our QA team have running a lot of test scripts (for automated regression
> > testing), they run them on the different databases (Oracle/MS SQL).
> > Several of those tests are dependent on the current date/time. In order
to
> > be
> > able to use them efficiently, we changed the current date/time on the QA
> > database server to a specific date/time before starting the scripts, so
we
> > are sure the test scripts always run in the same environment.
> > Resetting the date/time of the database server gives us more and more
> > problems (OS problems, backup/ virusscan, ...).
> > It is possible to fix the problem with SYSDATE function on Oracle by
> setting
> > FIXED_DATE init parameter.
> > Is it possible to 'change' the current date/time on 'database' level,
> > instead of on OS level for MSSQL2000?
> > Do you know other means to do such things?
> > Thanks in advance,
> > Konstantin
> I don't know what FIXED_DATE does in Oracle - does it force SYSDATE to
> return the same value every time it's called? If so, I don't believe
there's
> any way to do this in SQL Server. You could create your own function
called
> dbo.getdate(), and use that, but then it wouldn't be a valid test of your
> 'real' code.
> In any case, it's not clear why you would want to always test with the
same
> datetime value - you wouldn't prove anything except that your code works
> with one particular datetime, and that probably isn't desirable. It might
be
> better to put some work into establishing what the test results should be
> for a given datetime input value, and validate your code that way.
> If I've misunderstood, or if this doesn't help, perhaps you can clarify
> exactly what you want getdate() to return, and how you want to use it.
> Simon

Thank you for replay.

Yes. You are right. The FIXED_DATE lets you set a constant date that SYSDATE
will always return instead of the current date.

The main problem is that if you take a fixed test database, and run for
example a report, the output of that report can be (very) different if you
run this report today vs. the run you will do tomorrow vs. ... Other typical
example: our application (financial one) reacts differently if you want to
create some entities with dates in the past (different execution flow).

We are using Rational Robot in the automated testing. It validates that all
system reaction and outcome is identical to the reference run (verification
points). If the 'current date/time' is changed since the 'reference' run,
you get a lot of (unnecessary) errors/warnings and failed verification
points. That's why we need to run test scripts with specific datetime value.

Of course, automated tests are not intended to replace all other tests,
their purpose to supplement another tests. Automated test scripts are mainly
used for regression testing. Since it is very difficult and time-consuming
to make Robot test scripts time independent it is acceptable for us to
execute tests with fixed datetime.

Unfortunately, calling of new dbo.getdate() function will cause modification
of application. It is time-consuming and task. That's why our main goal is
to solve the problem by means of Oracle/MSSQL parameters. Moreover, your
solution with dbo.getdate() will not work in a case when we need correct
datetime (not fixed).

CREATE FUNCTION dbo.getdate()
RETURNS datetime AS
BEGIN
-- return cast ('2003-01-01' as datetime) -- It is OK.
return getdate() -- It is NOT OK.
END

The GETDATE() is a nondeterministic function and it is not allowed to use it
in user-defined functions.

If we will not solve the problem by configuring MSSQL database (I think it
is most likely) our solution will be to create the following view:

CREATE VIEW v_nondeterministic AS SELECT getdate() AS getdate;

And to use SELECT getdate FROM v_nondeterministic where required.

Best regards,

Konstantin