Need URGENT help.

Hello-
I am trying to restore a large sized database (100+ GB) on a machine that
didn't have database in the beginning, so its a create DB + restore operation.
Below is the machine configuration:
Machine Configuration: quad processor (Pentium 3 Xeon)
Memory: 2 GB
HDD: Fiber Array Channel with separate controllers.
Version: SQL Server 2000/SP3 (Standard Version)
OS: Windows 2003/SP1 (sp presence is a guess).
I understand the process will take some time to create database first before
starting restoration. So far, its been running for over 2 1/2 hours without
any sign of restoration (I have kept STATS=1 for getting any indication of
restoration start).
Now, I have three(3) questions:
1. What should be the approximate DB creation time in such type of
environment? 3-4 hours'
2. How come I know if there is a problem? Currently the restore process has
wait type of ASYNC_IO_COMPLETION with very large wait time.
3. Can upgrading to Enterprise version help parallelize ANY workload (here
db creation/restoration) across all processers? OR this is only true in some
instances say index rebuild?
Thanks in advance!!!
--
Regards,
MZeeshanHi
Fist SQL Server has to create the DB and allocate all the pages for the data
and log. This can take the longest.
Once this is done, the restore can really happen.
A 100GB DB can take anything between 30 minutes and 10 hours to create,
depending on the IO performance of the disk subsystem.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
> Hello-
> I am trying to restore a large sized database (100+ GB) on a machine that
> didn't have database in the beginning, so its a create DB + restore
> operation.
> Below is the machine configuration:
> Machine Configuration: quad processor (Pentium 3 Xeon)
> Memory: 2 GB
> HDD: Fiber Array Channel with separate controllers.
> Version: SQL Server 2000/SP3 (Standard Version)
> OS: Windows 2003/SP1 (sp presence is a guess).
> I understand the process will take some time to create database first
> before
> starting restoration. So far, its been running for over 2 1/2 hours
> without
> any sign of restoration (I have kept STATS=1 for getting any indication of
> restoration start).
> Now, I have three(3) questions:
> 1. What should be the approximate DB creation time in such type of
> environment? 3-4 hours'
> 2. How come I know if there is a problem? Currently the restore process
> has
> wait type of ASYNC_IO_COMPLETION with very large wait time.
> 3. Can upgrading to Enterprise version help parallelize ANY workload (here
> db creation/restoration) across all processers? OR this is only true in
> some
> instances say index rebuild?
> Thanks in advance!!!
> --
> Regards,
> MZeeshan|||Thanks!
Yes, it took just around 5 hours to create the database and currently in
restoration phase.
About my last question: Have you ever noticed any visible improvement in any
system when license is upgraded from Standard to Enterprise?
Database creation, backup/restoration and index rebuilds are some of the
common activities happening on this box.
--
Regards,
MZeeshan
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Fist SQL Server has to create the DB and allocate all the pages for the data
> and log. This can take the longest.
> Once this is done, the restore can really happen.
> A 100GB DB can take anything between 30 minutes and 10 hours to create,
> depending on the IO performance of the disk subsystem.
> Regards
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
> > Hello-
> >
> > I am trying to restore a large sized database (100+ GB) on a machine that
> > didn't have database in the beginning, so its a create DB + restore
> > operation.
> >
> > Below is the machine configuration:
> >
> > Machine Configuration: quad processor (Pentium 3 Xeon)
> > Memory: 2 GB
> > HDD: Fiber Array Channel with separate controllers.
> > Version: SQL Server 2000/SP3 (Standard Version)
> > OS: Windows 2003/SP1 (sp presence is a guess).
> >
> > I understand the process will take some time to create database first
> > before
> > starting restoration. So far, its been running for over 2 1/2 hours
> > without
> > any sign of restoration (I have kept STATS=1 for getting any indication of
> > restoration start).
> >
> > Now, I have three(3) questions:
> >
> > 1. What should be the approximate DB creation time in such type of
> > environment? 3-4 hours'
> >
> > 2. How come I know if there is a problem? Currently the restore process
> > has
> > wait type of ASYNC_IO_COMPLETION with very large wait time.
> >
> > 3. Can upgrading to Enterprise version help parallelize ANY workload (here
> > db creation/restoration) across all processers? OR this is only true in
> > some
> > instances say index rebuild?
> >
> > Thanks in advance!!!
> >
> > --
> > Regards,
> > MZeeshan
>
>|||Those operations generally do not get helped by parallelism due to their IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does more
to help than STD/EE editions upgrade.
Regards--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Fist SQL Server has to create the DB and allocate all the pages for the
>> data
>> and log. This can take the longest.
>> Once this is done, the restore can really happen.
>> A 100GB DB can take anything between 30 minutes and 10 hours to create,
>> depending on the IO performance of the disk subsystem.
>> Regards
>> --
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
>> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
>> > Hello-
>> >
>> > I am trying to restore a large sized database (100+ GB) on a machine
>> > that
>> > didn't have database in the beginning, so its a create DB + restore
>> > operation.
>> >
>> > Below is the machine configuration:
>> >
>> > Machine Configuration: quad processor (Pentium 3 Xeon)
>> > Memory: 2 GB
>> > HDD: Fiber Array Channel with separate controllers.
>> > Version: SQL Server 2000/SP3 (Standard Version)
>> > OS: Windows 2003/SP1 (sp presence is a guess).
>> >
>> > I understand the process will take some time to create database first
>> > before
>> > starting restoration. So far, its been running for over 2 1/2 hours
>> > without
>> > any sign of restoration (I have kept STATS=1 for getting any indication
>> > of
>> > restoration start).
>> >
>> > Now, I have three(3) questions:
>> >
>> > 1. What should be the approximate DB creation time in such type of
>> > environment? 3-4 hours'
>> >
>> > 2. How come I know if there is a problem? Currently the restore process
>> > has
>> > wait type of ASYNC_IO_COMPLETION with very large wait time.
>> >
>> > 3. Can upgrading to Enterprise version help parallelize ANY workload
>> > (here
>> > db creation/restoration) across all processers? OR this is only true in
>> > some
>> > instances say index rebuild?
>> >
>> > Thanks in advance!!!
>> >
>> > --
>> > Regards,
>> > MZeeshan
>>|||This is a multi-part message in MIME format.
--=_NextPart_000_0595_01C56968.94534E90
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Also, not only do you have to wait for all data files and transaction log
files to be created and zeroed out, SQL Server will first run a BACKUP
VERIFICATION and you will first have to wait for SQL Server to read through
the backup file first. The file read itself can take minutes to hours
depending on size, disk throughput, and backup file multiplexing.
If you need fast recovery, you should consider backing up to multiple files
per backup. This will allow you to run parallel read operations for the
verification and actual restore phases; however, the raw database creation
will still be solely dependent on the disk subsystem throughput.
If you also need fast backup times, consider multiple data files per
filegroup. This will allow SQL Server to run parallel operations for the
backup process.
An alternative to these methods would be to use one of several 3rd-party
backup tools. Lightspeed by Imceda would be a good candidate.
Sincerely,
Anthony Thomas
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eVKp0GXaFHA.2996@.TK2MSFTNGP10.phx.gbl...
Those operations generally do not get helped by parallelism due to their
IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does
more
to help than STD/EE editions upgrade.
Regards--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...
> Thanks!
>
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
>
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
>
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
>
> --
> Regards,
> MZeeshan
>
>
> "Mike Epprecht (SQL MVP)" wrote:
>
>> Hi
>>
>> Fist SQL Server has to create the DB and allocate all the pages for the
>> data
>> and log. This can take the longest.
>>
>> Once this is done, the restore can really happen.
>>
>> A 100GB DB can take anything between 30 minutes and 10 hours to create,
>> depending on the IO performance of the disk subsystem.
>>
>> Regards
>> --
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>>
>> IM: mike@.epprecht.net
>>
>> MVP Program: http://www.microsoft.com/mvp
>>
>> Blog: http://www.msmvps.com/epprecht/
>>
>> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
>> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
>> > Hello-
>> >
>> > I am trying to restore a large sized database (100+ GB) on a machine
>> > that
>> > didn't have database in the beginning, so its a create DB + restore
>> > operation.
>> >
>> > Below is the machine configuration:
>> >
>> > Machine Configuration: quad processor (Pentium 3 Xeon)
>> > Memory: 2 GB
>> > HDD: Fiber Array Channel with separate controllers.
>> > Version: SQL Server 2000/SP3 (Standard Version)
>> > OS: Windows 2003/SP1 (sp presence is a guess).
>> >
>> > I understand the process will take some time to create database first
>> > before
>> > starting restoration. So far, its been running for over 2 1/2 hours
>> > without
>> > any sign of restoration (I have kept STATS=1 for getting any
indication
>> > of
>> > restoration start).
>> >
>> > Now, I have three(3) questions:
>> >
>> > 1. What should be the approximate DB creation time in such type of
>> > environment? 3-4 hours'
>> >
>> > 2. How come I know if there is a problem? Currently the restore
process
>> > has
>> > wait type of ASYNC_IO_COMPLETION with very large wait time.
>> >
>> > 3. Can upgrading to Enterprise version help parallelize ANY workload
>> > (here
>> > db creation/restoration) across all processers? OR this is only true
in
>> > some
>> > instances say index rebuild?
>> >
>> > Thanks in advance!!!
>> >
>> > --
>> > Regards,
>> > MZeeshan
>>
>>
>>
--=_NextPart_000_0595_01C56968.94534E90
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Also, not only do you have to wait =for all data files and transaction log files to be created and zeroed out, SQL Server =will first run a BACKUP VERIFICATION and you will first have to wait for SQL =Server to read through the backup file first. The file read itself can =take minutes to hours depending on size, disk throughput, and =backup file multiplexing.
If you need fast recovery, you should =consider backing up to multiple files per backup. This will allow you to =run parallel read operations for the verification and actual restore phases; =however, the raw database creation will still be solely dependent on the =disk subsystem throughput.
If you also need fast backup times, =consider multiple data files per filegroup. This will allow SQL Server to =run parallel operations for the backup process.
An alternative to these methods would =be to use one of several 3rd-party backup tools. Lightspeed by Imceda =would be a good candidate.
Sincerely,
Anthony Thomas
--
"Mike Epprecht (SQL MVP)" wrote in =message news:eVKp0GXaFHA.2996=@.TK2MSFTNGP10.phx.gbl...Those operations generally do not get helped by parallelism due to their IO loads.Getting the fastest disk subsystem, and configuring =it correctly, does more to help than STD/EE editions upgrade.Regards-- --Mike = Epprecht, Microsoft SQL Server MVPZurich, SwitzerlandIM: =mike@.epprecht.netMVP =Program: http://www.microsoft.com/mvpBlog: http://www.msmvps.com/epprecht/<=/A>"MZeeshan" Thanks!>> Yes, it took just around 5 hours to create the = database and currently in> restoration phase.>> =About my last question: Have you ever noticed any visible improvement in => any> system when license is upgraded from Standard to Enterprise?>> Database creation, backup/restoration and =index rebuilds are some of the> common activities happening on this box.>> -- > Regards,> MZeeshan>>> "Mike Epprecht (SQL MVP)" wrote:>> Hi>> Fist SQL Server =has to create the DB and allocate all the pages for the > =data> and log. This can take the longest.>> Once this =is done, the restore can really happen.>> A 100GB DB can =take anything between 30 minutes and 10 hours to create,> =depending on the IO performance of the disk subsystem.>> Regards> -- > --> Mike Epprecht, Microsoft =SQL Server MVP> Zurich, Switzerland>> IM: =mike@.epprecht.net>&g=t;> MVP Program: http://www.microsoft.com/mvp>> Blog: http://www.msmvps.com/epprecht/<=/A>>> "MZeeshan" news:188=596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...> > Hello-> >> > I am trying to restore a =large sized database (100+ GB) on a machine > > =that> > didn't have database in the beginning, so its a create DB + restore> > operation.> >> > =Below is the machine configuration:> >> > Machine Configuration: quad processor (Pentium 3 Xeon)> > =Memory: 2 GB> > HDD: Fiber Array Channel with separate controllers.> > Version: SQL Server 2000/SP3 (Standard Version)> > OS: Windows 2003/SP1 (sp presence is a guess).> >> > I understand the process =will take some time to create database first> > before> => starting restoration. So far, its been running for over 2 1/2 hours> > without> > any sign of =restoration (I have kept STATS=3D1 for getting any indication > > =of> > restoration start).> >> > Now, I have = three(3) questions:> >> > 1. What should =be the approximate DB creation time in such type of> > =environment? 3-4 hours'> >> > 2. How come I know if there =is a problem? Currently the restore process> > =has> > wait type of ASYNC_IO_COMPLETION with very large wait =time.> >> > 3. Can upgrading to Enterprise version help =parallelize ANY workload > > (here> > db =creation/restoration) across all processers? OR this is only true in> > some> > instances say index rebuild?> >> > Thanks in advance!!!> =>> > -- > > Regards,> > MZeeshan>>> =

---

--=_NextPart_000_0595_01C56968.94534E90--|||This is a multi-part message in MIME format.
--=_NextPart_000_0024_01C569A2.12FAEC60
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
ANthonys suggestion of using striped backups to improve restore speed is =a great idea...
And in response to your third question, you will not see an difference =in backup/restore speeds if you upgrade to Enterprise Edition.
-- Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message =news:eSs5dLZaFHA.3184@.TK2MSFTNGP15.phx.gbl...
Also, not only do you have to wait for all data files and transaction =log files to be created and zeroed out, SQL Server will first run a =BACKUP VERIFICATION and you will first have to wait for SQL Server to =read through the backup file first. The file read itself can take =minutes to hours depending on size, disk throughput, and backup file =multiplexing.
If you need fast recovery, you should consider backing up to multiple =files per backup. This will allow you to run parallel read operations =for the verification and actual restore phases; however, the raw =database creation will still be solely dependent on the disk subsystem =throughput.
If you also need fast backup times, consider multiple data files per =filegroup. This will allow SQL Server to run parallel operations for =the backup process.
An alternative to these methods would be to use one of several =3rd-party backup tools. Lightspeed by Imceda would be a good candidate.
Sincerely,
Anthony Thomas
-- "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message =news:eVKp0GXaFHA.2996@.TK2MSFTNGP10.phx.gbl...
Those operations generally do not get helped by parallelism due to =their IO loads.
Getting the fastest disk subsystem, and configuring it correctly, =does more to help than STD/EE editions upgrade.
Regards-- --
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...
> Thanks!
>
> Yes, it took just around 5 hours to create the database and =currently in
> restoration phase.
>
> About my last question: Have you ever noticed any visible =improvement in > any
> system when license is upgraded from Standard to Enterprise?
>
> Database creation, backup/restoration and index rebuilds are some =of the
> common activities happening on this box.
>
> -- > Regards,
> MZeeshan
>
>
> "Mike Epprecht (SQL MVP)" wrote:
>
>> Hi
>>
>> Fist SQL Server has to create the DB and allocate all the pages =for the >> data
>> and log. This can take the longest.
>>
>> Once this is done, the restore can really happen.
>>
>> A 100GB DB can take anything between 30 minutes and 10 hours to =create,
>> depending on the IO performance of the disk subsystem.
>>
>> Regards
>> -- >> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>>
>> IM: mike@.epprecht.net
>>
>> MVP Program: http://www.microsoft.com/mvp
>>
>> Blog: http://www.msmvps.com/epprecht/
>>
>> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
>> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
>> > Hello-
>> >
>> > I am trying to restore a large sized database (100+ GB) on a =machine >> > that
>> > didn't have database in the beginning, so its a create DB + =restore
>> > operation.
>> >
>> > Below is the machine configuration:
>> >
>> > Machine Configuration: quad processor (Pentium 3 Xeon)
>> > Memory: 2 GB
>> > HDD: Fiber Array Channel with separate controllers.
>> > Version: SQL Server 2000/SP3 (Standard Version)
>> > OS: Windows 2003/SP1 (sp presence is a guess).
>> >
>> > I understand the process will take some time to create database =first
>> > before
>> > starting restoration. So far, its been running for over 2 1/2 =hours
>> > without
>> > any sign of restoration (I have kept STATS=3D1 for getting any =indication >> > of
>> > restoration start).
>> >
>> > Now, I have three(3) questions:
>> >
>> > 1. What should be the approximate DB creation time in such type =of
>> > environment? 3-4 hours'
>> >
>> > 2. How come I know if there is a problem? Currently the restore =process
>> > has
>> > wait type of ASYNC_IO_COMPLETION with very large wait time.
>> >
>> > 3. Can upgrading to Enterprise version help parallelize ANY =workload >> > (here
>> > db creation/restoration) across all processers? OR this is only =true in
>> > some
>> > instances say index rebuild?
>> >
>> > Thanks in advance!!!
>> >
>> > -- >> > Regards,
>> > MZeeshan
>>
>>
>>
--=_NextPart_000_0024_01C569A2.12FAEC60
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

ANthonys suggestion of using striped =backups to improve restore speed is a great idea...
And in response to your third question, =you will not see an difference in backup/restore speeds if you upgrade to =Enterprise Edition.
-- Wayne Snyder, MCDBA, SQL Server MVPMariner, =Charlotte, NChttp://www.mariner-usa.com">www.mariner-usa.com(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it'scommunity of SQL Server professionals.http://www.sqlpass.org">www.sqlpass.org
"Anthony Thomas" wrote in =message news:eSs5dLZaFHA.3184=@.TK2MSFTNGP15.phx.gbl...
Also, not only do you have to wait =for all data files and transaction log files to be created and zeroed out, SQL =Server will first run a BACKUP VERIFICATION and you will first have to wait for =SQL Server to read through the backup file first. The file read itself can =take minutes to hours depending on size, disk throughput, and =backup file multiplexing.

If you need fast recovery, you =should consider backing up to multiple files per backup. This will allow you to =run parallel read operations for the verification and actual restore =phases; however, the raw database creation will still be solely dependent on =the disk subsystem throughput.

If you also need fast backup times, =consider multiple data files per filegroup. This will allow SQL Server to =run parallel operations for the backup process.

An alternative to these methods =would be to use one of several 3rd-party backup tools. Lightspeed =by Imceda would be a good candidate.

Sincerely,


Anthony Thomas

--
"Mike Epprecht (SQL MVP)" wrote in =message news:eVKp0GXaFHA.2996=@.TK2MSFTNGP10.phx.gbl...Those operations generally do not get helped by parallelism due to their =IO loads.Getting the fastest disk subsystem, and =configuring it correctly, does more to help than STD/EE editions upgrade.Regards-- =--Mike Epprecht, Microsoft SQL Server MVPZurich, SwitzerlandIM: =mike@.epprecht.netMVP =Program: http://www.microsoft.com/mvpBlog: http://www.msmvps.com/epprecht/<=/A>"MZeeshan" Thanks!>> Yes, it took just around 5 hours to create =the database and currently in> restoration phase.>> =About my last question: Have you ever noticed any visible improvement in => any> system when license is upgraded from Standard to Enterprise?>> Database creation, backup/restoration =and index rebuilds are some of the> common activities happening on this = box.>> -- > Regards,> MZeeshan>>> "Mike Epprecht (SQL MVP)" wrote:>> Hi>> Fist SQL =Server has to create the DB and allocate all the pages for the > data> and log. This can take the =longest.>> Once this is done, the restore can really =happen.>> A 100GB DB can take anything between 30 minutes and 10 hours to create,> depending on the IO performance of the disk subsystem.>> Regards> -- => --> Mike Epprecht, =Microsoft SQL Server MVP> Zurich, Switzerland>> =IM: mike@.epprecht.net>&g=t;> MVP Program: http://www.microsoft.com/mvp>> Blog: http://www.msmvps.com/epprecht/<=/A>>> "MZeeshan" news:188=596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...> > Hello-> >> > I am trying to restore =a large sized database (100+ GB) on a machine > > =that> > didn't have database in the beginning, so its a create DB + restore> > operation.> >> => Below is the machine configuration:> >> > =Machine Configuration: quad processor (Pentium 3 Xeon)> > =Memory: 2 GB> > HDD: Fiber Array Channel with separate controllers.> > Version: SQL Server 2000/SP3 (Standard = Version)> > OS: Windows 2003/SP1 (sp presence is a guess).> >> > I understand the process =will take some time to create database first> > =before> > starting restoration. So far, its been running for over 2 1/2 hours> > without> > any sign of =restoration (I have kept STATS=3D1 for getting any indication > > of> > restoration start).> >> => Now, I have three(3) questions:> >> > 1. =What should be the approximate DB creation time in such type =of> > environment? 3-4 hours'> >> > 2. How =come I know if there is a problem? Currently the restore =process> > has> > wait type of ASYNC_IO_COMPLETION with very =large wait time.> >> > 3. Can upgrading to =Enterprise version help parallelize ANY workload > > =(here> > db creation/restoration) across all processers? OR this is only =true in> > some> > instances say index rebuild?> >> > Thanks in advance!!!> >> > -- > > Regards,> > =MZeeshan>>>

---

--=_NextPart_000_0024_01C569A2.12FAEC60--

Need URGENT help.

Hello-
I am trying to restore a large sized database (100+ GB) on a machine that
didn't have database in the beginning, so its a create DB + restore operatio
n.
Below is the machine configuration:
Machine Configuration: quad processor (Pentium 3 Xeon)
Memory: 2 GB
HDD: Fiber Array Channel with separate controllers.
Version: SQL Server 2000/SP3 (Standard Version)
OS: Windows 2003/SP1 (sp presence is a guess).
I understand the process will take some time to create database first before
starting restoration. So far, its been running for over 2 1/2 hours without
any sign of restoration (I have kept STATS=1 for getting any indication of
restoration start).
Now, I have three(3) questions:
1. What should be the approximate DB creation time in such type of
environment? 3-4 hours'
2. How come I know if there is a problem? Currently the restore process has
wait type of ASYNC_IO_COMPLETION with very large wait time.
3. Can upgrading to Enterprise version help parallelize ANY workload (here
db creation/restoration) across all processers? OR this is only true in some
instances say index rebuild?
Thanks in advance!!!
Regards,
MZeeshanHi
Fist SQL Server has to create the DB and allocate all the pages for the data
and log. This can take the longest.
Once this is done, the restore can really happen.
A 100GB DB can take anything between 30 minutes and 10 hours to create,
depending on the IO performance of the disk subsystem.
Regards
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
> Hello-
> I am trying to restore a large sized database (100+ GB) on a machine that
> didn't have database in the beginning, so its a create DB + restore
> operation.
> Below is the machine configuration:
> Machine Configuration: quad processor (Pentium 3 Xeon)
> Memory: 2 GB
> HDD: Fiber Array Channel with separate controllers.
> Version: SQL Server 2000/SP3 (Standard Version)
> OS: Windows 2003/SP1 (sp presence is a guess).
> I understand the process will take some time to create database first
> before
> starting restoration. So far, its been running for over 2 1/2 hours
> without
> any sign of restoration (I have kept STATS=1 for getting any indication of
> restoration start).
> Now, I have three(3) questions:
> 1. What should be the approximate DB creation time in such type of
> environment? 3-4 hours'
> 2. How come I know if there is a problem? Currently the restore process
> has
> wait type of ASYNC_IO_COMPLETION with very large wait time.
> 3. Can upgrading to Enterprise version help parallelize ANY workload (here
> db creation/restoration) across all processers? OR this is only true in
> some
> instances say index rebuild?
> Thanks in advance!!!
> --
> Regards,
> MZeeshan|||Thanks!
Yes, it took just around 5 hours to create the database and currently in
restoration phase.
About my last question: Have you ever noticed any visible improvement in any
system when license is upgraded from Standard to Enterprise?
Database creation, backup/restoration and index rebuilds are some of the
common activities happening on this box.
Regards,
MZeeshan
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Fist SQL Server has to create the DB and allocate all the pages for the da
ta
> and log. This can take the longest.
> Once this is done, the restore can really happen.
> A 100GB DB can take anything between 30 minutes and 10 hours to create,
> depending on the IO performance of the disk subsystem.
> Regards
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
>
>|||Those operations generally do not get helped by parallelism due to their IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does more
to help than STD/EE editions upgrade.
Regards--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...[vbcol=seagreen]
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
>|||Also, not only do you have to wait for all data files and transaction log
files to be created and zeroed out, SQL Server will first run a BACKUP
VERIFICATION and you will first have to wait for SQL Server to read through
the backup file first. The file read itself can take minutes to hours
depending on size, disk throughput, and backup file multiplexing.
If you need fast recovery, you should consider backing up to multiple files
per backup. This will allow you to run parallel read operations for the
verification and actual restore phases; however, the raw database creation
will still be solely dependent on the disk subsystem throughput.
If you also need fast backup times, consider multiple data files per
filegroup. This will allow SQL Server to run parallel operations for the
backup process.
An alternative to these methods would be to use one of several 3rd-party
backup tools. Lightspeed by Imceda would be a good candidate.
Sincerely,
Anthony Thomas
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eVKp0GXaFHA.2996@.TK2MSFTNGP10.phx.gbl...
Those operations generally do not get helped by parallelism due to their
IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does
more
to help than STD/EE editions upgrade.
Regards--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...[vbcol=seagreen]
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
>
indication[vbcol=seagreen]
process[vbcol=seagreen]
in[vbcol=seagreen]|||ANthonys suggestion of using striped backups to improve restore speed is a g
reat idea...
And in response to your third question, you will not see an difference in ba
ckup/restore speeds if you upgrade to Enterprise Edition.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message news:eSs5dLZaFHA.3184
@.TK2MSFTNGP15.phx.gbl...
Also, not only do you have to wait for all data files and transaction log fi
les to be created and zeroed out, SQL Server will first run a BACKUP VERIFIC
ATION and you will first have to wait for SQL Server to read through the bac
kup file first. The file read itself can take minutes to hours depending on
size, disk throughput, and backup file multiplexing.
If you need fast recovery, you should consider backing up to multiple files
per backup. This will allow you to run parallel read operations for the ver
ification and actual restore phases; however, the raw database creation will
still be solely dependent on the disk subsystem throughput.
If you also need fast backup times, consider multiple data files per filegro
up. This will allow SQL Server to run parallel operations for the backup pr
ocess.
An alternative to these methods would be to use one of several 3rd-party bac
kup tools. Lightspeed by Imceda would be a good candidate.
Sincerely,
Anthony Thomas
--
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message news:eVKp0GXa
FHA.2996@.TK2MSFTNGP10.phx.gbl...
Those operations generally do not get helped by parallelism due to their IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does more
to help than STD/EE editions upgrade.
Regards--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...[vbcol=seagreen]
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
>

Need URGENT help.

Hello-
I am trying to restore a large sized database (100+ GB) on a machine that
didn't have database in the beginning, so its a create DB + restore operation.
Below is the machine configuration:
Machine Configuration: quad processor (Pentium 3 Xeon)
Memory: 2 GB
HDD: Fiber Array Channel with separate controllers.
Version: SQL Server 2000/SP3 (Standard Version)
OS: Windows 2003/SP1 (sp presence is a guess).
I understand the process will take some time to create database first before
starting restoration. So far, its been running for over 2 1/2 hours without
any sign of restoration (I have kept STATS=1 for getting any indication of
restoration start).
Now, I have three(3) questions:
1. What should be the approximate DB creation time in such type of
environment? 3-4 hours?
2. How come I know if there is a problem? Currently the restore process has
wait type of ASYNC_IO_COMPLETION with very large wait time.
3. Can upgrading to Enterprise version help parallelize ANY workload (here
db creation/restoration) across all processers? OR this is only true in some
instances say index rebuild?
Thanks in advance!!!
Regards,
MZeeshan
Hi
Fist SQL Server has to create the DB and allocate all the pages for the data
and log. This can take the longest.
Once this is done, the restore can really happen.
A 100GB DB can take anything between 30 minutes and 10 hours to create,
depending on the IO performance of the disk subsystem.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
> Hello-
> I am trying to restore a large sized database (100+ GB) on a machine that
> didn't have database in the beginning, so its a create DB + restore
> operation.
> Below is the machine configuration:
> Machine Configuration: quad processor (Pentium 3 Xeon)
> Memory: 2 GB
> HDD: Fiber Array Channel with separate controllers.
> Version: SQL Server 2000/SP3 (Standard Version)
> OS: Windows 2003/SP1 (sp presence is a guess).
> I understand the process will take some time to create database first
> before
> starting restoration. So far, its been running for over 2 1/2 hours
> without
> any sign of restoration (I have kept STATS=1 for getting any indication of
> restoration start).
> Now, I have three(3) questions:
> 1. What should be the approximate DB creation time in such type of
> environment? 3-4 hours?
> 2. How come I know if there is a problem? Currently the restore process
> has
> wait type of ASYNC_IO_COMPLETION with very large wait time.
> 3. Can upgrading to Enterprise version help parallelize ANY workload (here
> db creation/restoration) across all processers? OR this is only true in
> some
> instances say index rebuild?
> Thanks in advance!!!
> --
> Regards,
> MZeeshan
|||Thanks!
Yes, it took just around 5 hours to create the database and currently in
restoration phase.
About my last question: Have you ever noticed any visible improvement in any
system when license is upgraded from Standard to Enterprise?
Database creation, backup/restoration and index rebuilds are some of the
common activities happening on this box.
Regards,
MZeeshan
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Fist SQL Server has to create the DB and allocate all the pages for the data
> and log. This can take the longest.
> Once this is done, the restore can really happen.
> A 100GB DB can take anything between 30 minutes and 10 hours to create,
> depending on the IO performance of the disk subsystem.
> Regards
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "MZeeshan" <mzeeshan@.community.nospam> wrote in message
> news:188596EB-FD62-48DA-94D7-8922B40770E6@.microsoft.com...
>
>
|||Those operations generally do not get helped by parallelism due to their IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does more
to help than STD/EE editions upgrade.
Regards--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...[vbcol=seagreen]
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
|||Also, not only do you have to wait for all data files and transaction log
files to be created and zeroed out, SQL Server will first run a BACKUP
VERIFICATION and you will first have to wait for SQL Server to read through
the backup file first. The file read itself can take minutes to hours
depending on size, disk throughput, and backup file multiplexing.
If you need fast recovery, you should consider backing up to multiple files
per backup. This will allow you to run parallel read operations for the
verification and actual restore phases; however, the raw database creation
will still be solely dependent on the disk subsystem throughput.
If you also need fast backup times, consider multiple data files per
filegroup. This will allow SQL Server to run parallel operations for the
backup process.
An alternative to these methods would be to use one of several 3rd-party
backup tools. Lightspeed by Imceda would be a good candidate.
Sincerely,
Anthony Thomas

"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:eVKp0GXaFHA.2996@.TK2MSFTNGP10.phx.gbl...
Those operations generally do not get helped by parallelism due to their
IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does
more
to help than STD/EE editions upgrade.
Regards--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...[vbcol=seagreen]
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:
indication[vbcol=seagreen]
process[vbcol=seagreen]
in[vbcol=seagreen]
|||ANthonys suggestion of using striped backups to improve restore speed is a great idea...
And in response to your third question, you will not see an difference in backup/restore speeds if you upgrade to Enterprise Edition.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message news:eSs5dLZaFHA.3184@.TK2MSFTNGP15.phx.gbl...
Also, not only do you have to wait for all data files and transaction log files to be created and zeroed out, SQL Server will first run a BACKUP VERIFICATION and you will first have to wait for SQL Server to read through the backup file first. The file read itself can take minutes to hours depending on size, disk throughput, and backup file multiplexing.
If you need fast recovery, you should consider backing up to multiple files per backup. This will allow you to run parallel read operations for the verification and actual restore phases; however, the raw database creation will still be solely dependent on the disk subsystem throughput.
If you also need fast backup times, consider multiple data files per filegroup. This will allow SQL Server to run parallel operations for the backup process.
An alternative to these methods would be to use one of several 3rd-party backup tools. Lightspeed by Imceda would be a good candidate.
Sincerely,
Anthony Thomas

"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message news:eVKp0GXaFHA.2996@.TK2MSFTNGP10.phx.gbl...
Those operations generally do not get helped by parallelism due to their IO
loads.
Getting the fastest disk subsystem, and configuring it correctly, does more
to help than STD/EE editions upgrade.
Regards--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:63CEA9B1-B957-4CBE-97A4-726B6AD0E3FF@.microsoft.com...[vbcol=seagreen]
> Thanks!
> Yes, it took just around 5 hours to create the database and currently in
> restoration phase.
> About my last question: Have you ever noticed any visible improvement in
> any
> system when license is upgraded from Standard to Enterprise?
> Database creation, backup/restoration and index rebuilds are some of the
> common activities happening on this box.
> --
> Regards,
> MZeeshan
>
> "Mike Epprecht (SQL MVP)" wrote:

Need urgent help with a db restore

using sql server 2000.
we have a table (that used to have 2.7 million records) which now has none.
We did a full db backup monday night at 7pm, did the weekly trans log backup
monday at 7:30pm.
The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
lost (using Export data) by trying to move a stored procedure (as an object)
from the development server to the production server. The process somehow
cleared out the table (in production) the stored procedure uses.
I know I can recover the table (actually the who db) via the 7pm full
database backup and the 7:30 transaction log backup, but how do I recover the
data that was entered after the 7:30 trans log backup up to 4:40pm the next
day (Tuesday)?
Thanks for any and all help.
Russ...add-on to the first message.
There was also a tlog backup done Tuesday night at 7:30pm
thanks
russ...
"Russ" wrote:
> using sql server 2000.
> we have a table (that used to have 2.7 million records) which now has none.
> We did a full db backup monday night at 7pm, did the weekly trans log backup
> monday at 7:30pm.
> The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
> lost (using Export data) by trying to move a stored procedure (as an object)
> from the development server to the production server. The process somehow
> cleared out the table (in production) the stored procedure uses.
> I know I can recover the table (actually the who db) via the 7pm full
> database backup and the 7:30 transaction log backup, but how do I recover the
> data that was entered after the 7:30 trans log backup up to 4:40pm the next
> day (Tuesday)?
> Thanks for any and all help.
> Russ...|||Perform a log backup now and restore the latest db backup, and all subsequent tlog backups. For the
very last one, specify a STOPAT value which is just before the accident. More info and options:
http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Russ" <Russ@.discussions.microsoft.com> wrote in message
news:2038FEBB-D723-4797-8BC4-9319299A1809@.microsoft.com...
> using sql server 2000.
> we have a table (that used to have 2.7 million records) which now has none.
> We did a full db backup monday night at 7pm, did the weekly trans log backup
> monday at 7:30pm.
> The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
> lost (using Export data) by trying to move a stored procedure (as an object)
> from the development server to the production server. The process somehow
> cleared out the table (in production) the stored procedure uses.
> I know I can recover the table (actually the who db) via the 7pm full
> database backup and the 7:30 transaction log backup, but how do I recover the
> data that was entered after the 7:30 trans log backup up to 4:40pm the next
> day (Tuesday)?
> Thanks for any and all help.
> Russ...

Need urgent help with a db restore

using sql server 2000.
we have a table (that used to have 2.7 million records) which now has none.
We did a full db backup monday night at 7pm, did the weekly trans log backup
monday at 7:30pm.
The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
lost (using Export data) by trying to move a stored procedure (as an object)
from the development server to the production server. The process somehow
cleared out the table (in production) the stored procedure uses.
I know I can recover the table (actually the who db) via the 7pm full
database backup and the 7:30 transaction log backup, but how do I recover th
e
data that was entered after the 7:30 trans log backup up to 4:40pm the next
day (Tuesday)?
Thanks for any and all help.
Russ...add-on to the first message.
There was also a tlog backup done Tuesday night at 7:30pm
thanks
russ...
"Russ" wrote:

> using sql server 2000.
> we have a table (that used to have 2.7 million records) which now has none
.
> We did a full db backup monday night at 7pm, did the weekly trans log back
up
> monday at 7:30pm.
> The data from the table (Dailylog) was lost at 4:42pm tuesday. The data w
as
> lost (using Export data) by trying to move a stored procedure (as an objec
t)
> from the development server to the production server. The process somehow
> cleared out the table (in production) the stored procedure uses.
> I know I can recover the table (actually the who db) via the 7pm full
> database backup and the 7:30 transaction log backup, but how do I recover
the
> data that was entered after the 7:30 trans log backup up to 4:40pm the nex
t
> day (Tuesday)?
> Thanks for any and all help.
> Russ...|||Perform a log backup now and restore the latest db backup, and all subsequen
t tlog backups. For the
very last one, specify a STOPAT value which is just before the accident. Mor
e info and options:
http://www.karaszi.com/SQLServer/in...veral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Russ" <Russ@.discussions.microsoft.com> wrote in message
news:2038FEBB-D723-4797-8BC4-9319299A1809@.microsoft.com...
> using sql server 2000.
> we have a table (that used to have 2.7 million records) which now has none
.
> We did a full db backup monday night at 7pm, did the weekly trans log back
up
> monday at 7:30pm.
> The data from the table (Dailylog) was lost at 4:42pm tuesday. The data w
as
> lost (using Export data) by trying to move a stored procedure (as an objec
t)
> from the development server to the production server. The process somehow
> cleared out the table (in production) the stored procedure uses.
> I know I can recover the table (actually the who db) via the 7pm full
> database backup and the 7:30 transaction log backup, but how do I recover
the
> data that was entered after the 7:30 trans log backup up to 4:40pm the nex
t
> day (Tuesday)?
> Thanks for any and all help.
> Russ...

Need urgent help with a db restore

using sql server 2000.
we have a table (that used to have 2.7 million records) which now has none.
We did a full db backup monday night at 7pm, did the weekly trans log backup
monday at 7:30pm.
The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
lost (using Export data) by trying to move a stored procedure (as an object)
from the development server to the production server. The process somehow
cleared out the table (in production) the stored procedure uses.
I know I can recover the table (actually the who db) via the 7pm full
database backup and the 7:30 transaction log backup, but how do I recover the
data that was entered after the 7:30 trans log backup up to 4:40pm the next
day (Tuesday)?
Thanks for any and all help.
Russ...
add-on to the first message.
There was also a tlog backup done Tuesday night at 7:30pm
thanks
russ...
"Russ" wrote:

> using sql server 2000.
> we have a table (that used to have 2.7 million records) which now has none.
> We did a full db backup monday night at 7pm, did the weekly trans log backup
> monday at 7:30pm.
> The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
> lost (using Export data) by trying to move a stored procedure (as an object)
> from the development server to the production server. The process somehow
> cleared out the table (in production) the stored procedure uses.
> I know I can recover the table (actually the who db) via the 7pm full
> database backup and the 7:30 transaction log backup, but how do I recover the
> data that was entered after the 7:30 trans log backup up to 4:40pm the next
> day (Tuesday)?
> Thanks for any and all help.
> Russ...
|||Perform a log backup now and restore the latest db backup, and all subsequent tlog backups. For the
very last one, specify a STOPAT value which is just before the accident. More info and options:
http://www.karaszi.com/SQLServer/inf...eral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Russ" <Russ@.discussions.microsoft.com> wrote in message
news:2038FEBB-D723-4797-8BC4-9319299A1809@.microsoft.com...
> using sql server 2000.
> we have a table (that used to have 2.7 million records) which now has none.
> We did a full db backup monday night at 7pm, did the weekly trans log backup
> monday at 7:30pm.
> The data from the table (Dailylog) was lost at 4:42pm tuesday. The data was
> lost (using Export data) by trying to move a stored procedure (as an object)
> from the development server to the production server. The process somehow
> cleared out the table (in production) the stored procedure uses.
> I know I can recover the table (actually the who db) via the 7pm full
> database backup and the 7:30 transaction log backup, but how do I recover the
> data that was entered after the 7:30 trans log backup up to 4:40pm the next
> day (Tuesday)?
> Thanks for any and all help.
> Russ...

Need to use "USE" in a stored Proc

I have a stored Procedure that I always use to restore databases.It
accepts the backup file name,name of the database to be restored as
amongst other parameters. It works pretty good and helps me speed up
the process. However after the database is restored, I need to connect
to that database and compile another stored procedure on that database
and then execute that stored procedure to drop all the users from that
database. I then execute another stored procedure to add the new users
and apply appropriate security.
What I want to do now is to see if there is a way to combine all those
steps all into one stored procedure that will restore the database and
then drop all the users from that database and then add the new users.
When I first attempted it I ran into the issue where I could not use
the "USE " statement in the stored proc, so I ended up writing two
different procs one for dropping users and one for adding new users
that I compile and execute after the database has been restored.
I am sure other people have run into this situation too where within a
stored proc, it is necessary to switch database and perform other tasks
in a different database. I am just looking to get some input to see how
you overcame or handled that issue.
Any help inthis regard will be greatly appreciated.
Thanks
You will need to build dynamic sql script, builiding a string with the
first word 'use dbname', and then execute the string.
Terry
shub wrote:
> I have a stored Procedure that I always use to restore databases.It
> accepts the backup file name,name of the database to be restored as
> amongst other parameters. It works pretty good and helps me speed up
> the process. However after the database is restored, I need to connect
> to that database and compile another stored procedure on that database
> and then execute that stored procedure to drop all the users from that
> database. I then execute another stored procedure to add the new users
> and apply appropriate security.
> What I want to do now is to see if there is a way to combine all those
> steps all into one stored procedure that will restore the database and
> then drop all the users from that database and then add the new users.
> When I first attempted it I ran into the issue where I could not use
> the "USE " statement in the stored proc, so I ended up writing two
> different procs one for dropping users and one for adding new users
> that I compile and execute after the database has been restored.
> I am sure other people have run into this situation too where within a
> stored proc, it is necessary to switch database and perform other tasks
> in a different database. I am just looking to get some input to see how
> you overcame or handled that issue.
> Any help inthis regard will be greatly appreciated.
> Thanks
|||Consider 3-part naming like database.owner.tablename format rather than USE
statement.
Anith
|||Hi
Why do you need to drop all the users? If you have orphaned users use
sp_change_users_login
John
"shub" wrote:

> I have a stored Procedure that I always use to restore databases.It
> accepts the backup file name,name of the database to be restored as
> amongst other parameters. It works pretty good and helps me speed up
> the process. However after the database is restored, I need to connect
> to that database and compile another stored procedure on that database
> and then execute that stored procedure to drop all the users from that
> database. I then execute another stored procedure to add the new users
> and apply appropriate security.
> What I want to do now is to see if there is a way to combine all those
> steps all into one stored procedure that will restore the database and
> then drop all the users from that database and then add the new users.
> When I first attempted it I ran into the issue where I could not use
> the "USE " statement in the stored proc, so I ended up writing two
> different procs one for dropping users and one for adding new users
> that I compile and execute after the database has been restored.
> I am sure other people have run into this situation too where within a
> stored proc, it is necessary to switch database and perform other tasks
> in a different database. I am just looking to get some input to see how
> you overcame or handled that issue.
> Any help inthis regard will be greatly appreciated.
> Thanks
>
|||>> Since the database name changes, that would require dynamic SQL.
That should be fine, if written properly, right? Since it is a
administrative task, it has little to do with injection risks or
recompilation issues.
Otherwise, he can consider a few alternatives:
1. Create individual RESTORE statements within the proc & use IF clause to
control the execution flow
2. Create separate stored procedures in each database & call it from the
main proc with the database as the parameter.
EXEC @.r = @.db.dbo.restore_proc ;
Anith
|||Anith,
I am not sure I understand what are you suggesting. You are right in
assuming that this is a administrative task and there is no concern or
SQL injection however I do not uderstand the solution.
Currently the Restore Stored proc is complied in the master database
and after the individual database is restored I am then compilibg the
other stored proc to drop all the users.
You may be into something, but I just need to understand to apply it
Thanks
Anith Sen wrote:
> That should be fine, if written properly, right? Since it is a
> administrative task, it has little to do with injection risks or
> recompilation issues.
> Otherwise, he can consider a few alternatives:
> 1. Create individual RESTORE statements within the proc & use IF clause to
> control the execution flow
> 2. Create separate stored procedures in each database & call it from the
> main proc with the database as the parameter.
> EXEC @.r = @.db.dbo.restore_proc ;
> --
> Anith
|||Hi
I believe Anith is saying that as this is being used in a controlled manner,
there is no need to worry about SQL Injection as there is no input from a
non-trusted user being executed.
If your database contains it own procedure to drop/create the users then you
can just run that, although anyone else with enough rights can run this
procedure as well.
Why do you need to drop/re-create the users?
John
"shub" wrote:

> Anith,
> I am not sure I understand what are you suggesting. You are right in
> assuming that this is a administrative task and there is no concern or
> SQL injection however I do not uderstand the solution.
> Currently the Restore Stored proc is complied in the master database
> and after the individual database is restored I am then compilibg the
> other stored proc to drop all the users.
> You may be into something, but I just need to understand to apply it
> Thanks
> Anith Sen wrote:
>
|||No the databases do not contain the stored proc to drop or create
users, I complie the stored proc after I restore the database and I
was wondering if there was some way from my stored proc to restore the
database if I could do all of that in one step. I think from the input
I have got, it sounds like I pretty much have to build a large dynamic
SQL string or do the way I am doing. (Two Step Process)
I need to drop the databases because every once in a while we will pull
in databases from different networks so when it is restored on ours
those users no longer exist so to keep it clean I prefer to delete the
users.
John Bell wrote:[vbcol=seagreen]
> Hi
> I believe Anith is saying that as this is being used in a controlled manner,
> there is no need to worry about SQL Injection as there is no input from a
> non-trusted user being executed.
> If your database contains it own procedure to drop/create the users then you
> can just run that, although anyone else with enough rights can run this
> procedure as well.
> Why do you need to drop/re-create the users?
> John
> "shub" wrote:
|||Hi
You could always restore the database with a given name and then change it
after this process using sp_renamedb. You may want to use something like the
following that assumes the database is called TESTUSERS, but you could make
it dynamic:
USE TEMPDB
GO
CREATE TABLE #users ( UserName sysname, UserSID varbinary(85) )
/* You may want to look at TESTUSERS..sysusers, although that would need to
exclude some entries */
INSERT INTO #users ( UserName, UserSID )
EXEC TESTUSERS..sp_change_users_login 'report'
/* Assumes user names and logins are the same */
DECLARE usercursor CURSOR FOR SELECT 'EXEC TESTUSERS..sp_change_users_login
''update_one'', ' + QUOTENAME(UserName) + ', ' + QUOTENAME(UserName) FROM
#users
DECLARE @.updatecmd sysname
OPEN usercursor
FETCH NEXT FROM usercursor INTO @.updatecmd
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.updatecmd
EXEC ( @.updatecmd )
FETCH NEXT FROM usercursor INTO @.updatecmd
END
CLOSE usercursor
DEALLOCATE usercursor
GO
/* Alternatively dropping users */
DECLARE usercursor CURSOR FOR SELECT 'EXEC TESTUSERS..sp_dropuser ' +
QUOTENAME(UserName) FROM #users
DECLARE @.updatecmd sysname
OPEN usercursor
FETCH NEXT FROM usercursor INTO @.updatecmd
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.updatecmd
EXEC ( @.updatecmd )
FETCH NEXT FROM usercursor INTO @.updatecmd
END
CLOSE usercursor
DEALLOCATE usercursor
John
"shub" wrote:

> No the databases do not contain the stored proc to drop or create
> users, I complie the stored proc after I restore the database and I
> was wondering if there was some way from my stored proc to restore the
> database if I could do all of that in one step. I think from the input
> I have got, it sounds like I pretty much have to build a large dynamic
> SQL string or do the way I am doing. (Two Step Process)
> I need to drop the databases because every once in a while we will pull
> in databases from different networks so when it is restored on ours
> those users no longer exist so to keep it clean I prefer to delete the
> users.
> John Bell wrote:
>

Need to use "USE" in a stored Proc

I have a stored Procedure that I always use to restore databases.It
accepts the backup file name,name of the database to be restored as
amongst other parameters. It works pretty good and helps me speed up
the process. However after the database is restored, I need to connect
to that database and compile another stored procedure on that database
and then execute that stored procedure to drop all the users from that
database. I then execute another stored procedure to add the new users
and apply appropriate security.
What I want to do now is to see if there is a way to combine all those
steps all into one stored procedure that will restore the database and
then drop all the users from that database and then add the new users.
When I first attempted it I ran into the issue where I could not use
the "USE " statement in the stored proc, so I ended up writing two
different procs one for dropping users and one for adding new users
that I compile and execute after the database has been restored.
I am sure other people have run into this situation too where within a
stored proc, it is necessary to switch database and perform other tasks
in a different database. I am just looking to get some input to see how
you overcame or handled that issue.
Any help inthis regard will be greatly appreciated.
ThanksYou will need to build dynamic sql script, builiding a string with the
first word 'use dbname', and then execute the string.
Terry
shub wrote:
> I have a stored Procedure that I always use to restore databases.It
> accepts the backup file name,name of the database to be restored as
> amongst other parameters. It works pretty good and helps me speed up
> the process. However after the database is restored, I need to connect
> to that database and compile another stored procedure on that database
> and then execute that stored procedure to drop all the users from that
> database. I then execute another stored procedure to add the new users
> and apply appropriate security.
> What I want to do now is to see if there is a way to combine all those
> steps all into one stored procedure that will restore the database and
> then drop all the users from that database and then add the new users.
> When I first attempted it I ran into the issue where I could not use
> the "USE " statement in the stored proc, so I ended up writing two
> different procs one for dropping users and one for adding new users
> that I compile and execute after the database has been restored.
> I am sure other people have run into this situation too where within a
> stored proc, it is necessary to switch database and perform other tasks
> in a different database. I am just looking to get some input to see how
> you overcame or handled that issue.
> Any help inthis regard will be greatly appreciated.
> Thanks|||Consider 3-part naming like database.owner.tablename format rather than USE
statement.
Anith|||On Fri, 15 Dec 2006 09:26:54 -0600, "Anith Sen"
<anith@.bizdatasolutions.com> wrote:

>Consider 3-part naming like database.owner.tablename format rather than USE
>statement.
Since the database name changes, that would require dynamic SQL.
Roy|||On 15 Dec 2006 07:09:00 -0800, "Terry" <tduffy@.calamos.com> wrote:

>You will need to build dynamic sql script, builiding a string with the
>first word 'use dbname', and then execute the string.
It is worth noting that the change that the USE makes does not persist
past the end of the dynamic SQL script. All the processing that has
to occur in the new database would have to be part of the dynamic SQL
string.
Roy|||Hi
Why do you need to drop all the users? If you have orphaned users use
sp_change_users_login
John
"shub" wrote:

> I have a stored Procedure that I always use to restore databases.It
> accepts the backup file name,name of the database to be restored as
> amongst other parameters. It works pretty good and helps me speed up
> the process. However after the database is restored, I need to connect
> to that database and compile another stored procedure on that database
> and then execute that stored procedure to drop all the users from that
> database. I then execute another stored procedure to add the new users
> and apply appropriate security.
> What I want to do now is to see if there is a way to combine all those
> steps all into one stored procedure that will restore the database and
> then drop all the users from that database and then add the new users.
> When I first attempted it I ran into the issue where I could not use
> the "USE " statement in the stored proc, so I ended up writing two
> different procs one for dropping users and one for adding new users
> that I compile and execute after the database has been restored.
> I am sure other people have run into this situation too where within a
> stored proc, it is necessary to switch database and perform other tasks
> in a different database. I am just looking to get some input to see how
> you overcame or handled that issue.
> Any help inthis regard will be greatly appreciated.
> Thanks
>|||>> Since the database name changes, that would require dynamic SQL.
That should be fine, if written properly, right? Since it is a
administrative task, it has little to do with injection risks or
recompilation issues.
Otherwise, he can consider a few alternatives:
1. Create individual RESTORE statements within the proc & use IF clause to
control the execution flow
2. Create separate stored procedures in each database & call it from the
main proc with the database as the parameter.
EXEC @.r = @.db.dbo.restore_proc ;
Anith|||Anith,
I am not sure I understand what are you suggesting. You are right in
assuming that this is a administrative task and there is no concern or
SQL injection however I do not uderstand the solution.
Currently the Restore Stored proc is complied in the master database
and after the individual database is restored I am then compilibg the
other stored proc to drop all the users.
You may be into something, but I just need to understand to apply it
Thanks
Anith Sen wrote:
> That should be fine, if written properly, right? Since it is a
> administrative task, it has little to do with injection risks or
> recompilation issues.
> Otherwise, he can consider a few alternatives:
> 1. Create individual RESTORE statements within the proc & use IF clause to
> control the execution flow
> 2. Create separate stored procedures in each database & call it from the
> main proc with the database as the parameter.
> EXEC @.r = @.db.dbo.restore_proc ;
> --
> Anith|||Hi
I believe Anith is saying that as this is being used in a controlled manner,
there is no need to worry about SQL Injection as there is no input from a
non-trusted user being executed.
If your database contains it own procedure to drop/create the users then you
can just run that, although anyone else with enough rights can run this
procedure as well.
Why do you need to drop/re-create the users?
John
"shub" wrote:

> Anith,
> I am not sure I understand what are you suggesting. You are right in
> assuming that this is a administrative task and there is no concern or
> SQL injection however I do not uderstand the solution.
> Currently the Restore Stored proc is complied in the master database
> and after the individual database is restored I am then compilibg the
> other stored proc to drop all the users.
> You may be into something, but I just need to understand to apply it
> Thanks
> Anith Sen wrote:
>|||No the databases do not contain the stored proc to drop or create
users, I complie the stored proc after I restore the database and I
was wondering if there was some way from my stored proc to restore the
database if I could do all of that in one step. I think from the input
I have got, it sounds like I pretty much have to build a large dynamic
SQL string or do the way I am doing. (Two Step Process)
I need to drop the databases because every once in a while we will pull
in databases from different networks so when it is restored on ours
those users no longer exist so to keep it clean I prefer to delete the
users.
John Bell wrote:[vbcol=seagreen]
> Hi
> I believe Anith is saying that as this is being used in a controlled manne
r,
> there is no need to worry about SQL Injection as there is no input from a
> non-trusted user being executed.
> If your database contains it own procedure to drop/create the users then y
ou
> can just run that, although anyone else with enough rights can run this
> procedure as well.
> Why do you need to drop/re-create the users?
> John
> "shub" wrote:
>

Need to use "USE" in a stored Proc

I have a stored Procedure that I always use to restore databases.It
accepts the backup file name,name of the database to be restored as
amongst other parameters. It works pretty good and helps me speed up
the process. However after the database is restored, I need to connect
to that database and compile another stored procedure on that database
and then execute that stored procedure to drop all the users from that
database. I then execute another stored procedure to add the new users
and apply appropriate security.
What I want to do now is to see if there is a way to combine all those
steps all into one stored procedure that will restore the database and
then drop all the users from that database and then add the new users.
When I first attempted it I ran into the issue where I could not use
the "USE " statement in the stored proc, so I ended up writing two
different procs one for dropping users and one for adding new users
that I compile and execute after the database has been restored.
I am sure other people have run into this situation too where within a
stored proc, it is necessary to switch database and perform other tasks
in a different database. I am just looking to get some input to see how
you overcame or handled that issue.
Any help inthis regard will be greatly appreciated.
ThanksYou will need to build dynamic sql script, builiding a string with the
first word 'use dbname', and then execute the string.
Terry
shub wrote:
> I have a stored Procedure that I always use to restore databases.It
> accepts the backup file name,name of the database to be restored as
> amongst other parameters. It works pretty good and helps me speed up
> the process. However after the database is restored, I need to connect
> to that database and compile another stored procedure on that database
> and then execute that stored procedure to drop all the users from that
> database. I then execute another stored procedure to add the new users
> and apply appropriate security.
> What I want to do now is to see if there is a way to combine all those
> steps all into one stored procedure that will restore the database and
> then drop all the users from that database and then add the new users.
> When I first attempted it I ran into the issue where I could not use
> the "USE " statement in the stored proc, so I ended up writing two
> different procs one for dropping users and one for adding new users
> that I compile and execute after the database has been restored.
> I am sure other people have run into this situation too where within a
> stored proc, it is necessary to switch database and perform other tasks
> in a different database. I am just looking to get some input to see how
> you overcame or handled that issue.
> Any help inthis regard will be greatly appreciated.
> Thanks|||Consider 3-part naming like database.owner.tablename format rather than USE
statement.
--
Anith|||On Fri, 15 Dec 2006 09:26:54 -0600, "Anith Sen"
<anith@.bizdatasolutions.com> wrote:
>Consider 3-part naming like database.owner.tablename format rather than USE
>statement.
Since the database name changes, that would require dynamic SQL.
Roy|||On 15 Dec 2006 07:09:00 -0800, "Terry" <tduffy@.calamos.com> wrote:
>You will need to build dynamic sql script, builiding a string with the
>first word 'use dbname', and then execute the string.
It is worth noting that the change that the USE makes does not persist
past the end of the dynamic SQL script. All the processing that has
to occur in the new database would have to be part of the dynamic SQL
string.
Roy|||Hi
Why do you need to drop all the users? If you have orphaned users use
sp_change_users_login
John
"shub" wrote:
> I have a stored Procedure that I always use to restore databases.It
> accepts the backup file name,name of the database to be restored as
> amongst other parameters. It works pretty good and helps me speed up
> the process. However after the database is restored, I need to connect
> to that database and compile another stored procedure on that database
> and then execute that stored procedure to drop all the users from that
> database. I then execute another stored procedure to add the new users
> and apply appropriate security.
> What I want to do now is to see if there is a way to combine all those
> steps all into one stored procedure that will restore the database and
> then drop all the users from that database and then add the new users.
> When I first attempted it I ran into the issue where I could not use
> the "USE " statement in the stored proc, so I ended up writing two
> different procs one for dropping users and one for adding new users
> that I compile and execute after the database has been restored.
> I am sure other people have run into this situation too where within a
> stored proc, it is necessary to switch database and perform other tasks
> in a different database. I am just looking to get some input to see how
> you overcame or handled that issue.
> Any help inthis regard will be greatly appreciated.
> Thanks
>|||>> Since the database name changes, that would require dynamic SQL.
That should be fine, if written properly, right? Since it is a
administrative task, it has little to do with injection risks or
recompilation issues.
Otherwise, he can consider a few alternatives:
1. Create individual RESTORE statements within the proc & use IF clause to
control the execution flow
2. Create separate stored procedures in each database & call it from the
main proc with the database as the parameter.
EXEC @.r = @.db.dbo.restore_proc ;
--
Anith|||Anith,
I am not sure I understand what are you suggesting. You are right in
assuming that this is a administrative task and there is no concern or
SQL injection however I do not uderstand the solution.
Currently the Restore Stored proc is complied in the master database
and after the individual database is restored I am then compilibg the
other stored proc to drop all the users.
You may be into something, but I just need to understand to apply it
Thanks
Anith Sen wrote:
> >> Since the database name changes, that would require dynamic SQL.
> That should be fine, if written properly, right? Since it is a
> administrative task, it has little to do with injection risks or
> recompilation issues.
> Otherwise, he can consider a few alternatives:
> 1. Create individual RESTORE statements within the proc & use IF clause to
> control the execution flow
> 2. Create separate stored procedures in each database & call it from the
> main proc with the database as the parameter.
> EXEC @.r = @.db.dbo.restore_proc ;
> --
> Anith|||Hi
I believe Anith is saying that as this is being used in a controlled manner,
there is no need to worry about SQL Injection as there is no input from a
non-trusted user being executed.
If your database contains it own procedure to drop/create the users then you
can just run that, although anyone else with enough rights can run this
procedure as well.
Why do you need to drop/re-create the users?
John
"shub" wrote:
> Anith,
> I am not sure I understand what are you suggesting. You are right in
> assuming that this is a administrative task and there is no concern or
> SQL injection however I do not uderstand the solution.
> Currently the Restore Stored proc is complied in the master database
> and after the individual database is restored I am then compilibg the
> other stored proc to drop all the users.
> You may be into something, but I just need to understand to apply it
> Thanks
> Anith Sen wrote:
> > >> Since the database name changes, that would require dynamic SQL.
> >
> > That should be fine, if written properly, right? Since it is a
> > administrative task, it has little to do with injection risks or
> > recompilation issues.
> >
> > Otherwise, he can consider a few alternatives:
> >
> > 1. Create individual RESTORE statements within the proc & use IF clause to
> > control the execution flow
> > 2. Create separate stored procedures in each database & call it from the
> > main proc with the database as the parameter.
> >
> > EXEC @.r = @.db.dbo.restore_proc ;
> >
> > --
> > Anith
>|||No the databases do not contain the stored proc to drop or create
users, I complie the stored proc after I restore the database and I
was wondering if there was some way from my stored proc to restore the
database if I could do all of that in one step. I think from the input
I have got, it sounds like I pretty much have to build a large dynamic
SQL string or do the way I am doing. (Two Step Process)
I need to drop the databases because every once in a while we will pull
in databases from different networks so when it is restored on ours
those users no longer exist so to keep it clean I prefer to delete the
users.
John Bell wrote:
> Hi
> I believe Anith is saying that as this is being used in a controlled manner,
> there is no need to worry about SQL Injection as there is no input from a
> non-trusted user being executed.
> If your database contains it own procedure to drop/create the users then you
> can just run that, although anyone else with enough rights can run this
> procedure as well.
> Why do you need to drop/re-create the users?
> John
> "shub" wrote:
> > Anith,
> > I am not sure I understand what are you suggesting. You are right in
> > assuming that this is a administrative task and there is no concern or
> > SQL injection however I do not uderstand the solution.
> >
> > Currently the Restore Stored proc is complied in the master database
> > and after the individual database is restored I am then compilibg the
> > other stored proc to drop all the users.
> >
> > You may be into something, but I just need to understand to apply it
> > Thanks
> > Anith Sen wrote:
> > > >> Since the database name changes, that would require dynamic SQL.
> > >
> > > That should be fine, if written properly, right? Since it is a
> > > administrative task, it has little to do with injection risks or
> > > recompilation issues.
> > >
> > > Otherwise, he can consider a few alternatives:
> > >
> > > 1. Create individual RESTORE statements within the proc & use IF clause to
> > > control the execution flow
> > > 2. Create separate stored procedures in each database & call it from the
> > > main proc with the database as the parameter.
> > >
> > > EXEC @.r = @.db.dbo.restore_proc ;
> > >
> > > --
> > > Anith
> >
> >