Archiving Data

I was exploring ways of archiving old data in our production environment
whilst maintaining it's availability... ...the current data takes 90% of the
queries, but grows very large and puts pressure on the disks... ...I wanted
to explore using a "sliding window" on a partitioned view across a federated
database and have two initial questions I can't find an answer to...
1. Does the data move between partitions when partitions are located on
different federated servers?
2. Assuming a good horizontal partition is chosen, is an automated sliding
window practical in a production environment?
Thanks in advance BenUKPersonally, I'd revise your strategy to use Partioned Tables in SQL2K5.
Awesome feature.
"Ben UK" <BenUK@.discussions.microsoft.com> wrote in message
news:843C5511-A7D1-4323-83A9-7D7B17BA7639@.microsoft.com...
> I was exploring ways of archiving old data in our production environment
> whilst maintaining it's availability... ...the current data takes 90% of
the
> queries, but grows very large and puts pressure on the disks... ...I
wanted
> to explore using a "sliding window" on a partitioned view across a
federated
> database and have two initial questions I can't find an answer to...
> 1. Does the data move between partitions when partitions are located on
> different federated servers?
> 2. Assuming a good horizontal partition is chosen, is an automated sliding
> window practical in a production environment?
> Thanks in advance BenUK

Apply Old Transaction Logs

Hello Experts,
This may be a wacky question, but I'm gonna ask it anyway.
Can I apply some old tran logs in my db which now has a
different structure (changed after when the old tran logs
backed up) and ignore any additional/removed fields?
Thanks in advance?
No, if you are going to restore transaction logs the database has to be =
in standby mode. You place the database in standby mode when restoring =
a full database backup by specifying the WITH STANDBY option.
--=20
Keith
"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in =
message news:a88c01c43687$85adeef0$a401280a@.phx.gbl...
> Hello Experts,
>=20
> This may be a wacky question, but I'm gonna ask it anyway.
>=20
> Can I apply some old tran logs in my db which now has a=20
> different structure (changed after when the old tran logs=20
> backed up) and ignore any additional/removed fields?
>=20
> Thanks in advance?
|||Hello Keith and you responding to my issue, why use
STANDBY, so users don't hurt my db while restore?
Thanks in advance.

>--Original Message--
>No, if you are going to restore transaction logs the
database has to be in standby mode. You place the
database in standby mode when restoring a full database
backup by specifying the WITH STANDBY option.
>--
>Keith
>
>"Konstantinos Michas"
<anonymous@.discussions.microsoft.com> wrote in message
news:a88c01c43687$85adeef0$a401280a@.phx.gbl...[vbcol=seagreen]
anyway.[vbcol=seagreen]
a[vbcol=seagreen]
logs
>.
>
|||You have to use STANDBY (or NORECOVERY) if you want to apply transaction =
logs. From Books Online:
Navigate to SQL Server Books Online (within the SQL Server program =
group) and search for RESTORE within the index tab. Read up on 'RESTORE =
(described).' Within that section will will find the following:
NORECOVERY
Instructs the restore operation to not roll back any uncommitted =
transactions. Either the NORECOVERY or STANDBY option must be specified =
if another transaction log has to be applied. If neither NORECOVERY, =
RECOVERY, or STANDBY is specified, RECOVERY is the default.
SQL Server requires that the WITH NORECOVERY option be used on all but =
the final RESTORE statement when restoring a database backup and =
multiple transaction logs, or when multiple RESTORE statements are =
needed (for example, a full database backup followed by a differential =
database backup).
--=20
Keith
"Konstantinos" <anonymous@.discussions.microsoft.com> wrote in message =
news:ac3b01c4368e$d3106720$a101280a@.phx.gbl...[vbcol=seagreen]
>=20
> Hello Keith and you responding to my issue, why use=20
> STANDBY, so users don't hurt my db while restore?
>=20
> Thanks in advance.
>=20
> database has to be in standby mode. You place the=20
> database in standby mode when restoring a full database=20
> backup by specifying the WITH STANDBY option.
> <anonymous@.discussions.microsoft.com> wrote in message=20
> news:a88c01c43687$85adeef0$a401280a@.phx.gbl...
> anyway.
> a=20
> logs=20
|||"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in message
news:a88c01c43687$85adeef0$a401280a@.phx.gbl...
> Hello Experts,
> This may be a wacky question, but I'm gonna ask it anyway.
>
No it is not a wacky question.

> Can I apply some old tran logs in my db which now has a
> different structure (changed after when the old tran logs
> backed up) and ignore any additional/removed fields?
What you want is a replay of the log in another
context/database.
The anwser to your question is : NO !
You can only apply the log files to the backups of
the database to which they belong and not to another
or a changed database.
http://www.lumigent.com/
Here you find some tools which can do more things
with a logfile. They have a tool 'Log Explorer' which
might be the tool you are looking for.
But if it works it is not as straitforward as 'replaying'
a log. And the tool is not a free tool.
Good luck and keep us informed.
ben brugman.

> Thanks in advance?

Apply Old Transaction Logs

Hello Experts,
This may be a wacky question, but I'm gonna ask it anyway.
Can I apply some old tran logs in my db which now has a
different structure (changed after when the old tran logs
backed up) and ignore any additional/removed fields?
Thanks in advance?No, if you are going to restore transaction logs the database has to be =in standby mode. You place the database in standby mode when restoring =a full database backup by specifying the WITH STANDBY option.
-- Keith
"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in =message news:a88c01c43687$85adeef0$a401280a@.phx.gbl...
> Hello Experts,
> > This may be a wacky question, but I'm gonna ask it anyway.
> > Can I apply some old tran logs in my db which now has a > different structure (changed after when the old tran logs > backed up) and ignore any additional/removed fields?
> > Thanks in advance?|||Hello Keith and you responding to my issue, why use
STANDBY, so users don't hurt my db while restore?
Thanks in advance.
>--Original Message--
>No, if you are going to restore transaction logs the
database has to be in standby mode. You place the
database in standby mode when restoring a full database
backup by specifying the WITH STANDBY option.
>--
>Keith
>
>"Konstantinos Michas"
<anonymous@.discussions.microsoft.com> wrote in message
news:a88c01c43687$85adeef0$a401280a@.phx.gbl...
>> Hello Experts,
>> This may be a wacky question, but I'm gonna ask it
anyway.
>> Can I apply some old tran logs in my db which now has
a
>> different structure (changed after when the old tran
logs
>> backed up) and ignore any additional/removed fields?
>> Thanks in advance?
>.
>|||You have to use STANDBY (or NORECOVERY) if you want to apply transaction =logs. From Books Online:
Navigate to SQL Server Books Online (within the SQL Server program =group) and search for RESTORE within the index tab. Read up on 'RESTORE =(described).' Within that section will will find the following:
NORECOVERY
Instructs the restore operation to not roll back any uncommitted =transactions. Either the NORECOVERY or STANDBY option must be specified =if another transaction log has to be applied. If neither NORECOVERY, =RECOVERY, or STANDBY is specified, RECOVERY is the default.
SQL Server requires that the WITH NORECOVERY option be used on all but =the final RESTORE statement when restoring a database backup and =multiple transaction logs, or when multiple RESTORE statements are =needed (for example, a full database backup followed by a differential =database backup).
-- Keith
"Konstantinos" <anonymous@.discussions.microsoft.com> wrote in message =news:ac3b01c4368e$d3106720$a101280a@.phx.gbl...
> > Hello Keith and you responding to my issue, why use > STANDBY, so users don't hurt my db while restore?
> > Thanks in advance.
> > >--Original Message--
> >No, if you are going to restore transaction logs the > database has to be in standby mode. You place the > database in standby mode when restoring a full database > backup by specifying the WITH STANDBY option.
> >
> >-- > >Keith
> >
> >
> >"Konstantinos Michas" > <anonymous@.discussions.microsoft.com> wrote in message > news:a88c01c43687$85adeef0$a401280a@.phx.gbl...
> >> Hello Experts,
> >> > >> This may be a wacky question, but I'm gonna ask it > anyway.
> >> > >> Can I apply some old tran logs in my db which now has > a > >> different structure (changed after when the old tran > logs > >> backed up) and ignore any additional/removed fields?
> >> > >> Thanks in advance?
> >.
> >|||"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in message
news:a88c01c43687$85adeef0$a401280a@.phx.gbl...
> Hello Experts,
> This may be a wacky question, but I'm gonna ask it anyway.
>
No it is not a wacky question.
> Can I apply some old tran logs in my db which now has a
> different structure (changed after when the old tran logs
> backed up) and ignore any additional/removed fields?
What you want is a replay of the log in another
context/database.
The anwser to your question is : NO !
You can only apply the log files to the backups of
the database to which they belong and not to another
or a changed database.
http://www.lumigent.com/
Here you find some tools which can do more things
with a logfile. They have a tool 'Log Explorer' which
might be the tool you are looking for.
But if it works it is not as straitforward as 'replaying'
a log. And the tool is not a free tool.
Good luck and keep us informed.
ben brugman.
> Thanks in advance?

Apply Old Transaction Logs

Hello Experts,
This may be a wacky question, but I'm gonna ask it anyway.
Can I apply some old tran logs in my db which now has a
different structure (changed after when the old tran logs
backed up) and ignore any additional/removed fields?
Thanks in advance?No, if you are going to restore transaction logs the database has to be =
in standby mode. You place the database in standby mode when restoring =
a full database backup by specifying the WITH STANDBY option.
--=20
Keith
"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in =
message news:a88c01c43687$85adeef0$a401280a@.phx.gbl...
> Hello Experts,
>=20
> This may be a wacky question, but I'm gonna ask it anyway.
>=20
> Can I apply some old tran logs in my db which now has a=20
> different structure (changed after when the old tran logs=20
> backed up) and ignore any additional/removed fields?
>=20
> Thanks in advance?|||Hello Keith and you responding to my issue, why use
STANDBY, so users don't hurt my db while restore?
Thanks in advance.

>--Original Message--
>No, if you are going to restore transaction logs the
database has to be in standby mode. You place the
database in standby mode when restoring a full database
backup by specifying the WITH STANDBY option.
>--
>Keith
>
>"Konstantinos Michas"
<anonymous@.discussions.microsoft.com> wrote in message
news:a88c01c43687$85adeef0$a401280a@.phx.gbl...
anyway.[vbcol=seagreen]
a[vbcol=seagreen]
logs[vbcol=seagreen]
>.
>|||You have to use STANDBY (or NORECOVERY) if you want to apply transaction =
logs. From Books Online:
Navigate to SQL Server Books Online (within the SQL Server program =
group) and search for RESTORE within the index tab. Read up on 'RESTORE =
(described).' Within that section will will find the following:
NORECOVERY
Instructs the restore operation to not roll back any uncommitted =
transactions. Either the NORECOVERY or STANDBY option must be specified =
if another transaction log has to be applied. If neither NORECOVERY, =
RECOVERY, or STANDBY is specified, RECOVERY is the default.
SQL Server requires that the WITH NORECOVERY option be used on all but =
the final RESTORE statement when restoring a database backup and =
multiple transaction logs, or when multiple RESTORE statements are =
needed (for example, a full database backup followed by a differential =
database backup).
--=20
Keith
"Konstantinos" <anonymous@.discussions.microsoft.com> wrote in message =
news:ac3b01c4368e$d3106720$a101280a@.phx.gbl...[vbcol=seagreen]
>=20
> Hello Keith and you responding to my issue, why use=20
> STANDBY, so users don't hurt my db while restore?
>=20
> Thanks in advance.
>=20
> database has to be in standby mode. You place the=20
> database in standby mode when restoring a full database=20
> backup by specifying the WITH STANDBY option.
> <anonymous@.discussions.microsoft.com> wrote in message=20
> news:a88c01c43687$85adeef0$a401280a@.phx.gbl...
> anyway.
> a=20
> logs=20|||"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in message
news:a88c01c43687$85adeef0$a401280a@.phx.gbl...
> Hello Experts,
> This may be a wacky question, but I'm gonna ask it anyway.
>
No it is not a wacky question.

> Can I apply some old tran logs in my db which now has a
> different structure (changed after when the old tran logs
> backed up) and ignore any additional/removed fields?
What you want is a replay of the log in another
context/database.
The anwser to your question is : NO !
You can only apply the log files to the backups of
the database to which they belong and not to another
or a changed database.
http://www.lumigent.com/
Here you find some tools which can do more things
with a logfile. They have a tool 'Log Explorer' which
might be the tool you are looking for.
But if it works it is not as straitforward as 'replaying'
a log. And the tool is not a free tool.
Good luck and keep us informed.
ben brugman.

> Thanks in advance?

Apparent Successful upgrade to 2005 but old (2000) SQL Server remains

I used the SQL Server 2005 Upgrade Advisor to upgrade from SQL Server 2000 Enterprise to 2005 Standard. The only complaint I got concerned DTS packages, but I had none anyway. When I open SQL Server Management Studio, I can run queries, but they're against tables in the old 2000 databases. The SQL engine and Server agent are version version 8.0. Not surprising that new TSQL statements like 'BACKUP SERVICE MASTER KEY TO FILE' won't work.

Do I have to uninstall my previous version before upgrading?

Thanks.

It sounds like your database(s) were not upgraded. Perhaps you installed only the client tools.

Try running the installer again, taking care to upgrade the existing database.

|||

Actually, there was an install-stopping error in trying to migrate DTS Transformation Services. This is a known upgrade issue. I proceded to dodge the issue by un-installing SQL Server 2005 and then doing a side-by-side installation. By installing a second (named) instance of SQL Server 2005 and leaving the existing SQL Server 2000 instance in place, I avoided the DTS problem. Now it's just a matter of moving my databases from the old instance to the new.

Could be worse. It could be raining.

If I knew how to mark the issue as resolved, I would do it now.

John

Apologies for half a post below ... as I was asking

How can I find out what appications ( if any ) are connecting to the database
using the sa password. Keeping in mind these are old applications that may
have the uid and pwd hardcoded in them. They are old apps with no
documentation and we, the DBA's are seperate from developers.
Any ideas would be welcome.exec sp_who2
Shows a wealth of good information.
Rick Sawtell
MCT, MCSD, MCDBA
Shou
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:8074B1F2-C889-4A83-852B-F4BAC5C5CD85@.microsoft.com...
> How can I find out what appications ( if any ) are connecting to the
database
> using the sa password. Keeping in mind these are old applications that
may
> have the uid and pwd hardcoded in them. They are old apps with no
> documentation and we, the DBA's are seperate from developers.
> Any ideas would be welcome.|||This is a multi-part message in MIME format.
--=_NextPart_000_008B_01C4ABE7.E14691B0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
You can also use profiler to filter this.
Tunji O
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message =news:8074B1F2-C889-4A83-852B-F4BAC5C5CD85@.microsoft.com...
How can I find out what appications ( if any ) are connecting to the =database using the sa password. Keeping in mind these are old applications =that may have the uid and pwd hardcoded in them. They are old apps with no documentation and we, the DBA's are seperate from developers.
Any ideas would be welcome.
--=_NextPart_000_008B_01C4ABE7.E14691B0
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You can also use profiler to filter this.
Tunji O
"MANCPOLYMAN" wrote in message news:807=4B1F2-C889-4A83-852B-F4BAC5C5CD85@.microsoft.com...How can I find out what appications ( if any ) are connecting to the =database using the sa password. Keeping in mind these are old =applications that may have the uid and pwd hardcoded in them. They are =old apps with no documentation and we, the DBA's are seperate from developers.Any ideas would be =welcome.

---

--=_NextPart_000_008B_01C4ABE7.E14691B0--

Apologies for half a post below ... as I was asking

How can I find out what appications ( if any ) are connecting to the database
using the sa password. Keeping in mind these are old applications that may
have the uid and pwd hardcoded in them. They are old apps with no
documentation and we, the DBA's are seperate from developers.
Any ideas would be welcome.
exec sp_who2
Shows a wealth of good information.
Rick Sawtell
MCT, MCSD, MCDBA
Shou
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message
news:8074B1F2-C889-4A83-852B-F4BAC5C5CD85@.microsoft.com...
> How can I find out what appications ( if any ) are connecting to the
database
> using the sa password. Keeping in mind these are old applications that
may
> have the uid and pwd hardcoded in them. They are old apps with no
> documentation and we, the DBA's are seperate from developers.
> Any ideas would be welcome.
|||You can also use profiler to filter this.
Tunji O
"MANCPOLYMAN" <MANCPOLYMAN@.discussions.microsoft.com> wrote in message news:8074B1F2-C889-4A83-852B-F4BAC5C5CD85@.microsoft.com...
How can I find out what appications ( if any ) are connecting to the database
using the sa password. Keeping in mind these are old applications that may
have the uid and pwd hardcoded in them. They are old apps with no
documentation and we, the DBA's are seperate from developers.
Any ideas would be welcome.