DR - Restore Master

I am doing a DR test and I am running the command sqlservr.exe -c -m to put
the server in single user mode before I restore the Master database. My ques
tion - How long does this command normally run?
Thanks,
JessThe command prompt window will sit there, it is running the sql server =
process, so it stays until you shut it down (either by CTRL + C, or a =
shutdown command from osql or Query analyser)
Mike John
"Jess" <anonymous@.discussions.microsoft.com> wrote in message =
news:65EAAC26-7D01-4B7A-8357-A88735A63000@.microsoft.com...
> I am doing a DR test and I am running the command sqlservr.exe -c -m =
to put the server in single user mode before I restore the Master =
database. My question - How long does this command normally run?=20
>=20
> Thanks,
> Jess|||sqlservr.exe is the service. When you run it with a switch in a command
window it will keep running as long as SQL Server is running in single user
mode.
Christian Smith
"Jess" <anonymous@.discussions.microsoft.com> wrote in message
news:65EAAC26-7D01-4B7A-8357-A88735A63000@.microsoft.com...
> I am doing a DR test and I am running the command sqlservr.exe -c -m to
put the server in single user mode before I restore the Master database. My
question - How long does this command normally run?
> Thanks,
> Jess|||Great, thanks guys! I logged into QA to see if it was in single user mode an
d it was but not sure if there was any way the command prompt would give any
notification that it was in single user mode. My sits there for awhile afte
r Starting up database 'msd
b'.

DR - Master db restore

I am doing a DR test and running the command sqlservr.exe -c -m to get the s
erver in single user mode. How long does this command normally run?
Thanks,
JessI am assuming you are running this in a command window. If so, it will run
until you hit Ctrl-C and answer yes to the prompt. When you start SQL
server this way it is starting as an application.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi,
As soon you run sqlservr.exe -c -m from command prompt the sql server starts
in Single user mode. One user can login to SQL server and perform all the
activities. After that you can close the sqlservr.exe which is running from
command prompt using CNTL and C key.
Alternately,
You can start the SQL server in Single user mode using
COntrol panel -- Admin tools -- Services , Select SQL server Services and in
parameters you can put -m and click the start button. This will also start
the SQL server in single user mode. Once the actitivty is completed you can
just restart the service to make it Multiuser.
Thanks
Hari
MCDBA
"Jess" <anonymous@.discussions.microsoft.com> wrote in message
news:3FAD59F2-DA04-490D-B131-EE5D92F6AB9C@.microsoft.com...
> I am doing a DR test and running the command sqlservr.exe -c -m to get the
server in single user mode. How long does this command normally run?
> Thanks,
> Jess|||When you run this from the command prompt, it should take a few seconds to
come up and while it is coming up, it will be writing things to the console
( command prompt), the last message shoujld be 'recovery complete' I think,
and then it stops writing...
The system is not hung.. the server is simply up and running. The server is
running as long as the command window is open... Go ahead and use Query
Analyzer or whatever to do your work, Then Ctrl-C on the command prompt
window to bring SQL back down.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Jess" <anonymous@.discussions.microsoft.com> wrote in message
news:3FAD59F2-DA04-490D-B131-EE5D92F6AB9C@.microsoft.com...
> I am doing a DR test and running the command sqlservr.exe -c -m to get the
server in single user mode. How long does this command normally run?
> Thanks,
> Jess|||Thanks everyone!sql

DR - Master db restore

I am doing a DR test and running the command sqlservr.exe -c -m to get the server in single user mode. How long does this command normally run
Thanks
JessI am assuming you are running this in a command window. If so, it will run
until you hit Ctrl-C and answer yes to the prompt. When you start SQL
server this way it is starting as an application.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi,
As soon you run sqlservr.exe -c -m from command prompt the sql server starts
in Single user mode. One user can login to SQL server and perform all the
activities. After that you can close the sqlservr.exe which is running from
command prompt using CNTL and C key.
Alternately,
You can start the SQL server in Single user mode using
COntrol panel -- Admin tools -- Services , Select SQL server Services and in
parameters you can put -m and click the start button. This will also start
the SQL server in single user mode. Once the actitivty is completed you can
just restart the service to make it Multiuser.
Thanks
Hari
MCDBA
"Jess" <anonymous@.discussions.microsoft.com> wrote in message
news:3FAD59F2-DA04-490D-B131-EE5D92F6AB9C@.microsoft.com...
> I am doing a DR test and running the command sqlservr.exe -c -m to get the
server in single user mode. How long does this command normally run?
> Thanks,
> Jess|||When you run this from the command prompt, it should take a few seconds to
come up and while it is coming up, it will be writing things to the console
( command prompt), the last message shoujld be 'recovery complete' I think,
and then it stops writing...
The system is not hung.. the server is simply up and running. The server is
running as long as the command window is open... Go ahead and use Query
Analyzer or whatever to do your work, Then Ctrl-C on the command prompt
window to bring SQL back down.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.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
"Jess" <anonymous@.discussions.microsoft.com> wrote in message
news:3FAD59F2-DA04-490D-B131-EE5D92F6AB9C@.microsoft.com...
> I am doing a DR test and running the command sqlservr.exe -c -m to get the
server in single user mode. How long does this command normally run?
> Thanks,
> Jess|||Thanks everyone!

Doubts about Transaction Log

Hello to Everybody!
I have a doubts about Transaction Log. What's happen when I execute the
follow command
BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH TRUNCATE_ONLY
So, When should I use this command to avoid fill up the log?
Thanks a Lot
Juliano HortaDon't use it. Either have the db in simple recovery mode. Or do regular transaction log backups (the
log is emptied when you do a log backup).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Juliano H via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:541010E0E3B40@.SQLMonster.com...
> Hello to Everybody!
> I have a doubts about Transaction Log. What's happen when I execute the
> follow command
> BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH TRUNCATE_ONLY
> So, When should I use this command to avoid fill up the log?
> Thanks a Lot
> Juliano Horta|||Only use this if you do not need to be recoverable via trans log, if so
maybe you should just use simple recovery mode
Below is from BOL
Note If backing up the log does not appear to truncate most of the log, an
old open transaction may exist in the log. Log space can be monitored with
DBCC SQLPERF (LOGSPACE). For more information, see Transaction Log Backups.
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and
truncates the log. This option frees space. Specifying a backup device is
unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY
are synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
recorded in the log are not recoverable. For recovery purposes, immediately
execute BACKUP DATABASE.
"Juliano H via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:541010E0E3B40@.SQLMonster.com...
> Hello to Everybody!
> I have a doubts about Transaction Log. What's happen when I execute the
> follow command
> BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH
> TRUNCATE_ONLY
> So, When should I use this command to avoid fill up the log?
> Thanks a Lot
> Juliano Horta|||Hi,
To add on to Tibor and David; Go ahead with SIMPLE recovery model if you do
not require a point in time recovery.
There is no difference between NO_LOG and TRUNCATE_ONLY. Both are same and
will truncate the inactive
portion of the log.
Thanks
Hari
SQL Server MVP
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:uYKNlZKtFHA.3080@.TK2MSFTNGP15.phx.gbl...
> Only use this if you do not need to be recoverable via trans log, if so
> maybe you should just use simple recovery mode
>
> Below is from BOL
>
> Note If backing up the log does not appear to truncate most of the log,
> an old open transaction may exist in the log. Log space can be monitored
> with DBCC SQLPERF (LOGSPACE). For more information, see Transaction Log
> Backups.
>
> NO_LOG | TRUNCATE_ONLY
> Removes the inactive part of the log without making a backup copy of it
> and truncates the log. This option frees space. Specifying a backup device
> is unnecessary because the log backup is not saved. NO_LOG and
> TRUNCATE_ONLY are synonyms.
> After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes
> recorded in the log are not recoverable. For recovery purposes,
> immediately execute BACKUP DATABASE.
>
> "Juliano H via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
> news:541010E0E3B40@.SQLMonster.com...
>> Hello to Everybody!
>> I have a doubts about Transaction Log. What's happen when I execute the
>> follow command
>> BACKUP LOG [database] WITH NO_LOG ou BACKUP LOG [database] WITH
>> TRUNCATE_ONLY
>> So, When should I use this command to avoid fill up the log?
>> Thanks a Lot
>> Juliano Horta
>

Double quotes in error messages!

I have a problem when trying to display an error message from sql server 2005 on a web page with an alert (javascript command).

The Sql server 2005 returns a message like:

Insert statement conflicted with foreign key constraint "bla bla". The conflict occured indatabase "databasename", table "tablename", column 'columnname'.

In sql server 2000 the error message is the same except all names (constraint, database, table) is in single quotation marks; just like the columnname in the above example.

Is it a configurable issue on the sql server. I would prefer not to solve this issue on a number of different web pages!

Thanks in advance.

Futte

hi,

you should set this:

SET QUOTED_IDENTIFIER { ON | OFF }

When on, then identifiers can be delimited by double quotation marks.

Regards,

Janos

|||

I all ready tried that without any luck.......

I want to avoid the double quotation marks.

Thanks though

Futte

|||

If you wish to 'embed' single quotes in your message, you need to double them up -use two single quotes in each place of one single quote.)

Try this:

SELECT 'This is my ''message'' with quotes.'

|||

I think my question might be a little misunderstood.

My question is not about query syntax in sql regarding single or double quotation marks or two single quotation marks.

It's about the error message sql server is returning. I suddenly experienced that after upgrading sql server from 2000 to 2005 the response from sql 2005 contained double quotation marks around all object names except for one; namely the column name:

2000:

Insert statement conflicted with foreign key constraint 'constraint name'. The conflict occurred in database 'database name', table 'table name', column 'column name'.

2005:

Insert statement conflicted with foreign key constraint "constraint name". The conflict occurred in database "database name", table "table name", column 'column name'.

This is a problem when displaying the error message on a web page with javascript (alert). So if it's a configurable issue on the sql server where is it set? Otherwise I have to handle this error message somewhere else replacing the double quotes with single quotes.

Kind Regards

Futte

|||

Thanks for the clarification. I better understand the situation.

As far as I am aware, there is no user configurable setting to alter the presentation of the error message.

Double inserts during snapshot posting of transactional replication

Hi,
So, I have setup transactional replication, uses the initial snapshot. When
the snapshot is being posted, it runs the same command, and causing it to
fail on the primary key constraint.
Has anyone seen this before?
Gary,
please can you post up the error message.
Also, are you doing a nosync subscription or is it a full initialization?
Regards,
Paul Ibison

DOS dir command info in a table?

Hello!
I am trying to capture all the infromation that DOS dir command
generates and stores it into a table called #DirectoryInfo. See below:
create table #DirectoryInfo (
dir varchar(255),
date datetime NULL,
size int,
name varchar(255),
type char(9))
insert #DirectoryInfo exec xp_cmdshell 'dir c:\MyDir\File1.txt'
I am getting this error message:
Server: Msg 213, Level 16, State 7, Procedure xp_cmdshell, Line 11
Insert Error: Column name or number of supplied values does not match
table definition.
what columns I am missing here? or am I am defining more columns than
expected? Thanks for your help.
This is what I get when I do a DIR command for c:\file1.txt
Volume in drive C is Dell Server
Volume Serial Number is XXXX-YYY
NULL
Directory of c:\MyDir
NULL
10/18/2005 10:05a 4 File1.txt
1 File(s) 4 bytes
0 Dir(s) 10,569,293,824 bytes free
Any help would be appreciated! Thanks!
*** Sent via Developersdex http://www.examnotes.net ***xp_cmdshell returns data in a single nvarchar(255) column. (see BOL
entry for more details)
Test Test wrote:

>Hello!
>I am trying to capture all the infromation that DOS dir command
>generates and stores it into a table called #DirectoryInfo. See below:
>create table #DirectoryInfo (
>dir varchar(255),
>date datetime NULL,
>size int,
>name varchar(255),
>type char(9))
>insert #DirectoryInfo exec xp_cmdshell 'dir c:\MyDir\File1.txt'
>I am getting this error message:
>Server: Msg 213, Level 16, State 7, Procedure xp_cmdshell, Line 11
>Insert Error: Column name or number of supplied values does not match
>table definition.
>what columns I am missing here? or am I am defining more columns than
>expected? Thanks for your help.
>This is what I get when I do a DIR command for c:\file1.txt
>
> Volume in drive C is Dell Server
> Volume Serial Number is XXXX-YYY
>NULL
> Directory of c:\MyDir
>NULL
>10/18/2005 10:05a 4 File1.txt
> 1 File(s) 4 bytes
> 0 Dir(s) 10,569,293,824 bytes free
>
>Any help would be appreciated! Thanks!
>
>*** Sent via Developersdex http://www.examnotes.net ***
>|||Try:
CREATE TABLE DIR
(DIRCOL NVARCHAR(255))
GO
INSERT DIR
EXEC MASTER..XP_CMDSHELL 'DIR C:'
GO
SELECT DIRCOL FROM DIR
HTH
Jerry
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:O7yrk1O1FHA.3256@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I am trying to capture all the infromation that DOS dir command
> generates and stores it into a table called #DirectoryInfo. See below:
> create table #DirectoryInfo (
> dir varchar(255),
> date datetime NULL,
> size int,
> name varchar(255),
> type char(9))
> insert #DirectoryInfo exec xp_cmdshell 'dir c:\MyDir\File1.txt'
> I am getting this error message:
> Server: Msg 213, Level 16, State 7, Procedure xp_cmdshell, Line 11
> Insert Error: Column name or number of supplied values does not match
> table definition.
> what columns I am missing here? or am I am defining more columns than
> expected? Thanks for your help.
> This is what I get when I do a DIR command for c:\file1.txt
>
> Volume in drive C is Dell Server
> Volume Serial Number is XXXX-YYY
> NULL
> Directory of c:\MyDir
> NULL
> 10/18/2005 10:05a 4 File1.txt
> 1 File(s) 4 bytes
> 0 Dir(s) 10,569,293,824 bytes free
>
> Any help would be appreciated! Thanks!
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks Jerry but I was hoping to store information (filename, extension,
size, datetime) in separte columns. What I need is FileName and FileSize
information. How do I get it? Should I extract it from DIRCOL from DIR
table? How?
*** Sent via Developersdex http://www.examnotes.net ***|||You could probably grab some of that information by using the undocumented
extended stored procedures in SQL Server however I'd probably use some other
method i.e., VBScript -->WSH --> SQL Table.
HTH
Jerry
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:%23zALTUP1FHA.3504@.TK2MSFTNGP10.phx.gbl...
> Thanks Jerry but I was hoping to store information (filename, extension,
> size, datetime) in separte columns. What I need is FileName and FileSize
> information. How do I get it? Should I extract it from DIRCOL from DIR
> table? How?
>
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||There is an example in this article that does almost exactly what you
are asking, I think:
http://users.drew.edu/skass/sql/TextDriver.htm
Steve Kass
Drew University
Test Test wrote:

>Hello!
>I am trying to capture all the infromation that DOS dir command
>generates and stores it into a table called #DirectoryInfo. See below:
>create table #DirectoryInfo (
>dir varchar(255),
>date datetime NULL,
>size int,
>name varchar(255),
>type char(9))
>insert #DirectoryInfo exec xp_cmdshell 'dir c:\MyDir\File1.txt'
>I am getting this error message:
>Server: Msg 213, Level 16, State 7, Procedure xp_cmdshell, Line 11
>Insert Error: Column name or number of supplied values does not match
>table definition.
>what columns I am missing here? or am I am defining more columns than
>expected? Thanks for your help.
>This is what I get when I do a DIR command for c:\file1.txt
>
> Volume in drive C is Dell Server
> Volume Serial Number is XXXX-YYY
>NULL
> Directory of c:\MyDir
>NULL
>10/18/2005 10:05a 4 File1.txt
> 1 File(s) 4 bytes
> 0 Dir(s) 10,569,293,824 bytes free
>
>Any help would be appreciated! Thanks!
>
>*** Sent via Developersdex http://www.examnotes.net ***
>

Dos commands within a stored procedure

Can I issue a dos command like
Cd\fred
Delete *.*
Thanks in advace for your assistance...use osql.
Mel|||Yes, though xp_cmdshell. Bu carefully consider the security implications.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jim Heavey" <JimHeavey@.discussions.microsoft.com> wrote in message
news:98BB887A-EDD1-4D08-966D-2938733502FD@.microsoft.com...
> Can I issue a dos command like
> Cd\fred
> Delete *.*
> Thanks in advace for your assistance...|||Hi Jim!
Don't forget that xp_cmdshell is disabled in SQL 2005 by default if that's
your platform. You'll need to enable it using the SQL Server Service Surface
Configuration Manager. And your DBA is gonna give you hell for doing so :-)
BTW, you would probably want to do a
del C:\fred\*.*
to chain the commands. And if you run a command shell like 4NT that aliases
commands like del, you may have to make it
*del C:\fred\*.*
Regards,
Jan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OpneAAZWGHA.3328@.TK2MSFTNGP02.phx.gbl...
> Yes, though xp_cmdshell. Bu carefully consider the security implications.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jim Heavey" <JimHeavey@.discussions.microsoft.com> wrote in message
> news:98BB887A-EDD1-4D08-966D-2938733502FD@.microsoft.com...
>

Dos commands within a stored procedure

Can I issue a dos command like
Cd\fred
Delete *.*
Thanks in advace for your assistance...
use osql.
Mel
|||Yes, though xp_cmdshell. Bu carefully consider the security implications.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jim Heavey" <JimHeavey@.discussions.microsoft.com> wrote in message
news:98BB887A-EDD1-4D08-966D-2938733502FD@.microsoft.com...
> Can I issue a dos command like
> Cd\fred
> Delete *.*
> Thanks in advace for your assistance...
|||Hi Jim!
Don't forget that xp_cmdshell is disabled in SQL 2005 by default if that's
your platform. You'll need to enable it using the SQL Server Service Surface
Configuration Manager. And your DBA is gonna give you hell for doing so :-)
BTW, you would probably want to do a
del C:\fred\*.*
to chain the commands. And if you run a command shell like 4NT that aliases
commands like del, you may have to make it
*del C:\fred\*.*
Regards,
Jan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OpneAAZWGHA.3328@.TK2MSFTNGP02.phx.gbl...
> Yes, though xp_cmdshell. Bu carefully consider the security implications.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jim Heavey" <JimHeavey@.discussions.microsoft.com> wrote in message
> news:98BB887A-EDD1-4D08-966D-2938733502FD@.microsoft.com...
>

Dos commands within a stored procedure

Can I issue a dos command like
Cd\fred
Delete *.*
Thanks in advace for your assistance...use osql.
Mel|||Yes, though xp_cmdshell. Bu carefully consider the security implications.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jim Heavey" <JimHeavey@.discussions.microsoft.com> wrote in message
news:98BB887A-EDD1-4D08-966D-2938733502FD@.microsoft.com...
> Can I issue a dos command like
> Cd\fred
> Delete *.*
> Thanks in advace for your assistance...|||Hi Jim!
Don't forget that xp_cmdshell is disabled in SQL 2005 by default if that's
your platform. You'll need to enable it using the SQL Server Service Surface
Configuration Manager. And your DBA is gonna give you hell for doing so :-)
BTW, you would probably want to do a
del C:\fred\*.*
to chain the commands. And if you run a command shell like 4NT that aliases
commands like del, you may have to make it
*del C:\fred\*.*
Regards,
Jan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OpneAAZWGHA.3328@.TK2MSFTNGP02.phx.gbl...
> Yes, though xp_cmdshell. Bu carefully consider the security implications.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jim Heavey" <JimHeavey@.discussions.microsoft.com> wrote in message
> news:98BB887A-EDD1-4D08-966D-2938733502FD@.microsoft.com...
>> Can I issue a dos command like
>> Cd\fred
>> Delete *.*
>> Thanks in advace for your assistance...
>

dos command to copy between 2 domains

anybody have the syntax floating around their heads to use DOS copy between 2 domains.
i think it is something like so ...
copy C:\PathToMySQLServer\MSSQL.1\MSSQL\Backup\ReportSe rver\* remote_host=\\10.0.0.0\backup" myusername@.mydomain.com;mypasswordare the ip addresses known to each other. You could invoke robocopy from the cmd line. robocopy is a m$oft tool ... http://www.microsoft.com/technet/technetmag/issues/2006/11/UtilitySpotlight/|||yeah i just talked that over with the boss.|||My first thought would have been to try to use the runas command, but I have never tried it for this sort of thing.

Does this log backup overwrite?

The following command is run every 5 minutes:
BACKUP LOG [Skinstore] TO [Skinstore-Log-Diff] WITH INIT , NOUNLOAD , NAME = N''Skinstore Log Differential'', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
I think it is overwriting the TRN file every time.Yes -- the INIT option makes it overwrite.
--
Adam Machanic
SQL Server MVP - http://sqlblog.com
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
"Jay" <nospam@.nospam.org> wrote in message
news:%23bXzE9l6HHA.3264@.TK2MSFTNGP02.phx.gbl...
> The following command is run every 5 minutes:
> BACKUP LOG [Skinstore] TO [Skinstore-Log-Diff] WITH INIT , NOUNLOAD , NAME
> = N''Skinstore Log Differential'', NOSKIP , STATS = 10, NOFORMAT ,
> NO_TRUNCATE
> I think it is overwriting the TRN file every time.
>|||Psssst! Wrong answer Hans!
I was really hoping to be told I'm a idiot and didn't RTFM right.
Thank you sir.
"Adam Machanic" <amachanic@.IHATESPAMgmail.com> wrote in message
news:BEBE0224-CE84-4872-A44C-A77F784A57EC@.microsoft.com...
> Yes -- the INIT option makes it overwrite.
> --
> Adam Machanic
> SQL Server MVP - http://sqlblog.com
> Author, "Expert SQL Server 2005 Development"
> http://www.apress.com/book/bookDisplay.html?bID=10220
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:%23bXzE9l6HHA.3264@.TK2MSFTNGP02.phx.gbl...
>> The following command is run every 5 minutes:
>> BACKUP LOG [Skinstore] TO [Skinstore-Log-Diff] WITH INIT , NOUNLOAD ,
>> NAME = N''Skinstore Log Differential'', NOSKIP , STATS = 10, NOFORMAT ,
>> NO_TRUNCATE
>> I think it is overwriting the TRN file every time.
>|||You should really think about generating backups with a unique filename each
time. Appending to a single file can get pretty ugly since you can't delete
individual backups in the file. It's all or nothing.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jay" <nospam@.nospam.org> wrote in message
news:%23hG$XGm6HHA.3900@.TK2MSFTNGP02.phx.gbl...
> Psssst! Wrong answer Hans!
> I was really hoping to be told I'm a idiot and didn't RTFM right.
> Thank you sir.
> "Adam Machanic" <amachanic@.IHATESPAMgmail.com> wrote in message
> news:BEBE0224-CE84-4872-A44C-A77F784A57EC@.microsoft.com...
>> Yes -- the INIT option makes it overwrite.
>> --
>> Adam Machanic
>> SQL Server MVP - http://sqlblog.com
>> Author, "Expert SQL Server 2005 Development"
>> http://www.apress.com/book/bookDisplay.html?bID=10220
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:%23bXzE9l6HHA.3264@.TK2MSFTNGP02.phx.gbl...
>> The following command is run every 5 minutes:
>> BACKUP LOG [Skinstore] TO [Skinstore-Log-Diff] WITH INIT , NOUNLOAD ,
>> NAME = N''Skinstore Log Differential'', NOSKIP , STATS = 10, NOFORMAT ,
>> NO_TRUNCATE
>> I think it is overwriting the TRN file every time.
>>
>|||Actually, I've been here a month now and had initially only verified that
backups were being done. Why so little? Because, no one would ever overwrite
their log backup without having backed it up, or archived it first ... would
they?
I have since spoken to the guy that set it up (my boss) and he said he
thought that he was making a complete copy of the .ldf changes for the day
each time he wrote to the .trn. I literally had to stop myself when I heard
my tone in replying to him. Not a good idea to speak to your boss like
you're talking to an idiot.
Anyway, when drive space is available, I will be changing to my own backup
program that was based on RealSQLGuy's backup program. The only problem with
the program is that it assumes the job that puts it to tape will also be
removing old files. A situation that is not the case here.
Thanks,
Jay
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23tB8RCn6HHA.5012@.TK2MSFTNGP02.phx.gbl...
> You should really think about generating backups with a unique filename
> each time. Appending to a single file can get pretty ugly since you can't
> delete individual backups in the file. It's all or nothing.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jay" <nospam@.nospam.org> wrote in message
> news:%23hG$XGm6HHA.3900@.TK2MSFTNGP02.phx.gbl...
>> Psssst! Wrong answer Hans!
>> I was really hoping to be told I'm a idiot and didn't RTFM right.
>> Thank you sir.
>> "Adam Machanic" <amachanic@.IHATESPAMgmail.com> wrote in message
>> news:BEBE0224-CE84-4872-A44C-A77F784A57EC@.microsoft.com...
>> Yes -- the INIT option makes it overwrite.
>> --
>> Adam Machanic
>> SQL Server MVP - http://sqlblog.com
>> Author, "Expert SQL Server 2005 Development"
>> http://www.apress.com/book/bookDisplay.html?bID=10220
>>
>> "Jay" <nospam@.nospam.org> wrote in message
>> news:%23bXzE9l6HHA.3264@.TK2MSFTNGP02.phx.gbl...
>> The following command is run every 5 minutes:
>> BACKUP LOG [Skinstore] TO [Skinstore-Log-Diff] WITH INIT , NOUNLOAD ,
>> NAME = N''Skinstore Log Differential'', NOSKIP , STATS = 10, NOFORMAT ,
>> NO_TRUNCATE
>> I think it is overwriting the TRN file every time.
>>
>>
>|||One possible way is to base the backups on weekday, day in month or similar. Take the easy route,
day of week. You have a Monday file, a Tuesday file etc. The first backup of the day, you do INIT,
then the rest of the backups of the day you do NONIT. You now have a tail of 6 backup files
(excluding the current day). This will cut down number of physical files (nice of you have lots of
databases and frequent log backups). However, some SQL persons aren't that familiar with several
backups on same file, so they don't understand the WITH FILE = option of the RESTORE command...
Pretty much a matter of taste. And, remember that the code you write need to be understood and
maintained by somebody... ;-).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospam@.nospam.org> wrote in message news:ui%23tvhn6HHA.5984@.TK2MSFTNGP04.phx.gbl...
> Actually, I've been here a month now and had initially only verified that backups were being done.
> Why so little? Because, no one would ever overwrite their log backup without having backed it up,
> or archived it first ... would they?
> I have since spoken to the guy that set it up (my boss) and he said he thought that he was making
> a complete copy of the .ldf changes for the day each time he wrote to the .trn. I literally had to
> stop myself when I heard my tone in replying to him. Not a good idea to speak to your boss like
> you're talking to an idiot.
> Anyway, when drive space is available, I will be changing to my own backup program that was based
> on RealSQLGuy's backup program. The only problem with the program is that it assumes the job that
> puts it to tape will also be removing old files. A situation that is not the case here.
> Thanks,
> Jay
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23tB8RCn6HHA.5012@.TK2MSFTNGP02.phx.gbl...
>> You should really think about generating backups with a unique filename each time. Appending to
>> a single file can get pretty ugly since you can't delete individual backups in the file. It's all
>> or nothing.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:%23hG$XGm6HHA.3900@.TK2MSFTNGP02.phx.gbl...
>> Psssst! Wrong answer Hans!
>> I was really hoping to be told I'm a idiot and didn't RTFM right.
>> Thank you sir.
>> "Adam Machanic" <amachanic@.IHATESPAMgmail.com> wrote in message
>> news:BEBE0224-CE84-4872-A44C-A77F784A57EC@.microsoft.com...
>> Yes -- the INIT option makes it overwrite.
>> --
>> Adam Machanic
>> SQL Server MVP - http://sqlblog.com
>> Author, "Expert SQL Server 2005 Development"
>> http://www.apress.com/book/bookDisplay.html?bID=10220
>>
>> "Jay" <nospam@.nospam.org> wrote in message news:%23bXzE9l6HHA.3264@.TK2MSFTNGP02.phx.gbl...
>> The following command is run every 5 minutes:
>> BACKUP LOG [Skinstore] TO [Skinstore-Log-Diff] WITH INIT , NOUNLOAD , NAME = N''Skinstore Log
>> Differential'', NOSKIP , STATS = 10, NOFORMAT , NO_TRUNCATE
>> I think it is overwriting the TRN file every time.
>>
>>
>

does the transaction log keep track of who did what?

Hello Guys,

Does any one know if the transaction log keep track of the user who did the operation or not?

coz i tried the command which have been written in the following post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1054474&SiteID=1

but it didn't show the user who do the operation

MagedSalah wrote:

Hello Guys,

Does any one know if the transaction log keep track of the user who did the operation or not?

coz i tried the command which have been written in the following post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1054474&SiteID=1

but it didn't show the user who do the operation

Yes...the information is logged. If the command you tried was DBCC LOGINFO...it won't show you who did what. As mentioned in the thread, you need a third party tool to view who did what command. Using DBCC LOGINFO isn't going to help you much in analyzing transactions and activity based on what is in the log. It's used more for checking VLFs and statuses.

-Sue
|||

It is best to NOT rely upon 'undocumented' functionality.

Using Profiler, or one of the many third party log tools, you can determine who caused an action. (Profiler must be running in advance, whereas many of the third party tools can extract that infomation from the logs. Here are some of the options:

Lumigent SQL Server log explorer
ApexSQL Log Navigator
Log P.I.
SQL Log Rescue from red-gate