Hi All,
I have one audit table in my db and i would like to do this:
1: always that user login in SQL I would like add one entry in this table.
(like this, USER 'xxx' SID 'xxx' log on Server 13:30:22...) I use Mixed
Mode (authentication)
2: always that user INSER, DELET or UPDATE tables, I would like add one
entry in audit table. (like this, USER 'xxx' SID 'xxx' DELET of table'xx'
13:30:22...)
Have any way to do this, or have other better way to do this?
ThanksThird party tools are going to be much better than re-inventing the wheel.
Lumigent's Entegra comes to mind, but there are several others available.
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23BmV0bZqFHA.3352@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have one audit table in my db and i would like to do this:
> 1: always that user login in SQL I would like add one entry in this
> table. (like this, USER 'xxx' SID 'xxx' log on Server 13:30:22...) I use
> Mixed Mode (authentication)
> 2: always that user INSER, DELET or UPDATE tables, I would like add one
> entry in audit table. (like this, USER 'xxx' SID 'xxx' DELET of table'xx'
> 13:30:22...)
> Have any way to do this, or have other better way to do this?
> Thanks
>|||I'm total agree with Aaron Bertrand, but if you can not afford such tools
then the only way in SQLServer 2000 is
- Profiler (not from GUI)
- c2 audit mode option (see BOL)
Fortunately SQLServer 2005 helps a lot with DDL triggers
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uMhz0eZqFHA.3920@.TK2MSFTNGP09.phx.gbl...
> Third party tools are going to be much better than re-inventing the wheel.
> Lumigent's Entegra comes to mind, but there are several others available.
>
> "ReTF" <re.tf@.newsgroup.nospam> wrote in message
> news:%23BmV0bZqFHA.3352@.TK2MSFTNGP14.phx.gbl...
>
Showing posts with label this1. Show all posts
Showing posts with label this1. Show all posts
Saturday, February 25, 2012
Sunday, February 12, 2012
Attaching a database, but missing a file
The situation is this:
1. I use an off-site service to backup my SQL database.
2. I did a restore of a database to a different directory.
3. I attempted to attach to this database only to discover that one of the
log files was not getting backed up. (there were three different log files)
4. I was not permitted to attach.
YIKES!
Had this been a REAL EMERGENCY, would I have lost everything just because of
one log file missing? Or is there some way to force the attachment, ignoring
the missing file?
--Zorpie
In some cases, you can attach without the log file and SQL Server will create a log file for you. It
requires some things in place:
Db has only one log file
Cleanly detached
Probably some other stuff (see documentation for sp_attach_single_file_db).
IF the database doesn't attach, your option is to call MS Support and see if they have any emergency
rescue options. It will probably lead to a possibly both logically and physically inconsistent
database. So this is not something you want to do. Probably to dig up your most recent backup
instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of the
> log files was not getting backed up. (there were three different log files)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because of
> one log file missing? Or is there some way to force the attachment, ignoring
> the missing file?
> --Zorpie
|||I would go with Tibor's suggestions but you may be interested in this as
well:
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
Andrew J. Kelly SQL MVP
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
> the
> log files was not getting backed up. (there were three different log
> files)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
> of
> one log file missing? Or is there some way to force the attachment,
> ignoring
> the missing file?
> --Zorpie
|||"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
the
> log files was not getting backed up. (there were three different log
files)
> 4. I was not permitted to attach.
If a log file is missing, it sounds like they're not using native SQL Server
backup commands. Do you know if there's a particular reason for this?
Generally using the native SQL Server backup commands are the best way to do
it. They will insure all logs are backed up and insure DB integrity.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
of
> one log file missing? Or is there some way to force the attachment,
ignoring
> the missing file?
> --Zorpie
1. I use an off-site service to backup my SQL database.
2. I did a restore of a database to a different directory.
3. I attempted to attach to this database only to discover that one of the
log files was not getting backed up. (there were three different log files)
4. I was not permitted to attach.
YIKES!
Had this been a REAL EMERGENCY, would I have lost everything just because of
one log file missing? Or is there some way to force the attachment, ignoring
the missing file?
--Zorpie
In some cases, you can attach without the log file and SQL Server will create a log file for you. It
requires some things in place:
Db has only one log file
Cleanly detached
Probably some other stuff (see documentation for sp_attach_single_file_db).
IF the database doesn't attach, your option is to call MS Support and see if they have any emergency
rescue options. It will probably lead to a possibly both logically and physically inconsistent
database. So this is not something you want to do. Probably to dig up your most recent backup
instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of the
> log files was not getting backed up. (there were three different log files)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because of
> one log file missing? Or is there some way to force the attachment, ignoring
> the missing file?
> --Zorpie
|||I would go with Tibor's suggestions but you may be interested in this as
well:
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
Andrew J. Kelly SQL MVP
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
> the
> log files was not getting backed up. (there were three different log
> files)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
> of
> one log file missing? Or is there some way to force the attachment,
> ignoring
> the missing file?
> --Zorpie
|||"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
the
> log files was not getting backed up. (there were three different log
files)
> 4. I was not permitted to attach.
If a log file is missing, it sounds like they're not using native SQL Server
backup commands. Do you know if there's a particular reason for this?
Generally using the native SQL Server backup commands are the best way to do
it. They will insure all logs are backed up and insure DB integrity.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
of
> one log file missing? Or is there some way to force the attachment,
ignoring
> the missing file?
> --Zorpie
Attaching a database, but missing a file
The situation is this:
1. I use an off-site service to backup my SQL database.
2. I did a restore of a database to a different directory.
3. I attempted to attach to this database only to discover that one of the
log files was not getting backed up. (there were three different log files)
4. I was not permitted to attach.
YIKES!
Had this been a REAL EMERGENCY, would I have lost everything just because of
one log file missing? Or is there some way to force the attachment, ignorin
g
the missing file?
--ZorpieIn some cases, you can attach without the log file and SQL Server will creat
e a log file for you. It
requires some things in place:
Db has only one log file
Cleanly detached
Probably some other stuff (see documentation for sp_attach_single_file_db).
IF the database doesn't attach, your option is to call MS Support and see if
they have any emergency
rescue options. It will probably lead to a possibly both logically and physi
cally inconsistent
database. So this is not something you want to do. Probably to dig up your m
ost recent backup
instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of th
e
> log files was not getting backed up. (there were three different log file
s)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
of
> one log file missing? Or is there some way to force the attachment, ignor
ing
> the missing file?
> --Zorpie|||I would go with Tibor's suggestions but you may be interested in this as
well:
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
Andrew J. Kelly SQL MVP
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
> the
> log files was not getting backed up. (there were three different log
> files)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
> of
> one log file missing? Or is there some way to force the attachment,
> ignoring
> the missing file?
> --Zorpie|||"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
the
> log files was not getting backed up. (there were three different log
files)
> 4. I was not permitted to attach.
If a log file is missing, it sounds like they're not using native SQL Server
backup commands. Do you know if there's a particular reason for this?
Generally using the native SQL Server backup commands are the best way to do
it. They will insure all logs are backed up and insure DB integrity.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
of
> one log file missing? Or is there some way to force the attachment,
ignoring
> the missing file?
> --Zorpie
1. I use an off-site service to backup my SQL database.
2. I did a restore of a database to a different directory.
3. I attempted to attach to this database only to discover that one of the
log files was not getting backed up. (there were three different log files)
4. I was not permitted to attach.
YIKES!
Had this been a REAL EMERGENCY, would I have lost everything just because of
one log file missing? Or is there some way to force the attachment, ignorin
g
the missing file?
--ZorpieIn some cases, you can attach without the log file and SQL Server will creat
e a log file for you. It
requires some things in place:
Db has only one log file
Cleanly detached
Probably some other stuff (see documentation for sp_attach_single_file_db).
IF the database doesn't attach, your option is to call MS Support and see if
they have any emergency
rescue options. It will probably lead to a possibly both logically and physi
cally inconsistent
database. So this is not something you want to do. Probably to dig up your m
ost recent backup
instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of th
e
> log files was not getting backed up. (there were three different log file
s)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
of
> one log file missing? Or is there some way to force the attachment, ignor
ing
> the missing file?
> --Zorpie|||I would go with Tibor's suggestions but you may be interested in this as
well:
http://www.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
Andrew J. Kelly SQL MVP
"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
> the
> log files was not getting backed up. (there were three different log
> files)
> 4. I was not permitted to attach.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
> of
> one log file missing? Or is there some way to force the attachment,
> ignoring
> the missing file?
> --Zorpie|||"Zorpiedoman" <nowheremane@.beatles.com> wrote in message
news:EE169C40-B180-4470-9572-B0272838A045@.microsoft.com...
> The situation is this:
> 1. I use an off-site service to backup my SQL database.
> 2. I did a restore of a database to a different directory.
> 3. I attempted to attach to this database only to discover that one of
the
> log files was not getting backed up. (there were three different log
files)
> 4. I was not permitted to attach.
If a log file is missing, it sounds like they're not using native SQL Server
backup commands. Do you know if there's a particular reason for this?
Generally using the native SQL Server backup commands are the best way to do
it. They will insure all logs are backed up and insure DB integrity.
> YIKES!
> Had this been a REAL EMERGENCY, would I have lost everything just because
of
> one log file missing? Or is there some way to force the attachment,
ignoring
> the missing file?
> --Zorpie
Subscribe to:
Posts (Atom)