Backup to Network Drive: log

Showing posts with label log. Show all posts

Tuesday, March 27, 2012

Basic Question - Truncate Log

Hello Experts
We just started using SQL Server 7.0 in our organization which had come
along with BackOffice with NT 4.0. We have just started putting in some data
in it and have created some tables and some relationships.
My Question is
Should One Truncate Log?
If Yes When Should it be done?
Would appreciate your suggestions on this - THANKS in advance
Regards
Manish Sawjiani
Three Cheers to Technet for the Help!
I suggest you start with Tibor's aricle:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-oj
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in
message news:24CEF98E-AF48-4707-BA94-1AF01E796BAE@.microsoft.com...
> Hello Experts
> We just started using SQL Server 7.0 in our organization which had come
> along with BackOffice with NT 4.0. We have just started putting in some
> data
> in it and have created some tables and some relationships.
> My Question is
> Should One Truncate Log?
> If Yes When Should it be done?
> Would appreciate your suggestions on this - THANKS in advance
> Regards
> Manish Sawjiani
> --
> Three Cheers to Technet for the Help!
|||Hi,
If it is not production database you could set the database option "Truncate
Log on Checkpoint". This will clear the Transaction log byy may itself.
If it is a production database or if the data is critical then you can not
enable the Truncate log on checkpoint option. Instead of that you have to
perform
Transaction log backup (See BACKUP LOG in books online). After the
Trasnaction log backup Log will be automatically cleared.
Thanks
Hari
SQL Server DBA
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in
message news:24CEF98E-AF48-4707-BA94-1AF01E796BAE@.microsoft.com...
> Hello Experts
> We just started using SQL Server 7.0 in our organization which had come
> along with BackOffice with NT 4.0. We have just started putting in some
> data
> in it and have created some tables and some relationships.
> My Question is
> Should One Truncate Log?
> If Yes When Should it be done?
> Would appreciate your suggestions on this - THANKS in advance
> Regards
> Manish Sawjiani
> --
> Three Cheers to Technet for the Help!

Basic Question - Truncate Log

Hello Experts
We just started using SQL Server 7.0 in our organization which had come
along with BackOffice with NT 4.0. We have just started putting in some data
in it and have created some tables and some relationships.
My Question is
Should One Truncate Log?
If Yes When Should it be done?
Would appreciate your suggestions on this - THANKS in advance
Regards
Manish Sawjiani
--
Three Cheers to Technet for the Help!I suggest you start with Tibor's aricle:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
--
-oj
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in
message news:24CEF98E-AF48-4707-BA94-1AF01E796BAE@.microsoft.com...
> Hello Experts
> We just started using SQL Server 7.0 in our organization which had come
> along with BackOffice with NT 4.0. We have just started putting in some
> data
> in it and have created some tables and some relationships.
> My Question is
> Should One Truncate Log?
> If Yes When Should it be done?
> Would appreciate your suggestions on this - THANKS in advance
> Regards
> Manish Sawjiani
> --
> Three Cheers to Technet for the Help!|||Hi,
If it is not production database you could set the database option "Truncate
Log on Checkpoint". This will clear the Transaction log byy may itself.
If it is a production database or if the data is critical then you can not
enable the Truncate log on checkpoint option. Instead of that you have to
perform
Transaction log backup (See BACKUP LOG in books online). After the
Trasnaction log backup Log will be automatically cleared.
Thanks
Hari
SQL Server DBA
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in
message news:24CEF98E-AF48-4707-BA94-1AF01E796BAE@.microsoft.com...
> Hello Experts
> We just started using SQL Server 7.0 in our organization which had come
> along with BackOffice with NT 4.0. We have just started putting in some
> data
> in it and have created some tables and some relationships.
> My Question is
> Should One Truncate Log?
> If Yes When Should it be done?
> Would appreciate your suggestions on this - THANKS in advance
> Regards
> Manish Sawjiani
> --
> Three Cheers to Technet for the Help!

Basic Question - Truncate Log

Hello Experts
We just started using SQL Server 7.0 in our organization which had come
along with BackOffice with NT 4.0. We have just started putting in some data
in it and have created some tables and some relationships.
My Question is
Should One Truncate Log?
If Yes When Should it be done?
Would appreciate your suggestions on this - THANKS in advance
Regards
Manish Sawjiani
--
Three Cheers to technet for the Help!I suggest you start with Tibor's aricle:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-oj
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in
message news:24CEF98E-AF48-4707-BA94-1AF01E796BAE@.microsoft.com...
> Hello Experts
> We just started using SQL Server 7.0 in our organization which had come
> along with BackOffice with NT 4.0. We have just started putting in some
> data
> in it and have created some tables and some relationships.
> My Question is
> Should One Truncate Log?
> If Yes When Should it be done?
> Would appreciate your suggestions on this - THANKS in advance
> Regards
> Manish Sawjiani
> --
> Three Cheers to technet for the Help!|||Hi,
If it is not production database you could set the database option "Truncate
Log on Checkpoint". This will clear the Transaction log byy may itself.
If it is a production database or if the data is critical then you can not
enable the Truncate log on checkpoint option. Instead of that you have to
perform
Transaction log backup (See BACKUP LOG in books online). After the
Trasnaction log backup Log will be automatically cleared.
Thanks
Hari
SQL Server DBA
"Manish Sawjiani" <ManishSawjiani@.discussions.microsoft.com> wrote in
message news:24CEF98E-AF48-4707-BA94-1AF01E796BAE@.microsoft.com...
> Hello Experts
> We just started using SQL Server 7.0 in our organization which had come
> along with BackOffice with NT 4.0. We have just started putting in some
> data
> in it and have created some tables and some relationships.
> My Question is
> Should One Truncate Log?
> If Yes When Should it be done?
> Would appreciate your suggestions on this - THANKS in advance
> Regards
> Manish Sawjiani
> --
> Three Cheers to technet for the Help!sql

Thursday, March 8, 2012

backupset

Here is my situation:
I have a stored procedure that runs full, differential and
log backups for a particular database.
Each backup has unique name based on the type, day of the
week and the time (e.g. Full_Backup_Monday_12-30).
Thus, backups are kept for one week.
When I review the backup set table, I see the name of my
backup, but in many cases I see an old date for the backup
and start and completion dates. However, when I review
the server, the newer backups are present.
My questions are:
Why isn't the backupset table being updated with the most
recent information?
When is the backupset table updated?
Thanks,
MichaelHi Michael,
Could you create a database backup as following steps?
1. Expand a server group, and then expand a server.
2. Expand databases, right-click the database, point to all tasks, and then
click backup database
3. Type the backup set name in the Name box and Select Database -complete
4. Under Destination, click Tape or Disk, and then specify a backup
destination.
If no backup destinations appear, click Add to add an existing backup
device or to create a new one.
5. Click Ok to create (Don't select the Schedule check box)
6. Check to see the backupset table again.
Is the backupset table updated immediately?
According to my test, the backupset table is updated on my side. When a
real backup finishes, a new record will be inserted in the table. Does it
work on your side? Generally, we do not recommended to directly query
system table. Could you tell me your detailed scenario?
This posting is provided "AS IS" with no warranties, and confers no rights.
Sincerely,
Michael Shao
Microsoft Support Engineer
| Content-Class: urn:content-classes:message
| From: "Michael" <michael_schall@.unionsanitary.com>
| Sender: "Michael" <michael_schall@.unionsanitary.com>
| Subject: backupset
| Date: Mon, 7 Jul 2003 16:51:46 -0700
| Lines: 22
| Message-ID: <06c801c344e2$b9f90bf0$a301280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcNE4rn5YC8MduACSZi2OM0YFuqZFQ==| Newsgroups: microsoft.public.sqlserver.server
| Path: cpmsftngxa09.phx.gbl
| Xref: cpmsftngxa09.phx.gbl microsoft.public.sqlserver.server:23185
| NNTP-Posting-Host: TK2MSFTNGXA11 10.40.1.163
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Here is my situation:
|
| I have a stored procedure that runs full, differential and
| log backups for a particular database.
|
| Each backup has unique name based on the type, day of the
| week and the time (e.g. Full_Backup_Monday_12-30).
| Thus, backups are kept for one week.
|
| When I review the backup set table, I see the name of my
| backup, but in many cases I see an old date for the backup
| and start and completion dates. However, when I review
| the server, the newer backups are present.
|
| My questions are:
| Why isn't the backupset table being updated with the most
| recent information?
|
| When is the backupset table updated?
|
| Thanks,
| Michael
|

Backups of tran log not working

I have my maintenance plan set up to backup to disk each day, including the
tran log.
I am getting an error on the Tran log backup, however.
It doesn't tell me why it failed, just that it failed.
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The Job
was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (Step
1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
The help center is no help.
The other 3 parts of the maintenance plan seem to go fine.
Also, there is no log in the Sql Logs, but there is in the Event log (where
I got this). There is plenty of disk space, so that is not the problem.
Am I missing something here?
Thanks,
TomSpecify a report file for the maint plan and check that for specific error messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>I have my maintenance plan set up to backup to disk each day, including the tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'DB Maintenance
> Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status: Failed - Invoked on: 2005-11-09 09:24:24 -
> Message: The job failed. The Job was invoked by User FTSOLUTIONS0\tfs. The last step to run was
> step 1 (Step 1).
> For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (where I got this). There is
> plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:edxoSaV5FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Specify a report file for the maint plan and check that for specific error
> messages.
I did that and got to following:
*****************************************************************************************************
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'VENUS' as 'FTSOLUTIONS0\servicesql' (trusted)
Starting maintenance plan 'All Databases Backup' on 11/9/2005 10:33:23 AM
Backup can not be performed on database 'FTS'. This sub task is ignored.
Backup can not be performed on database 'master'. This sub task is ignored.
[3] Database model: Transaction Log Backup...
Destination: [d:\Microsoft SQL
Server\MSSQL\BACKUP\model_tlog_200511091033.TRN]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[4] Database model: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub task is ignored.
End of maintenance plan 'All Databases Backup' on 11/9/2005 10:33:24 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
*****************************************************************************************************
So it works for some and not others.
And why can't it backup master or msdb - they have log files
Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
Thanks,
Tom
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>>I have my maintenance plan set up to backup to disk each day, including
>>the tran log.
>> I am getting an error on the Tran log backup, however.
>> It doesn't tell me why it failed, just that it failed.
>> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
>> Plan 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) -
>> Status: Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job
>> failed. The Job was invoked by User FTSOLUTIONS0\tfs. The last step to
>> run was step 1 (Step 1).
>> For more information, see Help and Support Center at
>> http://go.microsoft.com/fwlink/events.asp.
>> The help center is no help.
>> The other 3 parts of the maintenance plan seem to go fine.
>> Also, there is no log in the Sql Logs, but there is in the Event log
>> (where I got this). There is plenty of disk space, so that is not the
>> problem.
>> Am I missing something here?
>> Thanks,
>> Tom
>|||I think that master & msdb are simple recovery model, hence cannot have
tran. log backups. Just remove this portion of your maintenence plan.|||Ok.
But why did FTS not backup?
Tom
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131562250.817157.160290@.g43g2000cwa.googlegroups.com...
>I think that master & msdb are simple recovery model, hence cannot have
> tran. log backups. Just remove this portion of your maintenence plan.
>|||Most log backups fail because the database is in simple recovery mode. Check
that first. Second, verify the database has had a full backup. Finally, I
assume we're not talking about the msdb and master--but just to be safe,
it's not one of those two, correct?
"tshad" wrote:
> I have my maintenance plan set up to backup to disk each day, including the
> tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
> Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The Job
> was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (Step
> 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (where
> I got this). There is plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>
>|||>From his report, it looks like we are talking about master & msdb. As
well as one other called FTS. Like fnguy said - check to see if if FTS
is simple recovery model. If it is, you can't back up the tran. log.
Backup can not be performed on database 'FTS'. This sub task is
ignored.
Backup can not be performed on database 'master'. This sub task is
ignored.
Backup can not be performed on database 'msdb'. This sub task is
ignored.|||"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:90AC17EA-75F3-45C0-BA2D-C5CB3600AF4C@.microsoft.com...
> Most log backups fail because the database is in simple recovery mode.
> Check
> that first. Second, verify the database has had a full backup. Finally,
> I
> assume we're not talking about the msdb and master--but just to be safe,
> it's not one of those two, correct?
No, it isn't. It's one of our normal databases.
But you mentioned, the database having a full backup.
Do you need to do the Tran backup first?
Thanks,
Tom
>
> "tshad" wrote:
>> I have my maintenance plan set up to backup to disk each day, including
>> the
>> tran log.
>> I am getting an error on the Tran log backup, however.
>> It doesn't tell me why it failed, just that it failed.
>> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
>> Plan
>> 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
>> Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The
>> Job
>> was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1
>> (Step
>> 1).
>> For more information, see Help and Support Center at
>> http://go.microsoft.com/fwlink/events.asp.
>> The help center is no help.
>> The other 3 parts of the maintenance plan seem to go fine.
>> Also, there is no log in the Sql Logs, but there is in the Event log
>> (where
>> I got this). There is plenty of disk space, so that is not the problem.
>> Am I missing something here?
>> Thanks,
>> Tom
>>|||"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
> >From his report, it looks like we are talking about master & msdb. As
> well as one other called FTS. Like fnguy said - check to see if if FTS
> is simple recovery model. If it is, you can't back up the tran. log.
> Backup can not be performed on database 'FTS'. This sub task is
> ignored.
> Backup can not be performed on database 'master'. This sub task is
> ignored.
> Backup can not be performed on database 'msdb'. This sub task is
> ignored.
>
How do you check to see if it is a simple recovery model? I assume it
isn't, but I am not sure.
Tom|||Check the recovery model of your database.
If the FTS database is in Simple recovery model, then log backups make no
sense.
> Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
> SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
>
ERRORLOG.n are the SQL error logs. They are simple text files. You should
be able to open them in notepad unless there is some strange protection
applied. As an administrator you should certainly be agle to read them.
Kevin Farlee|||As Kevin suggested, it is probably in simple recovery mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23LFHRAW5FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Ok.
> But why did FTS not backup?
> Tom
> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
> news:1131562250.817157.160290@.g43g2000cwa.googlegroups.com...
>>I think that master & msdb are simple recovery model, hence cannot have
>> tran. log backups. Just remove this portion of your maintenence plan.
>|||> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
sp_helpdb, for instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
> Tom
>|||Pls execute following SP from Query Analyser and post it's output:
sp_helpdb 'fts'
"tshad" wrote:
> "fnguy" <fnguy@.discussions.microsoft.com> wrote in message
> news:90AC17EA-75F3-45C0-BA2D-C5CB3600AF4C@.microsoft.com...
> > Most log backups fail because the database is in simple recovery mode.
> > Check
> > that first. Second, verify the database has had a full backup. Finally,
> > I
> > assume we're not talking about the msdb and master--but just to be safe,
> > it's not one of those two, correct?
> No, it isn't. It's one of our normal databases.
> But you mentioned, the database having a full backup.
> Do you need to do the Tran backup first?
> Thanks,
> Tom|||How do you check to see if it is a simple recovery model? I assume it
isn't, but I am not sure.
--
Also - open enterprise manager, right click the database in question,
and choose properties. Go to options tab. See what exists under the
Recovery model section. If it's simple, then all you can do is full
backups. If it's full, then you can do logs.
Tibor Karaszi wrote:
> > How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
> sp_helpdb, for instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
> >
> > "Corey Bunch" <unc27932@.yahoo.com> wrote in message
> > news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
> >> >From his report, it looks like we are talking about master & msdb. As
> >> well as one other called FTS. Like fnguy said - check to see if if FTS
> >> is simple recovery model. If it is, you can't back up the tran. log.
> >>
> >> Backup can not be performed on database 'FTS'. This sub task is
> >> ignored.
> >> Backup can not be performed on database 'master'. This sub task is
> >> ignored.
> >> Backup can not be performed on database 'msdb'. This sub task is
> >> ignored.
> >>
> >
> > How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
> >
> > Tom
> >|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ejxz3mc5FHA.2364@.TK2MSFTNGP12.phx.gbl...
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
> sp_helpdb, for instance.
That does show it as
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
>>
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
>> Tom
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ejxz3mc5FHA.2364@.TK2MSFTNGP12.phx.gbl...
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
> sp_helpdb, for instance.
That was it.
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
IsAutoUpdateStatistics, IsFullTextEnabled
But how did it get that way? What did I do to make it that way? My other
databases aren't that way.
Can I change that?
Thanks,
Tom
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
>>
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
>> Tom
>|||Yes, you can change it using the ALTER DATABASE command.
How it got set to simple in the first place? I can't answer that, except saying that someone or some
program did it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message news:uP89yUh5FHA.140@.TK2MSFTNGP10.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:ejxz3mc5FHA.2364@.TK2MSFTNGP12.phx.gbl...
>> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
>> sp_helpdb, for instance.
> That was it.
> Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539,
> Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsTornPageDetectionEnabled,
> IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled
> But how did it get that way? What did I do to make it that way? My other databases aren't that
> way.
> Can I change that?
> Thanks,
> Tom
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
>> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
>>
>> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
>> Tom
>>
>|||"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131630158.667899.30870@.g44g2000cwa.googlegroups.com...
> How do you check to see if it is a simple recovery model? I assume it
> isn't, but I am not sure.
> --
> Also - open enterprise manager, right click the database in question,
> and choose properties. Go to options tab. See what exists under the
> Recovery model section. If it's simple, then all you can do is full
> backups. If it's full, then you can do logs.
>
Can I just change that there?
Why would you want a simple model?
It does have a tran log defined for it and it is the only one of 5 databases
I have that isn't full. So what causes it to be simple I just created
another database, it has no question about it and it automatically made it
simple. I originally created FTS in this way - so what would cause it to be
simple - I know I didn't do it manually.
Thanks,
Tom
>
> Tibor Karaszi wrote:
>> > How do you check to see if it is a simple recovery model? I assume it
>> > isn't, but I am not sure.
>> sp_helpdb, for instance.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
>> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> >
>> > "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> > news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >> >From his report, it looks like we are talking about master & msdb.
>> >> >As
>> >> well as one other called FTS. Like fnguy said - check to see if if
>> >> FTS
>> >> is simple recovery model. If it is, you can't back up the tran. log.
>> >>
>> >> Backup can not be performed on database 'FTS'. This sub task is
>> >> ignored.
>> >> Backup can not be performed on database 'master'. This sub task is
>> >> ignored.
>> >> Backup can not be performed on database 'msdb'. This sub task is
>> >> ignored.
>> >>
>> >
>> > How do you check to see if it is a simple recovery model? I assume it
>> > isn't, but I am not sure.
>> >
>> > Tom
>> >
>|||Yes - you can just change that there using the drop down. Not sure why
it's full instead of simple. Simple model = less maintenance, but also
= less recoverability. See below links.
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1069109,00.html
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_4l83.asp
>From microsoft...
Simple Recovery Model
This recovery model facilitates the maintenance of a database by making
the transaction log virtually maintenance free. There are limitations
placed on the recoverability of a database if this recovery model is
used.

Wednesday, March 7, 2012

backups interfering with log shipping?

I have some questions on backups and log shipping.

Before I get to them, though, the goal:

Back up a SQL 2000 database (~5GB in size) completely every day.
Back up its transaction log hourly.
Maintain a warm backup DB server using log shipping.

Here is what I have done so far, which has led to my questions:

I have stopped using maintenance plans for backups, feeling that
then the process would be less 'black boxy'

I first created two jobs to perform the daily db and hourly tlog backups
that saved the backups in files using the naming formats
<name>_db_<yymmdd>.bak and <name>_tlog_<yymmddnnss>.bak.

Issue 1: doing backups this way means that it's a little difficult for the
log shipping jobs to figure out the filenames (esp. the tlog ones), if log
shipping is going to use the files generated by the backup jobs.

Issue 2: I thought maybe I'd have log shipping generate its OWN backup
files, but would that cause problems with the transaction log? Say the
normal tlog backup fires, then fifteen minutes later the shipping tlog
backup fires. Would the shipping tlog backup file be missing the transactions
that were backed up during the normal log backup?

Issue 3: To try to help with the naming issue, I tried switching the backups from
creating new files each time to file devices whose names would be constant.
This worked, but since my database is about 5GB in size, that meant
that, with expiring backups after 7 days, the database backup device
would settle in at about 25GB (I skip weekends) and I'd have to copy
or cab-copy-uncab that file over to my warm server every day. That
seemed a little inefficient. Does anybody have alternative ideas?

My last question is for general info:

Where does SQL Server keep the information on the backups that exist?
Whether I was using individual files or file devices, I was able to go to
Database-All Tasks-Restore Database... in Enterprise Manager and it
would show me the backups that existed. I imagine these must be
stored in a system table somewhere, but I did not see any obvious place
to look (no 'sysbackups' table, etc.).

Many thanks in advance for your input!

GeoffBoiling my above messgae down to bite-size questions:

1. Where does SQL store information related to the backups that currently exist? Or does SQL scan for backup files and figure out what's in them on the fly (seems unlikely to me)?

2. Say I want to run two transaction log backups per hour. One is included in the normal backup plan and one occurs, say, 15 minutes later for log shipping purposes. Is my log shipping going to fail because the transactions that were backed up during the normal bacup will not be in my log shipping backup?|||It's in the msdb in a table called backupset.

I run log shipping where I backup the transaction log to a file name like DBNAME_TRAN.TRN. I then copy one to the SHIP TO server and apply it while copying the other to a directory where I add the data and time. I have scripts that determine which logs to apply in case I have to restore the database from the Full, Incrimental and then apply transaction log backups.

Backups Failing

I set up a full backup maintenance plan for my databases and the database portion of the backup jobs completes successfully, but the transaction log portion successfully backs up master and model, but fails for the other databases with the message:

Backup can not be performed on database 'msdb'. This sub task is ignored.

What's the problem?

Bob

Check the recovery model of your databases. They are probably in SIMPLE mode so transaction logs are not allowed.

HTH!

|||

I thought this was answered, but it's not. I have never had the master db transaction log backed up. I have set it to full backup as I have with the others and they all work, but master still does not.

Bob

|||

You can only perform transaction log backups on databases in the full or bulk-logged recovery modes (check your db options for this). Master and MSDB databases are always set to simple therefore no transaction log backup can occur on these databases. When configuring your maintenance plans these should be omitted.

|||As said above you can perform only Full backups with Master database regardless of the recovery model ! and full,differential and tran log backups with msdb db if the recovery model is full or bulk logged ! for model db if the recovery is full all 3 backups are possible if its simple only tran logs are not possible in model

|||

Thanks all - I see that now.

Backups and log file growth issues

I've been looking through this forum and can only guess that SQL2005 has changed in how backups and log file growth issues are handled. I tried the All Tasks feature from the GUI to perform a backup, however, the Truncate the Transaction log feature is greyed out so I can not select it. In SQL2000 I used the following to backup my databases and the log files were never a problem:

BACKUP DATABASE [DSS] TO DISK =

N'G:\MSSQL\Backup\DSSbackup.BAK'

WITH INIT , NOUNLOAD , NAME = N'DSS backup',

SKIP , STATS = 10, NOFORMAT

Could someone assist me with a proper T-SQL to place in a job to backup my database and keep the log file under control and / or explain why the Truncate transaction log feature is greyed out?

Thanks!

Additional information: I have used the scripts below to backup my database and logfile including using the INIT to truncate the log file. My database file is 9.2 GB and my log file is 9.9 GB. After running the scripts, a transaction log backup file is created (512 KB), however my ldf log file remains 9.9 GB. Why are the transactions not being written to the database file and log file entries being released?

/* This script backs up the DSS database to disk, overwriting any
previous backup
*/
BACKUP DATABASE [DSS]
TO DISK = N'g:\mssql\backup\database\DSS.bak'
WITH DESCRIPTION = N'DSS Full Database Backup'
, INIT
, NAME = N'DSS - Full Database Backup'
GO
/* Backup validation to ensure the file is valid before storing it */
RESTORE VERIFYONLY
FROM DISK = N'g:\mssql\backup\database\DSS.bak'
WITH FILE = 1
GO

/* This script backs up the DSS log file and truncates the file */
BACKUP LOG [DSS]
TO DISK = N'g:\mssql\backup\logfiles\DSS_log.bak'
WITH
INIT
, NAME = N'DSS-Transaction Log Backup'
GO

Thanks!

|||

I have few observation

(a) in this script what you are doing is you are overwriting the earlier backup with the current one... which is not at all recommended. if the backup file got corrupted means... you will endup without any backup.. this is a serious issue. You should not use INIT in this case i believe.

(b) to transaction log size under control , you should increase the frequency of the TL backup. If currently you are configured TL backup daily then make it hourly with out INIT option. If you overwrite the backup of TL then there is no point in taking the TL Backup

(c) if you do not want POINT-in-Time restore you can change the Recovery model to Simple , which will control the size to a certain limit

(d) If you want to trucate the LOg then use WIth TRUNCATE ONLY option with the backup script

EG.

backup log [DSS] with truncate_only

dbcc shrinkfile(DSS_Log)

Read more about TL architecture and Backup method /Recovery model in BOL

Read these article also :-

FileSize -How to stop the log file from growing
http://www.support.microsoft.com/?id=873235

FileSize -Log file filling up
http://www.support.microsoft.com/?id=110139

FileSize -Log File Grows too big
http://www.support.microsoft.com/?id=317375

FileSize -Log File issues
http://www.nigelrivett.net/TransactionLogFileGrows_1.html

FileSize -Shrinking Log in SQL Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=272318

Madhu

|||

Thank you for your response.

(a)The backup database and log files are backed up to tape regularily so I am not in danger of losing the backup.

(b) & (c) This is a new database, not yet released to production, so I will plan accordingly as you suggested to ensure a point-in-time recovery is available.

(d) As this database is new, I will try your script commands to reduce the log file this one time.

This would fall into the class of a one-off increase. I just created this database and imported several SQL2000 tables. I then re-wrote the SQL2000 stored procedures which truncate and reload the tables, so I feel this caused the log file increase. I will try the DBCC shrink this time, however, I do not plan to use it regularily.

Thanks again for your response!

|||

I performed a full database backup and a transaction log backup. I ran the following query:

DBCC SHRINKFILE (DSS_log)

..and received the following message:

Cannot shrink log file 2 (DSS_log) because all logical log files are in use.

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I am assuming there may be uncommitted transactions in the log file. Is there a T-SQL query to force all transactions to be committed and then erased from the log file?

Backups and log file growth issues

BACKUP DATABASE [DSS] TO DISK =

N'G:\MSSQL\Backup\DSSbackup.BAK'

WITH INIT , NOUNLOAD , NAME = N'DSS backup',

SKIP , STATS = 10, NOFORMAT

Could someone assist me with a proper T-SQL to place in a job to backup my database and keep the log file under control and / or explain why the Truncate transaction log feature is greyed out?

Thanks!

/* This script backs up the DSS log file and truncates the file */
BACKUP LOG [DSS]
TO DISK = N'g:\mssql\backup\logfiles\DSS_log.bak'
WITH
INIT
, NAME = N'DSS-Transaction Log Backup'
GO

Thanks!

|||

I have few observation

(c) if you do not want POINT-in-Time restore you can change the Recovery model to Simple , which will control the size to a certain limit

(d) If you want to trucate the LOg then use WIth TRUNCATE ONLY option with the backup script

EG.

backup log [DSS] with truncate_only

dbcc shrinkfile(DSS_Log)

Read more about TL architecture and Backup method /Recovery model in BOL

Read these article also :-

FileSize -How to stop the log file from growing
http://www.support.microsoft.com/?id=873235

FileSize -Log file filling up
http://www.support.microsoft.com/?id=110139

FileSize -Log File Grows too big
http://www.support.microsoft.com/?id=317375

FileSize -Log File issues
http://www.nigelrivett.net/TransactionLogFileGrows_1.html

FileSize -Shrinking Log in SQL Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=272318

Madhu

|||

Thank you for your response.

(a)The backup database and log files are backed up to tape regularily so I am not in danger of losing the backup.

(b) & (c) This is a new database, not yet released to production, so I will plan accordingly as you suggested to ensure a point-in-time recovery is available.

(d) As this database is new, I will try your script commands to reduce the log file this one time.

Thanks again for your response!

|||

I performed a full database backup and a transaction log backup. I ran the following query:

DBCC SHRINKFILE (DSS_log)

..and received the following message:

Cannot shrink log file 2 (DSS_log) because all logical log files are in use.

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I am assuming there may be uncommitted transactions in the log file. Is there a T-SQL query to force all transactions to be committed and then erased from the log file?

Saturday, February 25, 2012

backups - complete, differential and transaction log

Hi,
I am new to database administration and I was looking at the backups setup
on a database-
Full backup - every week, overwrite existing media
Differential backup - every night, append to media
Transaction log backup - every hour, append to media
The size of the differential backup file has grown to 40 GB while the actual
database full backup is just 1 GB. This led me to wonder why do we need to
use the setting "Append to media" for the Differential Backup. It should be
"Overwrite existing media" since I am using a disk to write the backups?
Am I correct? What's the correct setting for transaction log backups? Thanks.For DIFFERENTIAL backups: When you have to restore from backups, you will
need to restore the last FULL backup, and then restore the last DIFFERENTIAL
backup made since the last FULL backup (OR) ALL TRANSACTION LOG backups
since the last FULL backup. (And then, of course, the remaining TLOG backups
made since the last DIFFERENTIAL.)
Having DIFFERENTIAL backups mean we have to 'handle' fewer TLOG backups
during the stressed restore
It is ok to have only the last DIFFERENTIAL.
TLOG backups should be 'Append to Media' DO NOT overwrite. When there is a
FULL backup, you may not 'need' to keep any DIFFERENTIAL or TLOG backups.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:39DA30A9-9846-4802-A326-288F1C909F64@.microsoft.com...
> Hi,
> I am new to database administration and I was looking at the backups setup
> on a database-
> Full backup - every week, overwrite existing media
> Differential backup - every night, append to media
> Transaction log backup - every hour, append to media
> The size of the differential backup file has grown to 40 GB while the
> actual
> database full backup is just 1 GB. This led me to wonder why do we need to
> use the setting "Append to media" for the Differential Backup. It should
> be
> "Overwrite existing media" since I am using a disk to write the backups?
> Am I correct? What's the correct setting for transaction log backups?
> Thanks.|||Thanks, Arnie for the quick reply. When you say that "TLOG backups should be
'Append to Media' DO NOT overwrite", we just need to keep the TLOG backups
since the last DIFFERENTIAL BACKUP, we don't need any TLOG backup before that.
Am I correct?
"Arnie Rowland" wrote:
> For DIFFERENTIAL backups: When you have to restore from backups, you will
> need to restore the last FULL backup, and then restore the last DIFFERENTIAL
> backup made since the last FULL backup (OR) ALL TRANSACTION LOG backups
> since the last FULL backup. (And then, of course, the remaining TLOG backups
> made since the last DIFFERENTIAL.)
> Having DIFFERENTIAL backups mean we have to 'handle' fewer TLOG backups
> during the stressed restore
> It is ok to have only the last DIFFERENTIAL.
> TLOG backups should be 'Append to Media' DO NOT overwrite. When there is a
> FULL backup, you may not 'need' to keep any DIFFERENTIAL or TLOG backups.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:39DA30A9-9846-4802-A326-288F1C909F64@.microsoft.com...
> > Hi,
> >
> > I am new to database administration and I was looking at the backups setup
> > on a database-
> > Full backup - every week, overwrite existing media
> > Differential backup - every night, append to media
> > Transaction log backup - every hour, append to media
> >
> > The size of the differential backup file has grown to 40 GB while the
> > actual
> > database full backup is just 1 GB. This led me to wonder why do we need to
> > use the setting "Append to media" for the Differential Backup. It should
> > be
> > "Overwrite existing media" since I am using a disk to write the backups?
> >
> > Am I correct? What's the correct setting for transaction log backups?
> > Thanks.
>
>|||What you need to keep only you can decide. When you do a restore, you use:
Most recent db backup
Most recent diff backup
All subsequent log backups after the diff backup.
But:
Say you had a database corruption introduced between the last db backup and the most recent diff
backup. And say that the corruption is not still present if you restore according to above scheme.
You would not want to restore the most recent db backup, and all subsequent log backup (so the
corruption is not likely to be re-introduced).
Or say that you need a state of the database as it were at tuesday 14:34. If you did db backup sun,
diff mon, diff, tue, diff wed, diff thu, you would now want to restore db, diff tue night, and log
backups until that point in time.
So you do need to carefully consider your possible restore scenarios, before you can say what you
need.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:3E7C4E57-2AAB-47C1-A365-99AA537DC2AD@.microsoft.com...
> Thanks, Arnie for the quick reply. When you say that "TLOG backups should be
> 'Append to Media' DO NOT overwrite", we just need to keep the TLOG backups
> since the last DIFFERENTIAL BACKUP, we don't need any TLOG backup before that.
> Am I correct?
> "Arnie Rowland" wrote:
>> For DIFFERENTIAL backups: When you have to restore from backups, you will
>> need to restore the last FULL backup, and then restore the last DIFFERENTIAL
>> backup made since the last FULL backup (OR) ALL TRANSACTION LOG backups
>> since the last FULL backup. (And then, of course, the remaining TLOG backups
>> made since the last DIFFERENTIAL.)
>> Having DIFFERENTIAL backups mean we have to 'handle' fewer TLOG backups
>> during the stressed restore
>> It is ok to have only the last DIFFERENTIAL.
>> TLOG backups should be 'Append to Media' DO NOT overwrite. When there is a
>> FULL backup, you may not 'need' to keep any DIFFERENTIAL or TLOG backups.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>> You can't help someone get up a hill without getting a little closer to the
>> top yourself.
>> - H. Norman Schwarzkopf
>>
>> "sharman" <sharman@.discussions.microsoft.com> wrote in message
>> news:39DA30A9-9846-4802-A326-288F1C909F64@.microsoft.com...
>> > Hi,
>> >
>> > I am new to database administration and I was looking at the backups setup
>> > on a database-
>> > Full backup - every week, overwrite existing media
>> > Differential backup - every night, append to media
>> > Transaction log backup - every hour, append to media
>> >
>> > The size of the differential backup file has grown to 40 GB while the
>> > actual
>> > database full backup is just 1 GB. This led me to wonder why do we need to
>> > use the setting "Append to media" for the Differential Backup. It should
>> > be
>> > "Overwrite existing media" since I am using a disk to write the backups?
>> >
>> > Am I correct? What's the correct setting for transaction log backups?
>> > Thanks.
>>

backups

Dear all,
I would like to know in what table the backups are stored.
I mean, I see in the SQL Server log registry these lines:
Database backed up: Database: DATA1, creation date(time):
2005/07/26(17:11:49), pages dumped: 63699, first LSN: 2724:195:1, last LSN:
2724:197:1, number of dump devices: 1, device information: (FILE=1,
TYPE=VIRTUAL_DEVICE: {'Legato#af0fc469-fcdc-4b4e-a05f-4a774487e651'}).
..
..
That's fine but from what table is retrieving SQL that information?
Best regards,Enric,
The info is scatted across a few tables in the msdb database.
Mainly backupset, but have a look at the others whose name starts with backu
p :)
Regards
AJ
"Enric" <Enric@.discussions.microsoft.com> wrote in message news:8AA0C8CB-9368-4C70-8883-444
2B4CAA10E@.microsoft.com...
> Dear all,
> I would like to know in what table the backups are stored.
> I mean, I see in the SQL Server log registry these lines:
> Database backed up: Database: DATA1, creation date(time):
> 2005/07/26(17:11:49), pages dumped: 63699, first LSN: 2724:195:1, last LSN
:
> 2724:197:1, number of dump devices: 1, device information: (FILE=1,
> TYPE=VIRTUAL_DEVICE: {'Legato#af0fc469-fcdc-4b4e-a05f-4a774487e651'}).
> ..
> ..
> That's fine but from what table is retrieving SQL that information?
> Best regards,
>
>|||Below four tables in the msdb database:
dbo.backupfile
dbo.backupmediafamily
dbo.backupmediaset
dbo.backupset
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:8AA0C8CB-9368-4C70-8883-4442B4CAA10E@.microsoft.com...
> Dear all,
> I would like to know in what table the backups are stored.
> I mean, I see in the SQL Server log registry these lines:
> Database backed up: Database: DATA1, creation date(time):
> 2005/07/26(17:11:49), pages dumped: 63699, first LSN: 2724:195:1, last LSN
:
> 2724:197:1, number of dump devices: 1, device information: (FILE=1,
> TYPE=VIRTUAL_DEVICE: {'Legato#af0fc469-fcdc-4b4e-a05f-4a774487e651'}).
> ..
> ..
> That's fine but from what table is retrieving SQL that information?
> Best regards,
>
>|||Thanks a lot to both,
"Tibor Karaszi" wrote:

> Below four tables in the msdb database:
> dbo.backupfile
> dbo.backupmediafamily
> dbo.backupmediaset
> dbo.backupset
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:8AA0C8CB-9368-4C70-8883-4442B4CAA10E@.microsoft.com...
>

Backups

I have a database maintenance plan to backup the DB and the transaction log.
It is not doing backups any more. I can go and do them manually though. I
looked at the event log and noticed an error that has been send several time
(see below). Is there a relationship. Thanks for your help. BTW this is th
e
MOM server DB!
"The description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. You may be abl
e
to use the /AUXSOURCE= flag to retrieve this description; see Help and
Support for details. The following information is part of the event: 18278,
Database log truncated: Database: Onepoint."Check the status/history of the job. Look for errors on the SQL Server Error
Log (look at the date/time the job is suppose to run). Also look at the
reports file defined in the maintenance plan.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Niles" wrote:

> I have a database maintenance plan to backup the DB and the transaction lo
g.
> It is not doing backups any more. I can go and do them manually though. I
> looked at the event log and noticed an error that has been send several ti
me
> (see below). Is there a relationship. Thanks for your help. BTW this is
the
> MOM server DB!
> "The description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot b
e
> found. The local computer may not have the necessary registry information
or
> message DLL files to display messages from a remote computer. You may be a
ble
> to use the /AUXSOURCE= flag to retrieve this description; see Help and
> Support for details. The following information is part of the event: 18278
,
> Database log truncated: Database: Onepoint."
>

Backups

I have a database maintenance plan to backup the DB and the transaction log.
It is not doing backups any more. I can go and do them manually though. I
looked at the event log and noticed an error that has been send several time
(see below). Is there a relationship. Thanks for your help. BTW this is the
MOM server DB!
"The description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
found. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. You may be able
to use the /AUXSOURCE= flag to retrieve this description; see Help and
Support for details. The following information is part of the event: 18278,
Database log truncated: Database: Onepoint."Check the status/history of the job. Look for errors on the SQL Server Error
Log (look at the date/time the job is suppose to run). Also look at the
reports file defined in the maintenance plan.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Niles" wrote:
> I have a database maintenance plan to backup the DB and the transaction log.
> It is not doing backups any more. I can go and do them manually though. I
> looked at the event log and noticed an error that has been send several time
> (see below). Is there a relationship. Thanks for your help. BTW this is the
> MOM server DB!
> "The description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be
> found. The local computer may not have the necessary registry information or
> message DLL files to display messages from a remote computer. You may be able
> to use the /AUXSOURCE= flag to retrieve this description; see Help and
> Support for details. The following information is part of the event: 18278,
> Database log truncated: Database: Onepoint."
>

backups

Need to do sql server 2000 database and transaction log
backups. Using the backup wizard. Can back up on the
server machine no problem.
But I need to back up to another M/C which is visibly
connected by the network, and to a CD burner (which is
drive d on the server mc.) I have typed in a variety of
paths for each scenario and nothing is working.
Thank you
AnnAnn,
You need to use the UNC pattern.First create a share in that remote server,
make sure that the account under which SQL Server is running has the
required privileges/permissions on that share.After that, do the backup as:
BACKUP DATABASE <dbname>
TO DISK = '\\destserver\d$\dbbackup.BAK'
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"ann" <akukich@.plaind.com> wrote in message
news:025801c37d32$23687170$a101280a@.phx.gbl...
> Need to do sql server 2000 database and transaction log
> backups. Using the backup wizard. Can back up on the
> server machine no problem.
> But I need to back up to another M/C which is visibly
> connected by the network, and to a CD burner (which is
> drive d on the server mc.) I have typed in a variety of
> paths for each scenario and nothing is working.
> Thank you
> Ann|||Use the UNC paths. Example:
\\MachineName\ShareName
or
\\MachineName\Driver$\Folder1\Folder2
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"ann" <akukich@.plaind.com> wrote in message
news:025801c37d32$23687170$a101280a@.phx.gbl...
Need to do sql server 2000 database and transaction log
backups. Using the backup wizard. Can back up on the
server machine no problem.
But I need to back up to another M/C which is visibly
connected by the network, and to a CD burner (which is
drive d on the server mc.) I have typed in a variety of
paths for each scenario and nothing is working.
Thank you
Ann

Friday, February 24, 2012

Backup/Restore Tran Log

Hello Experts,
This is my first time that I work with the logic of
Transaction Log Backups/Restores seriously.
--First I made a full backup, then I backup my log in a
file:
BACKUP LOG [TEST_3]
TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH INIT, NAME = N'TEST 3 backup', NOSKIP
--then I wanted to restore this log:
declare @.FilePosition as int
SELECT @.FilePosition = position
FROM msdb..backupset
WHERE database_name = 'TEST_3'
AND type != 'F'
AND backup_set_id = (SELECT MAX(backup_set_id)
FROM msdb..backupset
WHERE database_name
= 'TEST_3')
RESTORE LOG TEST_3
FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH FILE = @.FilePosition
Even though I don't have any other connection in my DB
the error message I get is:
Server: Msg 3101, Level 16, State 1, Line 11
Exclusive access could not be obtained because the
database is in use.
Server: Msg 3013, Level 16, State 1, Line 11
RESTORE LOG is terminating abnormally.
Can you please post a transaction backup/restore example?
Thanks in advance!
Hi,
It seems there is some one connected to your database. Execute sp_who and
check the user connected to the database, either issue KILL SPID from the
query analyzer to kill the user connected or execute the below command just
before the restore:-
Alter database TEST_3 set single_user with rollback immediate
go
Restore command -- Execute
go
Alter database TEST_3 set multi_user
Thanks
Hari
MCDBA
"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in message
news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
> Hello Experts,
> This is my first time that I work with the logic of
> Transaction Log Backups/Restores seriously.
> --First I made a full backup, then I backup my log in a
> file:
> BACKUP LOG [TEST_3]
> TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH INIT, NAME = N'TEST 3 backup', NOSKIP
> --then I wanted to restore this log:
> declare @.FilePosition as int
> SELECT @.FilePosition = position
> FROM msdb..backupset
> WHERE database_name = 'TEST_3'
> AND type != 'F'
> AND backup_set_id = (SELECT MAX(backup_set_id)
> FROM msdb..backupset
> WHERE database_name
> = 'TEST_3')
> RESTORE LOG TEST_3
> FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH FILE = @.FilePosition
> Even though I don't have any other connection in my DB
> the error message I get is:
> Server: Msg 3101, Level 16, State 1, Line 11
> Exclusive access could not be obtained because the
> database is in use.
> Server: Msg 3013, Level 16, State 1, Line 11
> RESTORE LOG is terminating abnormally.
> Can you please post a transaction backup/restore example?
> Thanks in advance!
|||Thanks for the reply, the error insists to appear

I'll try to KILL SPIDs

>--Original Message--
>Hi,
>It seems there is some one connected to your database.
Execute sp_who and
>check the user connected to the database, either issue
KILL SPID from the
>query analyzer to kill the user connected or execute
the below command just
>before the restore:-
>Alter database TEST_3 set single_user with rollback
immediate
>go
>Restore command -- Execute
>go
>Alter database TEST_3 set multi_user
>Thanks
>Hari
>MCDBA
>
>
>"Konstantinos Michas"
<anonymous@.discussions.microsoft.com> wrote in message[vbcol=seagreen]
>news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
Server\MSSQL\BACKUP\BACKUP[vbcol=seagreen]
example?
>
>.
>

Tuesday, March 27, 2012

Basic Question - Truncate Log

Basic Question - Truncate Log

Basic Question - Truncate Log

Thursday, March 8, 2012

backupset

Backups of tran log not working

Backups of tran log not working

Backups of tran log not working

Wednesday, March 7, 2012

backups interfering with log shipping?

Backups Failing

Backups and log file growth issues

Backups and log file growth issues

Saturday, February 25, 2012

backups - complete, differential and transaction log

backups - complete, differential and transaction log

backups - complete, differential and transaction log

backups

Backups

Backups

backups

Friday, February 24, 2012

Backup/Restore Tran Log

Backup to Network Drive

Blog Archive

About Me