Duplicate Database

I need to make an exact duplicate of a database on the same instance of SQL
Server 2000; seems like it should be easy, but i'm stuck ...
Can anyone point me in the right direction on this ?
TIA
Liz
Liz wrote:
> I need to make an exact duplicate of a database on the same instance of SQL
> Server 2000; seems like it should be easy, but i'm stuck ...
> Can anyone point me in the right direction on this ?
> TIA
>
> Liz
>
Backup/restore, using the WITH MOVE option to create a new set of data
files.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||In case you need more detailed information about Tracy's suggestion, see
this article:
Using WITH MOVE in a Restore
http://www.support.microsoft.com/?id=221465
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
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:456F4BDE.5030604@.realsqlguy.com...
> Liz wrote:
> Backup/restore, using the WITH MOVE option to create a new set of data
> files.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:456F4BDE.5030604@.realsqlguy.com...
[vbcol=seagreen]
> Liz wrote:

> Backup/restore, using the WITH MOVE option to create a new set of data
> files.
sounds great ... but I don't see a "WITH MOVE" option in EM or in the T-SQL
docs; is this available on SQL 2000 ?
thanks ...
Liz
|||Yes, look up RESTORE in Books Online.
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
"Liz" <liz@.tiredofspam.com> wrote in message
news:udImlbMFHHA.1232@.TK2MSFTNGP05.phx.gbl...
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:456F4BDE.5030604@.realsqlguy.com...
>
>
> sounds great ... but I don't see a "WITH MOVE" option in EM or in the
> T-SQL docs; is this available on SQL 2000 ?
> thanks ...
> Liz
>
|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OaT8oZMFHHA.4712@.TK2MSFTNGP04.phx.gbl...
> In case you need more detailed information about Tracy's suggestion, see
> this article:
> Using WITH MOVE in a Restore
> http://www.support.microsoft.com/?id=221465
Thanks Arnie, Tracy ... got it; didn't realize it was a RESTORE option at
first ..
L
|||From Query Analyzer,execute below:-
1. Using Restore filelistonly command identify the logical file names of the
database backup file
RESTORE FILELISTONLY from disk='c:\x.bak'
2. With the output of the above query use RESTORE database
RESTORE DATABASE <newdbname> from disk='c:\backup\x.bak'
WITH move 'logical_mdf_filename' to 'new physical name with path',
move 'logical_ldf_filename' to 'new physical log name with
Path'
Change the database name and file names based on the backup and database
name you have.
Thanks
Hari
"Liz" <liz@.tiredofspam.com> wrote in message
news:udImlbMFHHA.1232@.TK2MSFTNGP05.phx.gbl...
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:456F4BDE.5030604@.realsqlguy.com...
>
>
> sounds great ... but I don't see a "WITH MOVE" option in EM or in the
> T-SQL docs; is this available on SQL 2000 ?
> thanks ...
> Liz
>
sql

Duplicate Database

I need to make an exact duplicate of a database on the same instance of SQL
Server 2000; seems like it should be easy, but i'm stuck ...
Can anyone point me in the right direction on this ?
TIA
LizLiz wrote:
> I need to make an exact duplicate of a database on the same instance of SQL
> Server 2000; seems like it should be easy, but i'm stuck ...
> Can anyone point me in the right direction on this ?
> TIA
>
> Liz
>
Backup/restore, using the WITH MOVE option to create a new set of data
files.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||In case you need more detailed information about Tracy's suggestion, see
this article:
Using WITH MOVE in a Restore
http://www.support.microsoft.com/?id=221465
--
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
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:456F4BDE.5030604@.realsqlguy.com...
> Liz wrote:
>> I need to make an exact duplicate of a database on the same instance of
>> SQL Server 2000; seems like it should be easy, but i'm stuck ...
>> Can anyone point me in the right direction on this ?
>> TIA
>>
>> Liz
>>
> Backup/restore, using the WITH MOVE option to create a new set of data
> files.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:456F4BDE.5030604@.realsqlguy.com...
> Liz wrote:
>> I need to make an exact duplicate of a database on the same instance of
>> SQL Server 2000; seems like it should be easy, but i'm stuck ...
>> Can anyone point me in the right direction on this ?
> Backup/restore, using the WITH MOVE option to create a new set of data
> files.
sounds great ... but I don't see a "WITH MOVE" option in EM or in the T-SQL
docs; is this available on SQL 2000 ?
thanks ...
Liz|||> sounds great ... but I don't see a "WITH MOVE" option in EM or in the T-SQL docs; is this
> available on SQL 2000 ?
Yes. It is indeed documented in BOL, RESTORE DATBASE. In EM restore dialog, you have it in the left
tab, where you can specify the desired physical filename for each file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Liz" <liz@.tiredofspam.com> wrote in message news:udImlbMFHHA.1232@.TK2MSFTNGP05.phx.gbl...
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:456F4BDE.5030604@.realsqlguy.com...
>> Liz wrote:
>> I need to make an exact duplicate of a database on the same instance of SQL Server 2000; seems
>> like it should be easy, but i'm stuck ...
>> Can anyone point me in the right direction on this ?
>
>> Backup/restore, using the WITH MOVE option to create a new set of data files.
>
> sounds great ... but I don't see a "WITH MOVE" option in EM or in the T-SQL docs; is this
> available on SQL 2000 ?
> thanks ...
> Liz
>|||Yes, look up RESTORE in Books Online.
--
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
"Liz" <liz@.tiredofspam.com> wrote in message
news:udImlbMFHHA.1232@.TK2MSFTNGP05.phx.gbl...
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:456F4BDE.5030604@.realsqlguy.com...
>> Liz wrote:
>> I need to make an exact duplicate of a database on the same instance of
>> SQL Server 2000; seems like it should be easy, but i'm stuck ...
>> Can anyone point me in the right direction on this ?
>
>> Backup/restore, using the WITH MOVE option to create a new set of data
>> files.
>
> sounds great ... but I don't see a "WITH MOVE" option in EM or in the
> T-SQL docs; is this available on SQL 2000 ?
> thanks ...
> Liz
>|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OaT8oZMFHHA.4712@.TK2MSFTNGP04.phx.gbl...
> In case you need more detailed information about Tracy's suggestion, see
> this article:
> Using WITH MOVE in a Restore
> http://www.support.microsoft.com/?id=221465
Thanks Arnie, Tracy ... got it; didn't realize it was a RESTORE option at
first ..
L|||From Query Analyzer,execute below:-
1. Using Restore filelistonly command identify the logical file names of the
database backup file
RESTORE FILELISTONLY from disk='c:\x.bak'
2. With the output of the above query use RESTORE database
RESTORE DATABASE <newdbname> from disk='c:\backup\x.bak'
WITH move 'logical_mdf_filename' to 'new physical name with path',
move 'logical_ldf_filename' to 'new physical log name with
Path'
Change the database name and file names based on the backup and database
name you have.
Thanks
Hari
"Liz" <liz@.tiredofspam.com> wrote in message
news:udImlbMFHHA.1232@.TK2MSFTNGP05.phx.gbl...
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:456F4BDE.5030604@.realsqlguy.com...
>> Liz wrote:
>> I need to make an exact duplicate of a database on the same instance of
>> SQL Server 2000; seems like it should be easy, but i'm stuck ...
>> Can anyone point me in the right direction on this ?
>
>> Backup/restore, using the WITH MOVE option to create a new set of data
>> files.
>
> sounds great ... but I don't see a "WITH MOVE" option in EM or in the
> T-SQL docs; is this available on SQL 2000 ?
> thanks ...
> Liz
>

Duplicate Database

I need to make an exact duplicate of a database on the same instance of SQL
Server 2000; seems like it should be easy, but i'm stuck ...
Can anyone point me in the right direction on this ?
TIA
LizLiz wrote:
> I need to make an exact duplicate of a database on the same instance of SQ
L
> Server 2000; seems like it should be easy, but i'm stuck ...
> Can anyone point me in the right direction on this ?
> TIA
>
> Liz
>
Backup/restore, using the WITH MOVE option to create a new set of data
files.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||In case you need more detailed information about Tracy's suggestion, see
this article:
Using WITH MOVE in a Restore
http://www.support.microsoft.com/?id=221465
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
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:456F4BDE.5030604@.realsqlguy.com...
> Liz wrote:
> Backup/restore, using the WITH MOVE option to create a new set of data
> files.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:456F4BDE.5030604@.realsqlguy.com...

> Liz wrote:
[vbcol=seagreen]
> Backup/restore, using the WITH MOVE option to create a new set of data
> files.
sounds great ... but I don't see a "WITH MOVE" option in EM or in the T-SQL
docs; is this available on SQL 2000 ?
thanks ...
Liz|||> sounds great ... but I don't see a "WITH MOVE" option in EM or in the T-SQL docs; is this

> available on SQL 2000 ?
Yes. It is indeed documented in BOL, RESTORE DATBASE. In EM restore dialog,
you have it in the left
tab, where you can specify the desired physical filename for each file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Liz" <liz@.tiredofspam.com> wrote in message news:udImlbMFHHA.1232@.TK2MSFTNGP05.phx.gbl...[v
bcol=seagreen]
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:456F4BDE.503
0604@.realsqlguy.com...
>
>
>
> sounds great ... but I don't see a "WITH MOVE" option in EM or in the T-SQ
L docs; is this
> available on SQL 2000 ?
> thanks ...
> Liz
>[/vbcol]|||Yes, look up RESTORE in Books Online.
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
"Liz" <liz@.tiredofspam.com> wrote in message
news:udImlbMFHHA.1232@.TK2MSFTNGP05.phx.gbl...
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:456F4BDE.5030604@.realsqlguy.com...
>
>
>
> sounds great ... but I don't see a "WITH MOVE" option in EM or in the
> T-SQL docs; is this available on SQL 2000 ?
> thanks ...
> Liz
>|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OaT8oZMFHHA.4712@.TK2MSFTNGP04.phx.gbl...
> In case you need more detailed information about Tracy's suggestion, see
> this article:
> Using WITH MOVE in a Restore
> http://www.support.microsoft.com/?id=221465
Thanks Arnie, Tracy ... got it; didn't realize it was a RESTORE option at
first ..
L|||From Query Analyzer,execute below:-
1. Using Restore filelistonly command identify the logical file names of the
database backup file
RESTORE FILELISTONLY from disk='c:\x.bak'
2. With the output of the above query use RESTORE database
RESTORE DATABASE <newdbname> from disk='c:\backup\x.bak'
WITH move 'logical_mdf_filename' to 'new physical name with path',
move 'logical_ldf_filename' to 'new physical log name with
Path'
Change the database name and file names based on the backup and database
name you have.
Thanks
Hari
"Liz" <liz@.tiredofspam.com> wrote in message
news:udImlbMFHHA.1232@.TK2MSFTNGP05.phx.gbl...
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:456F4BDE.5030604@.realsqlguy.com...
>
>
>
> sounds great ... but I don't see a "WITH MOVE" option in EM or in the
> T-SQL docs; is this available on SQL 2000 ?
> thanks ...
> Liz
>

DTS!! Plese help!

I've been stuck with this for a week and I seriously need help!

I have a DTS package with only 2 tasks, an ExecuteSqlTask that loads global variables and a TransformDataTask.

The TransformDataTask "source" is a query over one table, and the "destination" is another table. The transformation itself is an ActivexScript, written in VBscript, that performs several checks over source data and retrieves "data warehouse keys" from the DW tables using lookups.

So far, everything seems ok.
If I test the transformation it works fine, and produces a text output that (for testing purposes) I have tried to insert into the destination table, and this also works!!!!
But when I try to execute the package via Enterprise Manager the execution halts. I've set "Row Count Step" to 1, in order to guess wich line had the problem. This number changes but independetly from sorting data. Execution halts when less than 100 rows have been processed!!!! It actually doesn't halt, it says "Running" but stays like that for even days!!!!!!

Ok, I' ll send the code.
All the lookups "retrieve" values, except "actualiza_valores" that performs an update.

Any ideas would be apreciated!!!!
thanks in advance
lorena

Function Main()

Dim ASIGNADA
Dim CONFIRMADA
Dim NO_REALIZADA
Dim ULTERIOR
Dim PRIMERA_VEZ
Dim PROGRAMADA
Dim NO_PROGRAMADA
Dim NUEVA
Dim REPETIDA
Dim fecha
Dim TIEMPO_ESPERA
Dim cantAnio
Dim edad
Dim msg

ASIGNADA=0
CONFIRMADA=0
NO_REALIZADA=0
ULTERIOR = 0
PRIMERA_VEZ=0
NUEVA = 0
REPETIDA=0
TIEMPO_ESPERA= 0
PROGRAMADA = 0
NO_PROGRAMADA = 0
cantAnio = 0
msg="hola"

'Hay que ver si hay una consulta para el mismo paciente, profesional, servicio y fecha.
'Si es asi se actualizan las medidas de ese hecho.
'El tiempo de espera sera el promedio de los tiempos de espera

fecha = DTSLookups("get_id_fecha").Execute(DTSSource("fecha_consulta"))
servicio =DTSLookups("get_id_servicio").Execute(DTSSource("servicio_clave"))

If IsNull(DTSSource("profesional_clave")) Then
msg="El profesional llego NULL"
DTSLookups("set_entrada_log").Execute(msg)
profesional = DTSLookups("get_id_profesional").Execute(-1)
Else
profesional = DTSLookups("get_id_profesional").Execute(DTSSource("profesional_clave"))
End If
If IsEmpty(profesional) Then
msg="El profesional "&CStr(DTSSource("profesional_clave"))&" no esta en la dimension"
' DTSPackageLog.WriteStringToLog msg
profesional = DTSLookups("get_id_profesional").Execute(-1)
End If

cliente=DTSLookups("get_id_cliente").Execute(DTSSource("cliente_clave"))
If IsEmpty(cliente) Then
' DTSPackageLog.WriteStringToLog "El cliente"&CStr(DTSSource("cliente_clave"))&" no esta en la dimension"
cliente = DTSLookups("get_id_cliente").Execute(-1)
End If

cantAnio = DTSLookups("cant_consultas_ao").Execute(cliente, servicio)
If (cantAnio>0) Then
REPETIDA = 1
NUEVA=0
Else
NUEVA = 1
REPETIDA=0
End If

'confirmada, asignada, nopresentada
If StrComp( DTSSource("COD_ESTADO"),DTSLookups("get_cit").Execute())=0 Then
ASIGNADA=1
ElseIf StrComp( DTSSource("COD_ESTADO"),DTSLookups("get_visit").Execute())=0 Then
CONFIRMADA=1
ElseIf StrComp( DTSSource("COD_ESTADO"),DTSLookups("get_np").Execute())=0 Then
NO_REALIZADA=1
End If

If IsNull(DTSSource("TIPO_CEX")) Then
ULTERIOR = 0
PRIMERA_VEZ=0
ElseIf CInt(DTSSource("TIPO_CEX"))=CInt(DTSLookups("get_id_prim_vez").Execute()) Then
PRIMERA_VEZ=1
ElseIf CInt(DTSSource("TIPO_CEX"))=CInt(DTSLookups("get_id_ult").Execute()) Then
ULTERIOR = 1
End If

If IsNull(DTSSource("TIEMPO_ESPERA")) Then
TIEMPO_ESPERA=-1
Else
TIEMPO_ESPERA=DTSSource("TIEMPO_ESPERA")
End If


If DTSLookups("existe_consulta_dia").Execute(cliente, profesional,servicio,fecha) = 0 Then
'NO HAY EN LA BASE UNA CONSULTA PARA ESE CLIENTE EN ESE SERVICIO Y FECHA
DTSDestination("CLIENTE_ID") = cliente
DTSDestination("PROFESIONAL_ID") = profesional

If (DTSSource("edad")<=0) Then
DTSDestination("GR_ETAREO_ID") = DTSLookups("get_id_gr_etareo").Execute(0,0)
Else
If IsEmpty(DTSLookups("get_id_gr_etareo").Execute(CInt(DTSSource("edad")),CInt(DTSSource("edad")))) Then
DTSPackageLog.WriteStringToLog "No sabe calcular para "&CStr(DTSSource("edad"))
Else
DTSDestination("GR_ETAREO_ID") = DTSLookups("get_id_gr_etareo").Execute(CInt(DTSSource("edad")),CInt(DTSSource("edad")))
End If
End If

If IsNull(DTSSource("ICD_COD")) Then
DTSDestination("DIAGNOS_ID") = DTSLookups("get_id_diagnos").Execute(-1)
Else
DTSDestination("DIAGNOS_ID") = DTSLookups("get_id_diagnos").Execute(DTSSource("ICD_COD"))
End If

DTSDestination("SERVICIOS_ID")= servicio
DTSDestination("FECHA_ID") = fecha

If IsNull(DTSSource("MOTIVO_CONS_CLAVE")) Then
DTSDestination("MOTIVO_CONS_ID")=DTSLookups("get_id_motivo_cons").Execute(1)
Else
DTSDestination("MOTIVO_CONS_ID") = DTSLookups("get_id_motivo_cons").Execute(DTSSource("MOTIVO_CONS_CLAVE"))
End If

If IsNull(DTSSource("PROFESIONAL_DERIV_CLAVE")) Then
DTSDestination("PROFESIONAL_DERIVADOR_ID")=DTSLookups("get_id_profesional").Execute(-1)
Else
DTSDestination("PROFESIONAL_DERIVADOR_ID")=DTSLookups("get_id_profesional").Execute(DTSSource("PROFESIONAL_DERIV_CLAVE"))
End If
DTSDestination("TIEMPO_ESPERA") = TIEMPO_ESPERA

DTSDestination("CONSULTAS_CONFIRMADAS") = CONFIRMADA
DTSDestination("CONSULTAS_ASIGNADAS") = ASIGNADA
DTSDestination("CONSULTAS_NO_REALIZADAS")=NO_REALIZADA
DTSDestination("CONSULTAS_PRIMERA_VEZ") = PRIMERA_VEZ
DTSDestination("CONSULTAS_ULTERIORES")=ULTERIOR
DTSDestination("CONSULTAS_NUEVAS")=NUEVA
DTSDestination("CONSULTAS_REPETIDAS")=REPETIDA
DTSDestination("CONSULTAS_PROGRAMADAS")=PROGRAMADA
DTSDestination("CONSULTAS_NO_PROGRAMADAS")=NO_PROGRAMADA
DTSDestination("cant_consult") = 1


msg="Inserto "&CStr(cliente)&" profesional "&CStr(profesional)&" fecha "&CStr(fecha)&" servicio "&CStr(servicio)

'MsgBox(msg)
Main = DTSTransformStat_OK
Else
'HAY EN LA BASE, SE ACTUALIZA
a= DTSLookups("actualiza_valores").Execute(TIEMPO_ESPERA, CONFIRMADA,ASIGNADA,NO_REALIZADA,PROGRAMADA,NO_PRO GRAMADA,PRIMERA_VEZ,ULTERIOR,NUEVA,REPETIDA,CLIENT E,PROFESIONAL,SERVICIO,FECHA)

msg="Actualizo "&CStr(cliente)&" profesional "&CStr(profesional)&" fecha "&CStr(fecha)&" servicio "&CStr(servicio)
'MsgBox(msg)
Main = DTSTransformStat_SkipRow
End If


End FunctionI have just looked through the code and it all looks fine.

I suspect your problem lies elsewhere. Perhaps in the data you are processing or in the target table you are going to.

I'm not sure what it could be though. Perhaps some sort of lock being generated on the table or database. I really don't know.

It's possible that your MDAC installation is corrupted (it can cause some very strange behaviour). If possible try installing MDAC 2.8 and see if that helps. It won't hurt that is for sure.

Sorry I couldn't be of more help.