backup error - single user mode needed?

Hi Everyone,
One of my clients is running SQL 2000 SP4 and the Data Maintenance Plan
logs shows a log with...
Database master: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
SQL Server Driver][SQL Server]Repair statement not processed. Database
needs to be in single user mode.
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
processed. Database needs to be in single user mode.
** Execution Time: 0 hrs, 0 mins, 1 secs **
...
[5] Database master: Database Backup...
The backup was not performed since data verification errors were found.

There are similar messages for databases model and msdb.
Questions:
- should master, model and msdb be included in the daily backup?
- how does one get into single user mode and repair this?
Thanks
Richard
Hi,
In your maintenance plan, uncheck the Attempt to repair minor problems
checkbox in Integrity tab.
With that checkbox unchecked, integrity checks will be still done on the
databases and as a good practice verify your maintenance logs
for any integroty errors. If there is any error you could manually correct
it.
Thanks
Hari
SQL Server MVP
"Richard Fagen" <no_spam@.aol.com> wrote in message
news:O3JolEukFHA.2792@.TK2MSFTNGP10.phx.gbl...
> Hi Everyone,
> One of my clients is running SQL 2000 SP4 and the Data Maintenance Plan
> logs shows a log with...
> --
> Database master: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Repair statement not processed. Database
> needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> ...
> [5] Database master: Database Backup...
> The backup was not performed since data verification errors were
> found.
>
> --
> There are similar messages for databases model and msdb.
> Questions:
> - should master, model and msdb be included in the daily backup?
> - how does one get into single user mode and repair this?
> Thanks
> Richard
|||hi Richard,
Richard Fagen wrote:
is the disk damaged?

> Questions:
> - should master, model and msdb be included in the daily backup?
usually not.. only when server modifications occur, like implementing new
jobs, alerts, logins and so on...

> - how does one get into single user mode and repair this?
http://msdn.microsoft.com/library/de...start_4nhh.asp
http://msdn.microsoft.com/library/de...kprst_4g4w.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea Montanari wrote:
> hi Richard,
> Richard Fagen wrote:
> is the disk damaged?
always forget the Maintenance Plan is that intrusive (and bugged ) ..
always prefer self written jobs :D
yep.. Hari is right
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Hari,
I'll uncheck the box and try it again.
I use SQL as part of SBS 2000/2003 and that seems to go against what
I've read in books. All the books say be sure to check that box
However, since your both SQL MVPs and Andrea has helped me out before,
I'll go with your recommendations.
Richard
p.s. the one who says to check it, Harry Brelsford, is an MVP too, but
of SBS not SQL
Hari Prasad wrote:
> Hi,
> In your maintenance plan, uncheck the Attempt to repair minor problems
> checkbox in Integrity tab.
> With that checkbox unchecked, integrity checks will be still done on the
> databases and as a good practice verify your maintenance logs
> for any integroty errors. If there is any error you could manually correct
> it.
> Thanks
> Hari
> SQL Server MVP
>
> "Richard Fagen" <no_spam@.aol.com> wrote in message
> news:O3JolEukFHA.2792@.TK2MSFTNGP10.phx.gbl...
>
>
|||Hi Andrea,

> is the disk damaged?
Not that I know of. Besides, I remotely logged into a few SBS clients
to test the data maintenance plan with backing up master, model and msdb
and they all had the same error messages.
I guess it wouldn't hurt to have everyone run a scandisk (I try to get
them to do it on their own, but they never remember) and doublecheck.

> usually not.. only when server modifications occur, like implementing new
> jobs, alerts, logins and so on...
I can manually backup master, model and msdb via the Enterprise Manager,
just not via the maintenance plan.
I hope they fix this in SQL 2005

> http://msdn.microsoft.com/library/de...start_4nhh.asp
> http://msdn.microsoft.com/library/de...kprst_4g4w.asp
Thanks for the links
Richard
|||hi Richard,
Richard Fagen wrote:
> Hi Andrea,
> Not that I know of. Besides, I remotely logged into a few SBS clients
> to test the data maintenance plan with backing up master, model and
> msdb and they all had the same error messages.
see Hari post... Maintenance Plan is "intrusive" by default and tryes to
perform activities requiring single user access...

> I guess it wouldn't hurt to have everyone run a scandisk (I try to get
> them to do it on their own, but they never remember) and doublecheck.
but ok. checking disk integrity is always neat :D

> I can manually backup master, model and msdb via the Enterprise
> Manager, just not via the maintenance plan.
again, see Hari answer...

> I hope they fix this in SQL 2005
SQLExpress will not include the Agent, so no Maintenance Plan at all... but
worse, no jobs at all, and that includes both admin/house keeping jobs as
other dataload,dml jobs
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||From Microsoft Article 290622:
BUG: Databse Manintenance Plan on System Databases faile on Intergrity
Check if "Attempt to repair minor problems" is Selected
... The database maintenance plan attempts to place the system databases
in the single user mode in order to execute CkDBRepair. However the
Master and MSDB databases cannot be placed in single user mode because of
constantly running system processes like LAZYWRITER, LOG WRITER, SQL
Agent... The repair step fails to run and the job reports a failure...
There is no fix for tis bug as yet. The workaround is to disable the
"Attempt to repair any minor problems" option in your maintenance plan.
Lyle
In article <O3JolEukFHA.2792@.TK2MSFTNGP10.phx.gbl>, no_spam@.aol.com
says...
> Hi Everyone,
> One of my clients is running SQL 2000 SP4 and the Data Maintenance Plan
> logs shows a log with...
> --
> Database master: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
> SQL Server Driver][SQL Server]Repair statement not processed. Database
> needs to be in single user mode.
> The following errors were found:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Repair statement not
> processed. Database needs to be in single user mode.
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> ...
> [5] Database master: Database Backup...
> The backup was not performed since data verification errors were found.
>
> --
> There are similar messages for databases model and msdb.
> Questions:
> - should master, model and msdb be included in the daily backup?
> - how does one get into single user mode and repair this?
> Thanks
> Richard
>
|||Hi Lyle,
Thanks for the article. Finally, a great explanation
Richard
Lyle wrote:

> From Microsoft Article 290622:
> BUG: Databse Manintenance Plan on System Databases faile on Intergrity
> Check if "Attempt to repair minor problems" is Selected
> ... The database maintenance plan attempts to place the system databases
> in the single user mode in order to execute CkDBRepair. However the
> Master and MSDB databases cannot be placed in single user mode because of
> constantly running system processes like LAZYWRITER, LOG WRITER, SQL
> Agent... The repair step fails to run and the job reports a failure...
> There is no fix for tis bug as yet. The workaround is to disable the
> "Attempt to repair any minor problems" option in your maintenance plan.
> Lyle
sql

Backup error

Hi all,
Sometimes the backup fails on our sqllserver installation.
after this error you find a dumpfile.

the error in the log is:

Executed as user: NT AUTHORITY\SYSTEM. DBCC execut
ion completed. If DBCC printed error messages, con
tact your system administrator. [SQLSTATE 01000] (
Message 2528) BACKUP DATABASE is terminating abno
rmally. [SQLSTATE 42000] (Error 3013) A system as
sertion check has failed. Check the SQL Server err
or log for details [SQLSTATE HY000] (Error 3624)
Location:# opentape.cpp:1230 Expression:# !pNext
!foundMatch SPID:## 125 Process ID:# 192 [SQLSTA
TE 01000] (Error 3624). The step failed.

This is our configuration:
This file is generated by Microsoft SQL Server
version 9.00.2047.00
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.


Computer type is AT/AT COMPATIBLE.
Bios Version is HP - 2
Current time is 12:02:27 01/02/07.
4 Intel x86 level 6, 2666 Mhz processor (s).
Windows NT 5.2 Build 3790 CSD Service Pack1.

Memory
MemoryLoad = 18%
Total Physical = 16383 MB
Available Physical = 13372 MB
Total Page File = 28131 MB
Available Page File = 12568 MB
Total Virtual = 2047 MB
Available Virtual = 301 MB

Please help.

Are you trying to copy the backup to another location?

What is the privilege for the SQL Server Agent account, as I can see you are using Local system account. Try to schedule the backup manually in this case and also check what kind of other jobs are scheduled on SQL Server and also on the operating system side, such as antivirus checks etc.

Backup databases

Hi

I'm trying to setup a back up plan for a number of databases, I initially set up one plan to include all user databases which worked fine or so I thought, when I check them a few days later I noticed that some of the databases were not appearing in the backup set, the only way I could get these to appear is to set the comp level to 90, now when we run certain applications we get an error, when I return the comp level back to 70 then the application works fine, is there a reason I can not back up any database on sql 2005 without it being a comp level 90?

Thanks inadvance

I'm guessing that the maintenance plan you've created uses some feature or syntax which didn't exist in SQL 7.0

Look over the TSQL backup commands in your maintenance plan and verify that the syntax there is compatible with SQL 7.0

|||

Hi thanks for the reply, but its not getting that far, stepping through the wizard,first it asks for the database(s) to back up, the choice being all system databases, or specific databases and I don't see any database that has not been set to comp level 90, so no tsql to check.

Thanks

|||

I see that on my system as well.

Is compatibility level 80 an option for you? Databases with that compatibility level do show up in the Wizard.

I'll check on why 70 databases are excluded, but I suspect that it has to do with what was supported at that version.

Ultimately your best option may be to write a backup script yourself.

|||

Hi

Yes thanks, not sure why I didn't think of that, but setting to comp level 80 does the trick,, thanks!

|||I have encountered the same problem when I setup the maintenance plan with the maintenance plan Wizard. Any help if I cannot set the comp. level to 80? Please advice, thanks!

Backup Database Role\permission in SQL Server 2005

I have 20+ databases on the SQL Cluster. How can I give a user the permission to backup any database without giving sysadmin access ?
I dont want to make the user dbo on all databases either.BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.
If not you could take advantage to EXECUTE AS in this case.|||thanks Satya. I was hoping in SQL 2005, may be they had come up with something better. The only problem with db_backupoperator approach is that every time you add a new database to the server, thethe id needs to be given access to that database.

Backup claims to have run, but there's no file

I have been trying to figure out why my transaction log backup task doesn't
seem to do anything. I changed the user account to SA, and now I see in the
logs that the task did run (no more account errors).
When I View Job History on the task, I see that it ran last night at 5AM as
I expected, and there's no errors. But there's no file!
Any ideas?
Maury
That's not much to go on but is there any chance the db is in Simple
Recovery mode?
Andrew J. Kelly SQL MVP
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:4E2A71B7-814A-4DC8-98B3-0974F1778827@.microsoft.com...
>I have been trying to figure out why my transaction log backup task doesn't
> seem to do anything. I changed the user account to SA, and now I see in
> the
> logs that the task did run (no more account errors).
> When I View Job History on the task, I see that it ran last night at 5AM
> as
> I expected, and there's no errors. But there's no file!
> Any ideas?
> Maury
|||"Andrew J. Kelly" wrote:
> That's not much to go on but is there any chance the db is in Simple
> Recovery mode?
I can't tell you, I'm running '97 and Enterprise Manager doesn't say. Was
this even an option in earlier versions?
It would certainly make sense that this could cause the problem, I never
thought of it likely because I never saw the option while poking about!
After a little googling I note that the SQL Server Agent appearently resets
the server to Simple (assuming my server has this). This might be the problem
right there.
Maury
|||That's SQL Server 7.0 of course, not '97
|||In 7.0 it would not be called Simple mode. It would have Truncate Log on
ChkPoint I believe.
Andrew J. Kelly SQL MVP
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:5BF9ADF5-4465-43C6-B090-36BDF0322629@.microsoft.com...
> That's SQL Server 7.0 of course, not '97
|||"Andrew J. Kelly" wrote:
> In 7.0 it would not be called Simple mode. It would have Truncate Log on
> ChkPoint I believe.
That option I do see. So I will turn this off and keep my fingers crossed.
BTW, what is a checkpoint, and when does it occur?
Maury
|||Look up CHECKPOINT in BooksOnLine for details.
Andrew J. Kelly SQL MVP
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:14EEF517-A29E-40FA-983C-476E721FCD46@.microsoft.com...
> "Andrew J. Kelly" wrote:
> That option I do see. So I will turn this off and keep my fingers crossed.
> BTW, what is a checkpoint, and when does it occur?
> Maury
|||No luck so far...
I checked all of the log and error files (in /LOG), and don't see any
mention of a backup even running. I do, however, see an empty file for the
16th, the last time it ran.
I went into Enterprise Manager and after some poking about managed to find
the internal logs, the ones stored in the table. This did have an entry (so
why didn't the log file?!) for the 16th, which stated that "Backup can not be
performed on this database. This sub task is ignored", and said it
_succeeded_.
Either way, still no backup file!
This shouldn't be this confusing.
Maury
|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:28194502-6F72-4448-935B-E8DAE25CBCEA@.microsoft.com...
> No luck so far...
> I checked all of the log and error files (in /LOG), and don't see any
> mention of a backup even running. I do, however, see an empty file for the
> 16th, the last time it ran.
> I went into Enterprise Manager and after some poking about managed to find
> the internal logs, the ones stored in the table. This did have an entry
(so
> why didn't the log file?!) for the 16th, which stated that "Backup can not
be
> performed on this database. This sub task is ignored", and said it
> _succeeded_.
> Either way, still no backup file!
I missed it, is this a full or transaction log backup?
If it's a transaction log backup either your database may be in SIMPLE
recovery mode or a bulk insert has been done since the last full backup.

> This shouldn't be this confusing.
> Maury
|||"Greg D. Moore (Strider)" wrote:
> I missed it, is this a full or transaction log backup?
Transaction log.

> If it's a transaction log backup either your database may be in SIMPLE
> recovery mode or a bulk insert has been done since the last full backup.
There is no "simple" in 7.0, but I have turned off the confusingly-named
options that seem to imply transaction logs should now work.
The only bulk work we do is SELECT INTO on #temp tables. If THAT is the
cause of the problem, I can't imagine why -- bulks into temp tables should be
ignored for the purpose of transaction logging.
And if that is the cause, what is the solution? Diff backups prior to trans
backups?
Maury

Backup claims to have run, but there's no file

I have been trying to figure out why my transaction log backup task doesn't
seem to do anything. I changed the user account to SA, and now I see in the
logs that the task did run (no more account errors).
When I View Job History on the task, I see that it ran last night at 5AM as
I expected, and there's no errors. But there's no file!
Any ideas?
MauryThat's not much to go on but is there any chance the db is in Simple
Recovery mode?
--
Andrew J. Kelly SQL MVP
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:4E2A71B7-814A-4DC8-98B3-0974F1778827@.microsoft.com...
>I have been trying to figure out why my transaction log backup task doesn't
> seem to do anything. I changed the user account to SA, and now I see in
> the
> logs that the task did run (no more account errors).
> When I View Job History on the task, I see that it ran last night at 5AM
> as
> I expected, and there's no errors. But there's no file!
> Any ideas?
> Maury|||"Andrew J. Kelly" wrote:
> That's not much to go on but is there any chance the db is in Simple
> Recovery mode?
I can't tell you, I'm running '97 and Enterprise Manager doesn't say. Was
this even an option in earlier versions?
It would certainly make sense that this could cause the problem, I never
thought of it likely because I never saw the option while poking about!
After a little googling I note that the SQL Server Agent appearently resets
the server to Simple (assuming my server has this). This might be the problem
right there.
Maury|||That's SQL Server 7.0 of course, not '97|||In 7.0 it would not be called Simple mode. It would have Truncate Log on
ChkPoint I believe.
--
Andrew J. Kelly SQL MVP
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:5BF9ADF5-4465-43C6-B090-36BDF0322629@.microsoft.com...
> That's SQL Server 7.0 of course, not '97|||"Andrew J. Kelly" wrote:
> In 7.0 it would not be called Simple mode. It would have Truncate Log on
> ChkPoint I believe.
That option I do see. So I will turn this off and keep my fingers crossed.
BTW, what is a checkpoint, and when does it occur?
Maury|||Look up CHECKPOINT in BooksOnLine for details.
--
Andrew J. Kelly SQL MVP
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:14EEF517-A29E-40FA-983C-476E721FCD46@.microsoft.com...
> "Andrew J. Kelly" wrote:
>> In 7.0 it would not be called Simple mode. It would have Truncate Log on
>> ChkPoint I believe.
> That option I do see. So I will turn this off and keep my fingers crossed.
> BTW, what is a checkpoint, and when does it occur?
> Maury|||No luck so far...
I checked all of the log and error files (in /LOG), and don't see any
mention of a backup even running. I do, however, see an empty file for the
16th, the last time it ran.
I went into Enterprise Manager and after some poking about managed to find
the internal logs, the ones stored in the table. This did have an entry (so
why didn't the log file?!) for the 16th, which stated that "Backup can not be
performed on this database. This sub task is ignored", and said it
_succeeded_.
Either way, still no backup file!
This shouldn't be this confusing.
Maury|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:28194502-6F72-4448-935B-E8DAE25CBCEA@.microsoft.com...
> No luck so far...
> I checked all of the log and error files (in /LOG), and don't see any
> mention of a backup even running. I do, however, see an empty file for the
> 16th, the last time it ran.
> I went into Enterprise Manager and after some poking about managed to find
> the internal logs, the ones stored in the table. This did have an entry
(so
> why didn't the log file?!) for the 16th, which stated that "Backup can not
be
> performed on this database. This sub task is ignored", and said it
> _succeeded_.
> Either way, still no backup file!
I missed it, is this a full or transaction log backup?
If it's a transaction log backup either your database may be in SIMPLE
recovery mode or a bulk insert has been done since the last full backup.
> This shouldn't be this confusing.
> Maury|||"Greg D. Moore (Strider)" wrote:
> > Either way, still no backup file!
> I missed it, is this a full or transaction log backup?
Transaction log.
> If it's a transaction log backup either your database may be in SIMPLE
> recovery mode or a bulk insert has been done since the last full backup.
There is no "simple" in 7.0, but I have turned off the confusingly-named
options that seem to imply transaction logs should now work.
The only bulk work we do is SELECT INTO on #temp tables. If THAT is the
cause of the problem, I can't imagine why -- bulks into temp tables should be
ignored for the purpose of transaction logging.
And if that is the cause, what is the solution? Diff backups prior to trans
backups?
Maury|||What is the setting of "SELECT INTO / BULKCOPY"? Try turning it off. It doesn't have to be on for
SELECTINTO into temp tables...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message
news:856788B4-5904-4027-89AD-CAFC5275009C@.microsoft.com...
> "Greg D. Moore (Strider)" wrote:
>> > Either way, still no backup file!
>> I missed it, is this a full or transaction log backup?
> Transaction log.
>> If it's a transaction log backup either your database may be in SIMPLE
>> recovery mode or a bulk insert has been done since the last full backup.
> There is no "simple" in 7.0, but I have turned off the confusingly-named
> options that seem to imply transaction logs should now work.
> The only bulk work we do is SELECT INTO on #temp tables. If THAT is the
> cause of the problem, I can't imagine why -- bulks into temp tables should be
> ignored for the purpose of transaction logging.
> And if that is the cause, what is the solution? Diff backups prior to trans
> backups?
> Maury

Backup claims to have run, but there's no file

I have been trying to figure out why my transaction log backup task doesn't
seem to do anything. I changed the user account to SA, and now I see in the
logs that the task did run (no more account errors).
When I View Job History on the task, I see that it ran last night at 5AM as
I expected, and there's no errors. But there's no file!
Any ideas?
MauryThat's not much to go on but is there any chance the db is in Simple
Recovery mode?
Andrew J. Kelly SQL MVP
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:4E2A71B7-814A-4DC8-98B3-0974F1778827@.microsoft.com...
>I have been trying to figure out why my transaction log backup task doesn't
> seem to do anything. I changed the user account to SA, and now I see in
> the
> logs that the task did run (no more account errors).
> When I View Job History on the task, I see that it ran last night at 5AM
> as
> I expected, and there's no errors. But there's no file!
> Any ideas?
> Maury|||"Andrew J. Kelly" wrote:
> That's not much to go on but is there any chance the db is in Simple
> Recovery mode?
I can't tell you, I'm running '97 and Enterprise Manager doesn't say. Was
this even an option in earlier versions?
It would certainly make sense that this could cause the problem, I never
thought of it likely because I never saw the option while poking about!
After a little googling I note that the SQL Server Agent appearently resets
the server to Simple (assuming my server has this). This might be the proble
m
right there.
Maury|||That's SQL Server 7.0 of course, not '97|||In 7.0 it would not be called Simple mode. It would have Truncate Log on
ChkPoint I believe.
Andrew J. Kelly SQL MVP
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:5BF9ADF5-4465-43C6-B090-36BDF0322629@.microsoft.com...
> That's SQL Server 7.0 of course, not '97|||"Andrew J. Kelly" wrote:
> In 7.0 it would not be called Simple mode. It would have Truncate Log on
> ChkPoint I believe.
That option I do see. So I will turn this off and keep my fingers crossed.
BTW, what is a checkpoint, and when does it occur?
Maury|||Look up CHECKPOINT in BooksOnLine for details.
Andrew J. Kelly SQL MVP
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:14EEF517-A29E-40FA-983C-476E721FCD46@.microsoft.com...
> "Andrew J. Kelly" wrote:
> That option I do see. So I will turn this off and keep my fingers crossed.
> BTW, what is a checkpoint, and when does it occur?
> Maury|||No luck so far...
I checked all of the log and error files (in /LOG), and don't see any
mention of a backup even running. I do, however, see an empty file for the
16th, the last time it ran.
I went into Enterprise Manager and after some poking about managed to find
the internal logs, the ones stored in the table. This did have an entry (so
why didn't the log file?!) for the 16th, which stated that "Backup can not b
e
performed on this database. This sub task is ignored", and said it
_succeeded_.
Either way, still no backup file!
This shouldn't be this confusing.
Maury|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:28194502-6F72-4448-935B-E8DAE25CBCEA@.microsoft.com...
> No luck so far...
> I checked all of the log and error files (in /LOG), and don't see any
> mention of a backup even running. I do, however, see an empty file for the
> 16th, the last time it ran.
> I went into Enterprise Manager and after some poking about managed to find
> the internal logs, the ones stored in the table. This did have an entry
(so
> why didn't the log file?!) for the 16th, which stated that "Backup can not
be
> performed on this database. This sub task is ignored", and said it
> _succeeded_.
> Either way, still no backup file!
I missed it, is this a full or transaction log backup?
If it's a transaction log backup either your database may be in SIMPLE
recovery mode or a bulk insert has been done since the last full backup.

> This shouldn't be this confusing.
> Maury|||"Greg D. Moore (Strider)" wrote:
> I missed it, is this a full or transaction log backup?
Transaction log.

> If it's a transaction log backup either your database may be in SIMPLE
> recovery mode or a bulk insert has been done since the last full backup.
There is no "simple" in 7.0, but I have turned off the confusingly-named
options that seem to imply transaction logs should now work.
The only bulk work we do is SELECT INTO on #temp tables. If THAT is the
cause of the problem, I can't imagine why -- bulks into temp tables should b
e
ignored for the purpose of transaction logging.
And if that is the cause, what is the solution? Diff backups prior to trans
backups?
Maury

backup and restore role

I need to restrict somebody to do only the backup and restore over a
database. What server role and database permissions I should give to this
user?
Regards
FeiGive db_owner permissions to this specific database. With db_owner
permissions the user will be able to backup and restore this database.
However, it the database being restore does not exist, this user will
require CREATE DATABASE permissions.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Fei" wrote:
> I need to restrict somebody to do only the backup and restore over a
> database. What server role and database permissions I should give to this
> user?
> Regards
> Fei|||Thank you. What server role should I give? I just tried to a restore with
"server administrator" and "db_owner" on the database, I got the permission
denied msg.
Fei
"Ben Nevarez" wrote:
> Give db_owner permissions to this specific database. With db_owner
> permissions the user will be able to backup and restore this database.
> However, it the database being restore does not exist, this user will
> require CREATE DATABASE permissions.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Fei" wrote:
> > I need to restrict somebody to do only the backup and restore over a
> > database. What server role and database permissions I should give to this
> > user?
> >
> > Regards
> >
> > Fei|||The information I sent below is for SQL Server 2005. In SQL Server 2000
db_owner do not have RESTORE permissions.
Also, in both SQL Server 2000 and 2005, the fixed database role
db_backupoperator has BACKUP but not RESTORE permissions.
Hope this helps,
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ben Nevarez" wrote:
> Give db_owner permissions to this specific database. With db_owner
> permissions the user will be able to backup and restore this database.
> However, it the database being restore does not exist, this user will
> require CREATE DATABASE permissions.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Fei" wrote:
> > I need to restrict somebody to do only the backup and restore over a
> > database. What server role and database permissions I should give to this
> > user?
> >
> > Regards
> >
> > Fei

backup and restore a database

Dear all,
When i backup a database to a device(file) and then restore it into other
computer. The user always use to know the name of the database in order to
restore the database, can i make a backup to user without know the name of
the database to success to restore?Joe
I don't understand. Do you want to pass to BACKUP command a database name as
a parameter?
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:4E97E1F0-DC3E-4994-9F0A-0104F2F26979@.microsoft.com...
> Dear all,
> When i backup a database to a device(file) and then restore it into other
> computer. The user always use to know the name of the database in order to
> restore the database, can i make a backup to user without know the name of
> the database to success to restore?|||You can restore a user database backup to whatever database
name you want. The user doesn't have to specify the original
name of the database that was backed up.
You can find samples of how to do this in books online under
the topic: How to restore a database with a new name
or check the following link:
http://msdn.microsoft.com/library/d...>
kpc_6ng9.asp
-Sue
On Wed, 28 Sep 2005 01:03:03 -0700, Joe
<Joe@.discussions.microsoft.com> wrote:

>Dear all,
>When i backup a database to a device(file) and then restore it into other
>computer. The user always use to know the name of the database in order to
>restore the database, can i make a backup to user without know the name of
>the database to success to restore?

Backup and Job view and execute permissions

Can you please help me to identify which roles or permissions i need to grant
to a user to do the following task without assigning sysadmin role?
1. View and execute SQL Jobs
2. View backup device and its contents
I assigned 'TargetServerRole' role in msdb to a user but it didn't meet the
required permission to perform the above operation.
Using TargetServerRole isn't a documented approach - the
permissions for this role depend on what service pack you
are on.
To view and execute jobs, the user needs to be the job owner
or a member of sysadmins.
Viewing contents of a device executes a restore headeronly
which any user can execute.
-Sue
On Fri, 21 Oct 2005 08:41:05 -0700, "RumulusKyle"
<RumulusKyle@.discussions.microsoft.com> wrote:

>Can you please help me to identify which roles or permissions i need to grant
>to a user to do the following task without assigning sysadmin role?
>1. View and execute SQL Jobs
>2. View backup device and its contents
>I assigned 'TargetServerRole' role in msdb to a user but it didn't meet the
>required permission to perform the above operation.
|||Thanks Sue.
"Sue Hoegemeier" wrote:

> Using TargetServerRole isn't a documented approach - the
> permissions for this role depend on what service pack you
> are on.
> To view and execute jobs, the user needs to be the job owner
> or a member of sysadmins.
> Viewing contents of a device executes a restore headeronly
> which any user can execute.
> -Sue
> On Fri, 21 Oct 2005 08:41:05 -0700, "RumulusKyle"
> <RumulusKyle@.discussions.microsoft.com> wrote:
>
>

Backup and Job view and execute permissions

Can you please help me to identify which roles or permissions i need to grant
to a user to do the following task without assigning sysadmin role?
1. View and execute SQL Jobs
2. View backup device and its contents
I assigned 'TargetServerRole' role in msdb to a user but it didn't meet the
required permission to perform the above operation.Using TargetServerRole isn't a documented approach - the
permissions for this role depend on what service pack you
are on.
To view and execute jobs, the user needs to be the job owner
or a member of sysadmins.
Viewing contents of a device executes a restore headeronly
which any user can execute.
-Sue
On Fri, 21 Oct 2005 08:41:05 -0700, "RumulusKyle"
<RumulusKyle@.discussions.microsoft.com> wrote:
>Can you please help me to identify which roles or permissions i need to grant
>to a user to do the following task without assigning sysadmin role?
>1. View and execute SQL Jobs
>2. View backup device and its contents
>I assigned 'TargetServerRole' role in msdb to a user but it didn't meet the
>required permission to perform the above operation.|||Thanks Sue. :)
"Sue Hoegemeier" wrote:
> Using TargetServerRole isn't a documented approach - the
> permissions for this role depend on what service pack you
> are on.
> To view and execute jobs, the user needs to be the job owner
> or a member of sysadmins.
> Viewing contents of a device executes a restore headeronly
> which any user can execute.
> -Sue
> On Fri, 21 Oct 2005 08:41:05 -0700, "RumulusKyle"
> <RumulusKyle@.discussions.microsoft.com> wrote:
> >Can you please help me to identify which roles or permissions i need to grant
> >to a user to do the following task without assigning sysadmin role?
> >
> >1. View and execute SQL Jobs
> >2. View backup device and its contents
> >
> >I assigned 'TargetServerRole' role in msdb to a user but it didn't meet the
> >required permission to perform the above operation.
>

Backup and Job view and execute permissions

Can you please help me to identify which roles or permissions i need to gran
t
to a user to do the following task without assigning sysadmin role?
1. View and execute SQL Jobs
2. View backup device and its contents
I assigned 'TargetServerRole' role in msdb to a user but it didn't meet the
required permission to perform the above operation.Using TargetServerRole isn't a documented approach - the
permissions for this role depend on what service pack you
are on.
To view and execute jobs, the user needs to be the job owner
or a member of sysadmins.
Viewing contents of a device executes a restore headeronly
which any user can execute.
-Sue
On Fri, 21 Oct 2005 08:41:05 -0700, "RumulusKyle"
<RumulusKyle@.discussions.microsoft.com> wrote:

>Can you please help me to identify which roles or permissions i need to gra
nt
>to a user to do the following task without assigning sysadmin role?
>1. View and execute SQL Jobs
>2. View backup device and its contents
>I assigned 'TargetServerRole' role in msdb to a user but it didn't meet th
e
>required permission to perform the above operation.|||Thanks Sue.
"Sue Hoegemeier" wrote:

> Using TargetServerRole isn't a documented approach - the
> permissions for this role depend on what service pack you
> are on.
> To view and execute jobs, the user needs to be the job owner
> or a member of sysadmins.
> Viewing contents of a device executes a restore headeronly
> which any user can execute.
> -Sue
> On Fri, 21 Oct 2005 08:41:05 -0700, "RumulusKyle"
> <RumulusKyle@.discussions.microsoft.com> wrote:
>
>

Backup and DTS....@ the same time....

I am launching full backup of user database and at the same time loading the
data to the tables using a DTS package. Will this be a performance issue?
I am using SQL2K.
Thanks,
CarlWell, it will potentially slow down both the load and the backup a little,
mostly simply because of the disc io involved. But is it an issue? - depends
on manythings - how biug the db is, how long the backup takes, how many rows
DTS is loading etc.
You will need to consider things like how long do the 2 steps take
seperately, how long together?, when is it happening? - will anyone else
know - ie is the db idle anyway when you do this?
Mike John
"Carl San" <Carl_San@.hotmail.com> wrote in message
news:un0FX8aMFHA.244@.TK2MSFTNGP12.phx.gbl...
>I am launching full backup of user database and at the same time loading
>the
> data to the tables using a DTS package. Will this be a performance issue?
> I am using SQL2K.
> Thanks,
> Carl
>

Backup and DTS....@ the same time....

I am launching full backup of user database and at the same time loading the
data to the tables using a DTS package. Will this be a performance issue?
I am using SQL2K.
Thanks,
CarlWell, it will potentially slow down both the load and the backup a little,
mostly simply because of the disc io involved. But is it an issue? - depends
on manythings - how biug the db is, how long the backup takes, how many rows
DTS is loading etc.
You will need to consider things like how long do the 2 steps take
seperately, how long together?, when is it happening? - will anyone else
know - ie is the db idle anyway when you do this?
Mike John
"Carl San" <Carl_San@.hotmail.com> wrote in message
news:un0FX8aMFHA.244@.TK2MSFTNGP12.phx.gbl...
>I am launching full backup of user database and at the same time loading
>the
> data to the tables using a DTS package. Will this be a performance issue?
> I am using SQL2K.
> Thanks,
> Carl
>

Backup and DTS....@ the same time....

I am launching full backup of user database and at the same time loading the
data to the tables using a DTS package. Will this be a performance issue?
I am using SQL2K.
Thanks,
Carl
Well, it will potentially slow down both the load and the backup a little,
mostly simply because of the disc io involved. But is it an issue? - depends
on manythings - how biug the db is, how long the backup takes, how many rows
DTS is loading etc.
You will need to consider things like how long do the 2 steps take
seperately, how long together?, when is it happening? - will anyone else
know - ie is the db idle anyway when you do this?
Mike John
"Carl San" <Carl_San@.hotmail.com> wrote in message
news:un0FX8aMFHA.244@.TK2MSFTNGP12.phx.gbl...
>I am launching full backup of user database and at the same time loading
>the
> data to the tables using a DTS package. Will this be a performance issue?
> I am using SQL2K.
> Thanks,
> Carl
>

Backup

I have one user account on an SQL Server, and one Database, and I dont have access to the server. Is there a way I can backup the DB, can an SQL DB be turned into a file of some sort?Where did you want to backup to? The same disk? Accross a network? To tape?

If you just want to export a text file, you can create a DTS process that will run daily (nightly) that will export your database as a csv. CSVs are comma-delimited text files that are easily importable back into SQL Server or most any other DB-type apps, including Excel.|||Originally posted by lebhead
Where did you want to backup to? The same disk? Accross a network? To tape?

If you just want to export a text file, you can create a DTS process that will run daily (nightly) that will export your database as a csv. CSVs are comma-delimited text files that are easily importable back into SQL Server or most any other DB-type apps, including Excel.

how do you create and export a CSV?