How to force 'lazy spool'

Hi,
I've got 2 server. One is the restore of the other.
But query where not performed by the same way on each.
One use a lazy spool, and the other does not.
The first make 2s to answer the query
The second make 4min !
I'd like to anderstand why !
It's the same installation of SQL Server 2000
And database are restore from each other.
Did anyone have an explication ?
ThanksHi
update statistics on both servers (for details please refer to the BOL)
"Florimond" <florimond@.gmail.com> wrote in message
news:ee293729.0504270647.7322ed74@.posting.google.com...
> Hi,
> I've got 2 server. One is the restore of the other.
> But query where not performed by the same way on each.
> One use a lazy spool, and the other does not.
> The first make 2s to answer the query
> The second make 4min !
> I'd like to anderstand why !
> It's the same installation of SQL Server 2000
> And database are restore from each other.
> Did anyone have an explication ?
> Thanks

How to fix an orphaned alias?

I have a database which has been moved from one server to another using
backup/restore. Of course there were some problems with mapping of
userid to logins. That was solved by running sp_change_users_login.
But one problem remains. I had one userid that I had aliased to dbo.
That userid is not listed when using sp_change_users_login 'report' and
it is not possible to drop the alias either.
How can I fix this problem? Delete the userid manually from sysusers or
what?Do you by "alias" mean created with sp_addalias?
If so, can you remove it using sp_dropalias?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Magnus Bergh" <magnusb@.sbbs.se> wrote in message
news:MPG.2140b997ebb0f77398969f@.msnews.microsoft.com...
>I have a database which has been moved from one server to another using
> backup/restore. Of course there were some problems with mapping of
> userid to logins. That was solved by running sp_change_users_login.
> But one problem remains. I had one userid that I had aliased to dbo.
> That userid is not listed when using sp_change_users_login 'report' and
> it is not possible to drop the alias either.
> How can I fix this problem? Delete the userid manually from sysusers or
> what?
>|||In article <#6dL$Pv6HHA.980@.TK2MSFTNGP06.phx.gbl>,
tibor_please.no.email_karaszi@.hotmail.nomail.com says...
> Do you by "alias" mean created with sp_addalias?
> If so, can you remove it using sp_dropalias?
Yes, the user was aliased to the dbo using sp_addalias. sp_dropalias
doesn't work (probably because the sid doesn't match the sid for the
login).
Note: The database was moved from one server to another server so that
is why I have a problem with users in database does not match the server
logins.|||> sp_dropalias
> doesn't work (probably because the sid doesn't match the sid for the
> login).
Ahh. How about creating a login with the correct SID so the alias matches something and then try
sp_dropalias? Also, note that aliases has been deprecated since 7.0, so you should plan for stop
using them. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Magnus Bergh" <magnusb@.sbbs.se> wrote in message
news:MPG.21423f1b50bae9779896a0@.msnews.microsoft.com...
> In article <#6dL$Pv6HHA.980@.TK2MSFTNGP06.phx.gbl>,
> tibor_please.no.email_karaszi@.hotmail.nomail.com says...
>> Do you by "alias" mean created with sp_addalias?
>> If so, can you remove it using sp_dropalias?
> Yes, the user was aliased to the dbo using sp_addalias. sp_dropalias
> doesn't work (probably because the sid doesn't match the sid for the
> login).
> Note: The database was moved from one server to another server so that
> is why I have a problem with users in database does not match the server
> logins.
>

How to find out restore progress of the job

Hello,
I was wondering if there is a way to find out restore progress of the
job. If I run restore in QA, I can use STATS option to control restore
progress statistics but this information is not accessible when restore
executed as a part of a job.
Thanks,
Igor
Hi,
You can do it using a batch file and call the batch file inside the SQL
Agent -- Jobs (Command
1. Batch file should be:-
OSQL -Uuser -Ppassword -Sserver -d dbname -Q"backup database pubs to
pubsbak' with init, stats=10 -oc:\backup.log
2. Then schedule the batch using SQL Agent job with type as "Operating
system command".
3. During job you could open the c:\backup.log to get the status
I hope this will work out.. I have not tested this so far Probably you
could test and get back.
Thanks
Hari
SQL Server MVP
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:eRAVJRkOFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I was wondering if there is a way to find out restore progress of the
> job. If I run restore in QA, I can use STATS option to control restore
> progress statistics but this information is not accessible when restore
> executed as a part of a job.
>
> Thanks,
> Igor
>
|||It worked nicely. Thanks a lot, Hari! I have found another way. When
scheduling a job in EM, you can specify Output file in Advanced tab of the
task. Stats progress is being logged into this file.
Igor
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OynaXakOFHA.3880@.tk2msftngp13.phx.gbl...
> Hi,
> You can do it using a batch file and call the batch file inside the SQL
> Agent -- Jobs (Command
> 1. Batch file should be:-
> OSQL -Uuser -Ppassword -Sserver -d dbname -Q"backup database pubs to
> pubsbak' with init, stats=10 -oc:\backup.log
> 2. Then schedule the batch using SQL Agent job with type as "Operating
> system command".
> 3. During job you could open the c:\backup.log to get the status
> I hope this will work out.. I have not tested this so far Probably you
> could test and get back.
> Thanks
> Hari
> SQL Server MVP
>
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:eRAVJRkOFHA.1500@.TK2MSFTNGP09.phx.gbl...
>

How to find out restore progress of the job

Hello,
I was wondering if there is a way to find out restore progress of the
job. If I run restore in QA, I can use STATS option to control restore
progress statistics but this information is not accessible when restore
executed as a part of a job.
Thanks,
IgorHi,
You can do it using a batch file and call the batch file inside the SQL
Agent -- Jobs (Command
1. Batch file should be:-
OSQL -Uuser -Ppassword -Sserver -d dbname -Q"backup database pubs to
pubsbak' with init, stats=10 -oc:\backup.log
2. Then schedule the batch using SQL Agent job with type as "Operating
system command".
3. During job you could open the c:\backup.log to get the status
I hope this will work out.. I have not tested this so far Probably you
could test and get back.
Thanks
Hari
SQL Server MVP
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:eRAVJRkOFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I was wondering if there is a way to find out restore progress of the
> job. If I run restore in QA, I can use STATS option to control restore
> progress statistics but this information is not accessible when restore
> executed as a part of a job.
>
> Thanks,
> Igor
>|||It worked nicely. Thanks a lot, Hari! I have found another way. When
scheduling a job in EM, you can specify Output file in Advanced tab of the
task. Stats progress is being logged into this file.
Igor
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OynaXakOFHA.3880@.tk2msftngp13.phx.gbl...
> Hi,
> You can do it using a batch file and call the batch file inside the SQL
> Agent -- Jobs (Command
> 1. Batch file should be:-
> OSQL -Uuser -Ppassword -Sserver -d dbname -Q"backup database pubs to
> pubsbak' with init, stats=10 -oc:\backup.log
> 2. Then schedule the batch using SQL Agent job with type as "Operating
> system command".
> 3. During job you could open the c:\backup.log to get the status
> I hope this will work out.. I have not tested this so far Probably you
> could test and get back.
> Thanks
> Hari
> SQL Server MVP
>
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:eRAVJRkOFHA.1500@.TK2MSFTNGP09.phx.gbl...
>

How to find out restore progress of the job

Hello,
I was wondering if there is a way to find out restore progress of the
job. If I run restore in QA, I can use STATS option to control restore
progress statistics but this information is not accessible when restore
executed as a part of a job.
Thanks,
IgorHi,
You can do it using a batch file and call the batch file inside the SQL
Agent -- Jobs (Command
1. Batch file should be:-
OSQL -Uuser -Ppassword -Sserver -d dbname -Q"backup database pubs to
pubsbak' with init, stats=10 -oc:\backup.log
2. Then schedule the batch using SQL Agent job with type as "Operating
system command".
3. During job you could open the c:\backup.log to get the status
I hope this will work out.. I have not tested this so far :) Probably you
could test and get back.
Thanks
Hari
SQL Server MVP
"Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
news:eRAVJRkOFHA.1500@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I was wondering if there is a way to find out restore progress of the
> job. If I run restore in QA, I can use STATS option to control restore
> progress statistics but this information is not accessible when restore
> executed as a part of a job.
>
> Thanks,
> Igor
>|||It worked nicely. Thanks a lot, Hari! I have found another way. When
scheduling a job in EM, you can specify Output file in Advanced tab of the
task. Stats progress is being logged into this file.
Igor
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OynaXakOFHA.3880@.tk2msftngp13.phx.gbl...
> Hi,
> You can do it using a batch file and call the batch file inside the SQL
> Agent -- Jobs (Command
> 1. Batch file should be:-
> OSQL -Uuser -Ppassword -Sserver -d dbname -Q"backup database pubs to
> pubsbak' with init, stats=10 -oc:\backup.log
> 2. Then schedule the batch using SQL Agent job with type as "Operating
> system command".
> 3. During job you could open the c:\backup.log to get the status
> I hope this will work out.. I have not tested this so far :) Probably you
> could test and get back.
> Thanks
> Hari
> SQL Server MVP
>
> "Igor Marchenko" <igormarchenko@.hotmail.com> wrote in message
> news:eRAVJRkOFHA.1500@.TK2MSFTNGP09.phx.gbl...
>> Hello,
>> I was wondering if there is a way to find out restore progress of the
>> job. If I run restore in QA, I can use STATS option to control restore
>> progress statistics but this information is not accessible when restore
>> executed as a part of a job.
>>
>> Thanks,
>> Igor
>

How to find currpted backup file

1.How can i find out whether the backup file is currpted or good enough to
restore.
2.Is there any other ways to restore db without loosing any data when my
database files (mdf & Ldf) and my backup file is currupt
ThanksHello,
1.How can i find out whether the backup file is currpted or good enough to
restore.
RESTORE VERIFY ONLY
2. Is there any other ways to restore db without loosing any data when my
database files (mdf & Ldf) and my backup file is currupt
If both MDF and LDF is corrupted. You cant do much. Only way is to
restore from good backup. Incase if ur LDF is giving issues or if u have 803
error you can
1. Create a database with same
2. Stop SQL Server and copy the corrupted MDF above the new MDF file
3. Start SQL Server
4. Datbaase will be marked suspect. Change the mode to EMergency
5, Using DTS or Scripts to copy the data
Steps will varry based on the situation.
Thanks
Hari
"Khwaja Arshaduddin" <KhwajaArshaduddin@.discussions.microsoft.com> wrote in
message news:E5825040-0628-4821-8645-A5C833BDB601@.microsoft.com...
> 1.How can i find out whether the backup file is currpted or good enough to
> restore.
> 2.Is there any other ways to restore db without loosing any data when my
> database files (mdf & Ldf) and my backup file is currupt
> Thanks|||Thanks Hari
"Hari Prasad" wrote:

> Hello,
> 1.How can i find out whether the backup file is currpted or good enough to
> restore.
> RESTORE VERIFY ONLY
> 2. Is there any other ways to restore db without loosing any data when my
> database files (mdf & Ldf) and my backup file is currupt
> If both MDF and LDF is corrupted. You cant do much. Only way is to
> restore from good backup. Incase if ur LDF is giving issues or if u have 8
03
> error you can
> 1. Create a database with same
> 2. Stop SQL Server and copy the corrupted MDF above the new MDF file
> 3. Start SQL Server
> 4. Datbaase will be marked suspect. Change the mode to EMergency
> 5, Using DTS or Scripts to copy the data
> Steps will varry based on the situation.
> Thanks
> Hari
>
> "Khwaja Arshaduddin" <KhwajaArshaduddin@.discussions.microsoft.com> wrote i
n
> message news:E5825040-0628-4821-8645-A5C833BDB601@.microsoft.com...
>
>|||Note that RESTORE VERIFYONLY doesn't do a "true" verification unless you are
on 2005 and did the
backup using CHECKSUM option. In such cases, you should do a real restore (i
nto a new database, of
course; possibly on another server).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Khwaja Arshaduddin" <KhwajaArshaduddin@.discussions.microsoft.com> wrote in
message
news:66EFEFEC-3A63-4F14-A955-C27DC137EA94@.microsoft.com...[vbcol=seagreen]
> Thanks Hari
> "Hari Prasad" wrote:
>|||Exactly, I agree with Tibor.
Khwaja ,
As a practice for critical databases you could restore the backup once in a
while and do a DBCC CHECKDB and confirm that ur backupis good.
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%230FiC3aUHHA.4076@.TK2MSFTNGP05.phx.gbl...
> Note that RESTORE VERIFYONLY doesn't do a "true" verification unless you
> are on 2005 and did the backup using CHECKSUM option. In such cases, you
> should do a real restore (into a new database, of course; possibly on
> another server).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Khwaja Arshaduddin" <KhwajaArshaduddin@.discussions.microsoft.com> wrote
> in message news:66EFEFEC-3A63-4F14-A955-C27DC137EA94@.microsoft.com...
>

How to find currpted backup file

1.How can i find out whether the backup file is currpted or good enough to
restore.
2.Is there any other ways to restore db without loosing any data when my
database files (mdf & Ldf) and my backup file is currupt
Thanks
Hello,
1.How can i find out whether the backup file is currpted or good enough to
restore.
RESTORE VERIFY ONLY
2. Is there any other ways to restore db without loosing any data when my
database files (mdf & Ldf) and my backup file is currupt
If both MDF and LDF is corrupted. You cant do much. Only way is to
restore from good backup. Incase if ur LDF is giving issues or if u have 803
error you can
1. Create a database with same
2. Stop SQL Server and copy the corrupted MDF above the new MDF file
3. Start SQL Server
4. Datbaase will be marked suspect. Change the mode to EMergency
5, Using DTS or Scripts to copy the data
Steps will varry based on the situation.
Thanks
Hari
"Khwaja Arshaduddin" <KhwajaArshaduddin@.discussions.microsoft.com> wrote in
message news:E5825040-0628-4821-8645-A5C833BDB601@.microsoft.com...
> 1.How can i find out whether the backup file is currpted or good enough to
> restore.
> 2.Is there any other ways to restore db without loosing any data when my
> database files (mdf & Ldf) and my backup file is currupt
> Thanks
|||Thanks Hari
"Hari Prasad" wrote:

> Hello,
> 1.How can i find out whether the backup file is currpted or good enough to
> restore.
> RESTORE VERIFY ONLY
> 2. Is there any other ways to restore db without loosing any data when my
> database files (mdf & Ldf) and my backup file is currupt
> If both MDF and LDF is corrupted. You cant do much. Only way is to
> restore from good backup. Incase if ur LDF is giving issues or if u have 803
> error you can
> 1. Create a database with same
> 2. Stop SQL Server and copy the corrupted MDF above the new MDF file
> 3. Start SQL Server
> 4. Datbaase will be marked suspect. Change the mode to EMergency
> 5, Using DTS or Scripts to copy the data
> Steps will varry based on the situation.
> Thanks
> Hari
>
> "Khwaja Arshaduddin" <KhwajaArshaduddin@.discussions.microsoft.com> wrote in
> message news:E5825040-0628-4821-8645-A5C833BDB601@.microsoft.com...
>
>
|||Exactly, I agree with Tibor.
Khwaja ,
As a practice for critical databases you could restore the backup once in a
while and do a DBCC CHECKDB and confirm that ur backupis good.
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%230FiC3aUHHA.4076@.TK2MSFTNGP05.phx.gbl...
> Note that RESTORE VERIFYONLY doesn't do a "true" verification unless you
> are on 2005 and did the backup using CHECKSUM option. In such cases, you
> should do a real restore (into a new database, of course; possibly on
> another server).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Khwaja Arshaduddin" <KhwajaArshaduddin@.discussions.microsoft.com> wrote
> in message news:66EFEFEC-3A63-4F14-A955-C27DC137EA94@.microsoft.com...
>

How to find currpted backup file

1.How can i find out whether the backup file is currpted or good enough to
restore.
2.Is there any other ways to restore db without loosing any data when my
database files (mdf & Ldf) and my backup file is currupt
ThanksHello,
1.How can i find out whether the backup file is currpted or good enough to
restore.
RESTORE VERIFY ONLY
2. Is there any other ways to restore db without loosing any data when my
database files (mdf & Ldf) and my backup file is currupt
If both MDF and LDF is corrupted. You cant do much. Only way is to
restore from good backup. Incase if ur LDF is giving issues or if u have 803
error you can
1. Create a database with same
2. Stop SQL Server and copy the corrupted MDF above the new MDF file
3. Start SQL Server
4. Datbaase will be marked suspect. Change the mode to EMergency
5, Using DTS or Scripts to copy the data
Steps will varry based on the situation.
Thanks
Hari
"Khwaja Arshaduddin" <KhwajaArshaduddin@.discussions.microsoft.com> wrote in
message news:E5825040-0628-4821-8645-A5C833BDB601@.microsoft.com...
> 1.How can i find out whether the backup file is currpted or good enough to
> restore.
> 2.Is there any other ways to restore db without loosing any data when my
> database files (mdf & Ldf) and my backup file is currupt
> Thanks|||Thanks Hari
"Hari Prasad" wrote:
> Hello,
> 1.How can i find out whether the backup file is currpted or good enough to
> restore.
> RESTORE VERIFY ONLY
> 2. Is there any other ways to restore db without loosing any data when my
> database files (mdf & Ldf) and my backup file is currupt
> If both MDF and LDF is corrupted. You cant do much. Only way is to
> restore from good backup. Incase if ur LDF is giving issues or if u have 803
> error you can
> 1. Create a database with same
> 2. Stop SQL Server and copy the corrupted MDF above the new MDF file
> 3. Start SQL Server
> 4. Datbaase will be marked suspect. Change the mode to EMergency
> 5, Using DTS or Scripts to copy the data
> Steps will varry based on the situation.
> Thanks
> Hari
>
> "Khwaja Arshaduddin" <KhwajaArshaduddin@.discussions.microsoft.com> wrote in
> message news:E5825040-0628-4821-8645-A5C833BDB601@.microsoft.com...
> > 1.How can i find out whether the backup file is currpted or good enough to
> > restore.
> > 2.Is there any other ways to restore db without loosing any data when my
> > database files (mdf & Ldf) and my backup file is currupt
> > Thanks
>
>|||Note that RESTORE VERIFYONLY doesn't do a "true" verification unless you are on 2005 and did the
backup using CHECKSUM option. In such cases, you should do a real restore (into a new database, of
course; possibly on another server).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Khwaja Arshaduddin" <KhwajaArshaduddin@.discussions.microsoft.com> wrote in message
news:66EFEFEC-3A63-4F14-A955-C27DC137EA94@.microsoft.com...
> Thanks Hari
> "Hari Prasad" wrote:
>> Hello,
>> 1.How can i find out whether the backup file is currpted or good enough to
>> restore.
>> RESTORE VERIFY ONLY
>> 2. Is there any other ways to restore db without loosing any data when my
>> database files (mdf & Ldf) and my backup file is currupt
>> If both MDF and LDF is corrupted. You cant do much. Only way is to
>> restore from good backup. Incase if ur LDF is giving issues or if u have 803
>> error you can
>> 1. Create a database with same
>> 2. Stop SQL Server and copy the corrupted MDF above the new MDF file
>> 3. Start SQL Server
>> 4. Datbaase will be marked suspect. Change the mode to EMergency
>> 5, Using DTS or Scripts to copy the data
>> Steps will varry based on the situation.
>> Thanks
>> Hari
>>
>> "Khwaja Arshaduddin" <KhwajaArshaduddin@.discussions.microsoft.com> wrote in
>> message news:E5825040-0628-4821-8645-A5C833BDB601@.microsoft.com...
>> > 1.How can i find out whether the backup file is currpted or good enough to
>> > restore.
>> > 2.Is there any other ways to restore db without loosing any data when my
>> > database files (mdf & Ldf) and my backup file is currupt
>> > Thanks
>>|||Exactly, I agree with Tibor.
Khwaja ,
As a practice for critical databases you could restore the backup once in a
while and do a DBCC CHECKDB and confirm that ur backupis good.
Thanks
Hari
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%230FiC3aUHHA.4076@.TK2MSFTNGP05.phx.gbl...
> Note that RESTORE VERIFYONLY doesn't do a "true" verification unless you
> are on 2005 and did the backup using CHECKSUM option. In such cases, you
> should do a real restore (into a new database, of course; possibly on
> another server).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Khwaja Arshaduddin" <KhwajaArshaduddin@.discussions.microsoft.com> wrote
> in message news:66EFEFEC-3A63-4F14-A955-C27DC137EA94@.microsoft.com...
>> Thanks Hari
>> "Hari Prasad" wrote:
>> Hello,
>> 1.How can i find out whether the backup file is currpted or good enough
>> to
>> restore.
>> RESTORE VERIFY ONLY
>> 2. Is there any other ways to restore db without loosing any data when
>> my
>> database files (mdf & Ldf) and my backup file is currupt
>> If both MDF and LDF is corrupted. You cant do much. Only way is to
>> restore from good backup. Incase if ur LDF is giving issues or if u have
>> 803
>> error you can
>> 1. Create a database with same
>> 2. Stop SQL Server and copy the corrupted MDF above the new MDF file
>> 3. Start SQL Server
>> 4. Datbaase will be marked suspect. Change the mode to EMergency
>> 5, Using DTS or Scripts to copy the data
>> Steps will varry based on the situation.
>> Thanks
>> Hari
>>
>> "Khwaja Arshaduddin" <KhwajaArshaduddin@.discussions.microsoft.com> wrote
>> in
>> message news:E5825040-0628-4821-8645-A5C833BDB601@.microsoft.com...
>> > 1.How can i find out whether the backup file is currpted or good
>> > enough to
>> > restore.
>> > 2.Is there any other ways to restore db without loosing any data when
>> > my
>> > database files (mdf & Ldf) and my backup file is currupt
>> > Thanks
>>
>