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.

Automatic file grown

Hello:
I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
fromtime to time every statement trying to insert or update data gets
blocked and dies with timeout. Looking for more detail I have found that
this happens when SQL server is growing its files. The DB is configured to
grow automatically (+100 MB) every time needed, and has no size limit.
To correct this lock, I run a INSERT statement from SQL query analizer, wait
for a minute and the statement is correctly executed, the server unlocked
and my programs continue inserting data. INSERT statements launched from
programs die with timeout and don't solve the problem.
I'd like to have more control over DB grown. Where can I see when has the DB
grown? Can I force file grown when I want, ata some fewer-work times? Where
can I find more information about this?
Thanks in advance, best regardsAuto-grow should be an emergency fail-safe only. You should actively
monitor the database sizes and grow them ahead of time in an off-peak hour.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert T." <r_t@.nospam.nospam> wrote in message
news:ej5%23rhqjEHA.3624@.TK2MSFTNGP10.phx.gbl...
> Hello:
> I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
> fromtime to time every statement trying to insert or update data gets
> blocked and dies with timeout. Looking for more detail I have found that
> this happens when SQL server is growing its files. The DB is configured to
> grow automatically (+100 MB) every time needed, and has no size limit.
> To correct this lock, I run a INSERT statement from SQL query analizer,
wait
> for a minute and the statement is correctly executed, the server unlocked
> and my programs continue inserting data. INSERT statements launched from
> programs die with timeout and don't solve the problem.
> I'd like to have more control over DB grown. Where can I see when has the
DB
> grown? Can I force file grown when I want, ata some fewer-work times?
Where
> can I find more information about this?
> Thanks in advance, best regards
>|||In addition to Geoff's post, you find a couple of stored procedures at .com" target="_blank">www.dbmaint
.com (see the "free
utilities section" for jobs that can serve as inspiration...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert T." <r_t@.nospam.nospam> wrote in message news:ej5%23rhqjEHA.3624@.TK2MSFTNGP10.phx.gb
l...
> Hello:
> I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
> fromtime to time every statement trying to insert or update data gets
> blocked and dies with timeout. Looking for more detail I have found that
> this happens when SQL server is growing its files. The DB is configured to
> grow automatically (+100 MB) every time needed, and has no size limit.
> To correct this lock, I run a INSERT statement from SQL query analizer, wa
it
> for a minute and the statement is correctly executed, the server unlocked
> and my programs continue inserting data. INSERT statements launched from
> programs die with timeout and don't solve the problem.
> I'd like to have more control over DB grown. Where can I see when has the
DB
> grown? Can I force file grown when I want, ata some fewer-work times? Wher
e
> can I find more information about this?
> Thanks in advance, best regards
>

Automatic file grown

Hello:
I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
fromtime to time every statement trying to insert or update data gets
blocked and dies with timeout. Looking for more detail I have found that
this happens when SQL server is growing its files. The DB is configured to
grow automatically (+100 MB) every time needed, and has no size limit.
To correct this lock, I run a INSERT statement from SQL query analizer, wait
for a minute and the statement is correctly executed, the server unlocked
and my programs continue inserting data. INSERT statements launched from
programs die with timeout and don't solve the problem.
I'd like to have more control over DB grown. Where can I see when has the DB
grown? Can I force file grown when I want, ata some fewer-work times? Where
can I find more information about this?
Thanks in advance, best regards
Auto-grow should be an emergency fail-safe only. You should actively
monitor the database sizes and grow them ahead of time in an off-peak hour.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert T." <r_t@.nospam.nospam> wrote in message
news:ej5%23rhqjEHA.3624@.TK2MSFTNGP10.phx.gbl...
> Hello:
> I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
> fromtime to time every statement trying to insert or update data gets
> blocked and dies with timeout. Looking for more detail I have found that
> this happens when SQL server is growing its files. The DB is configured to
> grow automatically (+100 MB) every time needed, and has no size limit.
> To correct this lock, I run a INSERT statement from SQL query analizer,
wait
> for a minute and the statement is correctly executed, the server unlocked
> and my programs continue inserting data. INSERT statements launched from
> programs die with timeout and don't solve the problem.
> I'd like to have more control over DB grown. Where can I see when has the
DB
> grown? Can I force file grown when I want, ata some fewer-work times?
Where
> can I find more information about this?
> Thanks in advance, best regards
>
|||In addition to Geoff's post, you find a couple of stored procedures at www.dbmaint.com (see the "free
utilities section" for jobs that can serve as inspiration...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert T." <r_t@.nospam.nospam> wrote in message news:ej5%23rhqjEHA.3624@.TK2MSFTNGP10.phx.gbl...
> Hello:
> I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
> fromtime to time every statement trying to insert or update data gets
> blocked and dies with timeout. Looking for more detail I have found that
> this happens when SQL server is growing its files. The DB is configured to
> grow automatically (+100 MB) every time needed, and has no size limit.
> To correct this lock, I run a INSERT statement from SQL query analizer, wait
> for a minute and the statement is correctly executed, the server unlocked
> and my programs continue inserting data. INSERT statements launched from
> programs die with timeout and don't solve the problem.
> I'd like to have more control over DB grown. Where can I see when has the DB
> grown? Can I force file grown when I want, ata some fewer-work times? Where
> can I find more information about this?
> Thanks in advance, best regards
>

Automatic file grown

Hello:
I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
fromtime to time every statement trying to insert or update data gets
blocked and dies with timeout. Looking for more detail I have found that
this happens when SQL server is growing its files. The DB is configured to
grow automatically (+100 MB) every time needed, and has no size limit.
To correct this lock, I run a INSERT statement from SQL query analizer, wait
for a minute and the statement is correctly executed, the server unlocked
and my programs continue inserting data. INSERT statements launched from
programs die with timeout and don't solve the problem.
I'd like to have more control over DB grown. Where can I see when has the DB
grown? Can I force file grown when I want, ata some fewer-work times? Where
can I find more information about this?
Thanks in advance, best regardsAuto-grow should be an emergency fail-safe only. You should actively
monitor the database sizes and grow them ahead of time in an off-peak hour.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Robert T." <r_t@.nospam.nospam> wrote in message
news:ej5%23rhqjEHA.3624@.TK2MSFTNGP10.phx.gbl...
> Hello:
> I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
> fromtime to time every statement trying to insert or update data gets
> blocked and dies with timeout. Looking for more detail I have found that
> this happens when SQL server is growing its files. The DB is configured to
> grow automatically (+100 MB) every time needed, and has no size limit.
> To correct this lock, I run a INSERT statement from SQL query analizer,
wait
> for a minute and the statement is correctly executed, the server unlocked
> and my programs continue inserting data. INSERT statements launched from
> programs die with timeout and don't solve the problem.
> I'd like to have more control over DB grown. Where can I see when has the
DB
> grown? Can I force file grown when I want, ata some fewer-work times?
Where
> can I find more information about this?
> Thanks in advance, best regards
>|||In addition to Geoff's post, you find a couple of stored procedures at www.dbmaint.com (see the "free
utilities section" for jobs that can serve as inspiration...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert T." <r_t@.nospam.nospam> wrote in message news:ej5%23rhqjEHA.3624@.TK2MSFTNGP10.phx.gbl...
> Hello:
> I'm experiencing this problem with SQL 2000. I have a large DB (7 GB) and
> fromtime to time every statement trying to insert or update data gets
> blocked and dies with timeout. Looking for more detail I have found that
> this happens when SQL server is growing its files. The DB is configured to
> grow automatically (+100 MB) every time needed, and has no size limit.
> To correct this lock, I run a INSERT statement from SQL query analizer, wait
> for a minute and the statement is correctly executed, the server unlocked
> and my programs continue inserting data. INSERT statements launched from
> programs die with timeout and don't solve the problem.
> I'd like to have more control over DB grown. Where can I see when has the DB
> grown? Can I force file grown when I want, ata some fewer-work times? Where
> can I find more information about this?
> Thanks in advance, best regards
>