This is maybe a dumb question but I couldn't find a definitive answer
on BOL.
Looking at my backup script. If I issue a CHECKPOINT, does this truly
force all transaction log entries to the data file? Therefore, making
it unnecessary to BACKUP log (just BACKUP database is needed).
Louis
On the SQL Server the Event Viewer shows the same messages and errors every evening between 22:05:00 and 22:08:00. The following information messages are shown for every database:
"I/O is frozen on database <database name>. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup."
"I/O was resumed on database <database name>. No user action is required."
"Database backed up. Database: <database name>, creation date(time): 2003/04/08(09:13:36), pages dumped: 306, first LSN: 44:148:37, last LSN: 44:165:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{A79410F7-4AC5-47CE-9E9B-F91660F1072B}4'}). This is an informational message only. No user action is required."
After the 3 messages the following error message is shown for every database:
"BACKUP failed to complete the command BACKUP LOG <database name>. Check the backup application log for detailed messages."
I have added a Maintenance Plan but these jobs run after 02:00:00 at night.
Where can I find the command or setup which will backup all databases and log files at 22:00:00 in the evening?
Backup failed to complete the command backup database [ ] TO VIRTUAL DEVICE = ' { 853D3FC0 - 45EA -85B1 - 54F0EA379CAC } 24 ' WITH SNAPSHOT , BUFFERCOUNT = 1 , BLOCKSIZE = 1024
View 1 Replies View RelatedHi,
I run the follow command to backup the database log file after a full database backup.
backup log supl_pzhou2 to supl_pzhou2_log
and got the following message.
Processed 3 pages for database 'supl_pzhou2', file 'SUPL_Log' on file 2.
BACKUP LOG successfully processed 3 pages in 0.485 seconds (0.049 MB/sec).
The problem is, the log file .ldf is still the same size after the backup log command. it is 14GB.
THe datafile is only 2GB in size.
How do I write the backup log command properly so the log file can get truncated?
Thanks in advance.
Pingx
What ius the synthax to backup an SQLServer DB from the command line ?
Thanks
I need to have RETAINDAYS=6hours on a backup command. How can I code that,seems like it should be in integer only,any work-around to have the retain days only few hours(less than a day)?
View 1 Replies View RelatedHi there, I'm not exactly sure where to post this question, so I'll post it here.
I have 2 Windows XP machines, not part of a network domain. One of the XP machines is running SQL Server 2005 Express edition, lets call this DB machine. The other machine is just running my application - App machine. As part of my application, I want to be able to do a backup of the database.
The DB machine is also running the application. If I log into my application on the DB machine using SQL Server Authentication, and run the backup it works fine. (It's using the T-SQL BACKUP command). If it log into my application on the App machine, and try to do the backup, I'm getting an error saying that "The user is not associated with a trusted SQL connection". The same user is being used in both scenarios, and this user can update the database fine on the App machine, so it's not really a connection problem, it seems a permission problem. The SQL user I have created is a member of the db_backupoperator role for the required database.
Is anyone aware as to why I would be getting this error?
This script will read the contents of a DB backup file, and generate a restore command.
Set the value of parameter @backup_path to point to the backup file, run in Query Analyzer, cut/paste the output into another Query Analyzer window, modify as necessary, and run.
This is just a barebones script to demo how this can be done. Modify as necessary to meet your own needs.
Works in SQL 2000 and 7.0. May work in SQL 2005, but it is not tested.
-- Create Restore Database Command from DB Backup File
set nocount on
go
declare @backup_path nvarchar(500)
select @backup_path =
-- Path to Backup file
'\SERVERNAMESHARE_NAMEMY_DB_BACKUP_FILENAME.BAK'
create table #header (
BackupNamenvarchar(128)null,
BackupDescriptionnvarchar(128)null,
BackupTypeintnot null,
ExpirationDatedatetimenull,
Compressedintnot null,
Positionintnot null,
DeviceTypeintnot null,
UserNamenvarchar(128)not null,
ServerNamenvarchar(128)not null,
DatabaseNamenvarchar(128)not null,
DatabaseVersionintnot null,
DatabaseCreationDatedatetimenot null,
BackupSizedecimal(28,0)not null,
FirstLsndecimal(28,0)not null,
LastLsndecimal(28,0)not null,
CheckpointLsndecimal(28,0)not null,
DatabaseBackupLsndecimal(28,0)not null,
BackupStartDatedatetimenot null,
BackupFinishDatedatetimenot null,
SortOrderintnot null,
CodePageintnot null,
UnicodeLocaleIdintnot null,
UnicodeComparisonStyleintnot null,
CompatibilityLevelintnot null,
SoftwareVendorIdintnull,
SoftwareVersionMajorintnull,
SoftwareVersionMinorintnull,
SoftwareVersionBuildintnull,
MachineNamenvarchar(128)not null,
Flagsintnull,
BindingIDuniqueidentifier null,
RecoveryForkIDuniqueidentifier null,
Collationnvarchar(128)null,
Seqintnot null
identity(1,1),
)
create table #filelist (
LogicalNamenvarchar(128)not null,
PhysicalNamenvarchar(128)not null,
Typenvarchar(10)not null,
FileGroupNamenvarchar(128)null,
Sizedecimal(28,0)not null,
MaxSizedecimal(28,0)not null,
Seqintnot null
identity(1,1),
)
insert into #header
exec ('restore HeaderOnly from disk = '''+@backup_path+''' ')
insert into #filelist
exec ('restore FilelistOnly from disk = '''+@backup_path+'''')
declare @tab varchar(1), @cr varchar(2)
select @tab = char(9), @cr = char(13)+Char(10)
select
[--Restore--]=
case
when a.Seq = 1
then
@cr+
@cr+'restore database '+c.DatabaseName+
@cr+'from disk ='+@cr+@tab+''''+
@backup_path+''''+@cr+'with'+@cr
else ''
end+
@tab+'move '''+a.LogicalName+
'''to '''+a.PhysicalName+''' ,'+
case
when a.Seq = b.Seq
then
@cr+@tab+'replace, stats = 5 , recovery'
else ''
end
from
#filelist a
cross join
( select Seq = max(b1.Seq) from #filelist b1 ) b
cross join
( select DatabaseName = max(c1.DatabaseName)
from #header c1 ) c
order by
a.Seq
go
drop table #header
drop table #filelist
Results, modify as needed:
--Restore--
--------------------------------------------------------------------
restore database MY_DB
from disk =
'\SERVERNAMESHARE_NAMEMY_DB_BACKUP_FILENAME.BAK'
with
move 'PRIMARY'to 'D:MSSQLDATAMY_DB_PRIMARY.MDF' ,
move 'DATA1'to 'D:MSSQLDATAMY_DB_DATA1.NDF' ,
move 'DATA2'to 'D:MSSQLDATAMY_DB_DATA2.NDF' ,
move 'LOG'to 'D:MSSQLDATAMY_DB_LOG.LDF' ,
replace, stats = 5 , recovery
CODO ERGO SUM
Hi there, I'm not exactly sure where to post this question, so I'll post it here.
I have 2 Windows XP machines, not part of a network domain. One of the XP machines is running SQL Server 2005 Express edition, lets call this DB machine. The other machine is just running my application - App machine. As part of my application, I want to be able to do a backup of the database.
The DB machine is also running the application. If I log into my application on the DB machine using SQL Server Authentication, and run the backup it works fine. (It's using the T-SQL BACKUP command). If it log into my application on the App machine, and try to do the backup, I'm getting an error saying that "The user is not associated with a trusted SQL connection". The same user is being used in both scenarios, and this user can update the database fine on the App machine, so it's not really a connection problem, it seems a permission problem. The SQL user I have created is a member of the db_backupoperator role for the required database.
Is anyone aware as to why I would be getting this error?
How do you add a specific timestamp to a backup? For example, if the backups are going to the same drive location on disk and you want to retain 3 days worth of backups online, how do you add the timestamp to the filename to make each backup unique?
F:MSSQL.1MSSQLBackup and your user database is <xyz>_<timestamp>.bak
The user database dumps each night at 9 PM.
You want to keep 3 days of online backups.
One of my SQL Servers (SQL 2000 SP4) is reporting this error in the Maintenance Plan. But shortly after it shows this error is does successfully backup my Databases (per log history), however the plan is indicating "failure".
I have no idea how to resolve this nor why it is happening.
Rob.
Hi guys.
acutally my network dept. has change the backup file server IP address.. i m now hving problem for taking backup.
i have around 85 backup that runs everyday.. what i m doing now running every single command to drop backup device and then adding again.. but it's taking agess to do..
is there any simple script that just update the device path folder..
Thanks and looking foward.
-MALIK
Sorry if this is the wrong place for this, but I couldn't find a better place...
I have a .net application that calls a TSQL command to backup the database when the user clicks a button. I would like to give the user incremental feedback on what's going on.
So I hooked into the connection's InfoMessage event.
It gets called at the end of the command, not during. I read somewhere that you're supposed to be able to add "WITH NOWAIT" to a TSQL command to have it return immediately and then use the InfoMessage for updates, but the Backup command doesn't support it.
I also set a trace on the SQL Server and told SQL Server to backup using Enterprise manager hoping to catch what it was doing special to get these commands, and didn't see anything unique.
Does anyone have suggestions? I really need to show the progress of what's going on.
Thanks!
When I execute the following transaction log backup TSQL in SSMS
BACKUP LOG ProductDBÂ
TO DISK = 'D:Database_BackupTransProductDB.trn';
I get this error:
Msg 3201, Level 16, State 1, Line 2
Cannot open backup device 'D:Database_BackupTransProductDB.trn'. Operating system error 21(The device is not ready.).
Msg 3013, Level 16, State 1, Line 2
BACKUP LOG is terminating abnormally.
I also have a daily database backup job that puts the backup file under this directory.
D:Database_BackupData
So I changed the path to this and it still fails.
BACKUP LOG ProductDBÂ
TO DISK = 'D:Database_BackupDataProductDB.trn';
However if I change it to this path, it works:
BACKUP LOG ProductDBÂ
TO DISK = 'MyServerDataProductDB.trn';
This turns out to be the path I used a few weeks back when I backed up the log for the very first time. Â But I now wanted to store my transaction log backups in D:Database_BackupTrans. Â
I am running backups on SQL Sever express and I take backups via the batch file executing the TSQL as below. This works perfectly fine, I just need to be able to spool out the backup completion log. What TSQL command can I use to do that?
My batch file looks like this:
@echo off
sqlcmd -S SERVER01 -E -i H:master_scriptsTOM_FAM_log.sql
Which will the call the TOM_FAM_log.sql :
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = 'H:BackupTOM_FAM'
[Code] ....
Usually if we schedule the backups using the maintenance plan, we can chose reporting options that can spool the result of the backup to a text file. What is the T-SQL for the spool report to a text file.
This option produces a file that writes logs with the details I posted below. I would like to do the same or similar using T-SQL in my code.
Microsoft(R) Server Maintenance Utility (Unicode) Version 11.0.3000
Report was generated on "SERVER01".
Maintenance Plan: Backup logs
Duration: 00:00:00
Status: Succeeded.
[Code] ....
Can i force a checkpoint?How?Will it have any implication?
TIA
Please help.
I would like to checkpoint my transaction log every night before full backup.
Would this affect the transaction log sequence in the event of a restore.
I run SQL Server 2K SP 3 on WIN 2K SP 3.
Thank you.
Regards
Hi,
Has anybody encountered this situation before? DB on SQL Server 2000 SP4 with trunc log on chkpt option turned on. Checkpoint trace flags were turned on but noticing no checkpoints are being done on one specific DB resulting into growing transaction log. No open transactions.
Any ideas?
Thanks.
I see a line in sys.sysprocesses. The process's status is suspend and the command is CHECKPOINT. I have the information here exactly as it is on my monitor. It seems is consuming hi cpu. What should I do?
[/code]
spid: 10
kpid: 7416
block: 0
waittype: 0x0081
waittime: 232546
lastwaittype: CHECKPOINT_QUEUE
waitresource:
dbid: 1
uid: 1
cpu: 427046
physical_io: 36695
memusage: 0
login_time: 2007-04-04 10:01:32.787
lastbatch: 2007-04-04 10:01:32.787
ecid: 0
open_tran: 0
status: suspended
sid:0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
hostname:
program_name:
hostprocess:
cmd:CHECKPOINT nt_domain:
nt_username:
loginame: sa
[/code]
Canada DBA
I have a package that uses checkpoint restart. It is resposible for truncatings many sets of tables and then loading them. There are several ExecuteSQL tasks to truncate the tables and several corresponding data flows to accomplish the loads.
If a load fails I want the corresponding truncate task to be part of the restart otherwise duplicate data may be loaded. Normally, SSIS will start at the failed task. I read something about containers that led me to think that if I put the truncate & matching load pair in a sequence container that the container would be the restart point, but either I read it wrong or it's not working that way.
Anybody know how to accomplish what I want to do?
With 2005 SP1. Have built a SSIS package that successfully saves a checkpoint file and sometimes successfully restarts. (I've also built some others that are 100% reliable).
On the unsuccessful restart it appears as though the failed steps and subsequent steps do not execute. the package appears to "complete" though and the checpoint file is removed as though everything is fine.
On a successful restart the failed step reexcutes and everything works fine.
The issue appears that when a failed step finishes at the same time as a successful step finishes there is contention in the process that writes the checkpoint file out and the checkpoint file is corrupt. The failing step runs in parallel with a successful step and the execution times are very similar so task A may complete before or after task B.
Contents of a good checkpoint file follows <DTS:Checkpoint xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:PackageID="{3BFFF2F9-74BA-4CE9-8435-81CC198E8144}"><DTS:Variables DTS:ContID="{3BFFF2F9-74BA-4CE9-8435-81CC198E8144}"/><DTS:Container DTS:ContID="{3655F83D-5EA5-4F16-9B8F-520582A1229A}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{DB2D7A57-D405-4B11-AF4A-41B331EE3F15}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{DFC6A95F-CCFA-4FD9-B604-FCBD722B47D8}" DTS:Result="0" DTS:PrecedenceMap="YYY"/></DTS:Checkpoint>
Contents of a bad checkpoint file follows
<DTS:Checkpoint xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:PackageID="{3BFFF2F9-74BA-4CE9-8435-81CC198E8144}"><DTS:Variables DTS:ContID="{3BFFF2F9-74BA-4CE9-8435-81CC198E8144}"/><DTS:Container DTS:ContID="{3655F83D-5EA5-4F16-9B8F-520582A1229A}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{9FAD4043-8D5F-4044-915A-87ECABDE6A7C}" DTS:Result="1" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{DB2D7A57-D405-4B11-AF4A-41B331EE3F15}" DTS:Result="0" DTS:PrecedenceMap=""/><DTS:Container DTS:ContID="{DFC6A95F-CCFA-4FD9-B604-FCBD722B47D8}" DTS:Result="0" DTS:PrecedenceMap="YYY"/></DTS:Checkpoint>
Has anyone seen this behaviour before?
Hi,
I've set up a number of jobs (not a maintenance plan) via a script in SQL 2005. These jobs do the following:
1) Full backup every sunday night
2) Differential backup every weeknight
3) Log backup every hour
The database is obviously in the full recovery model.
The backups all seem to be running, with one issue - the log file is still growing and is not being truncated. I was under the impression that a log backup should result in the log being truncated after each full backup. However, this does not seem to be the case.
Is there anything obvious I've missed that needs to be set up, or is there a way I can check that the full backup is actually setting the appropriate checkpoint and that the log backups are 'seeing' these checkpoints?
Thanks
If i have 3 Tasks in my control flow with checkpoint enabled and the transactionoption of the tasks is required,
Transaction option of the package is supported.
if the second task fails , the package restart from the first task when its running again instead of using the checkpoint and begin from the second task
Can anyone tell me if it€™s a bug in SSIS?
I have the need to delete old backup files via TSQL job. Found this solution online:
PushD "
emoteservershareDIFF" &&(
forfiles -m *DIFF*.sqb -d -1 -c "cmd /c del /q @path"
) & PopD
It works remotely if I run it via command prompt. But when I add this to a TSQL job on my remote SQL instance, it runs without deleting anything. What I'm missing?
Details: MSDE 1.0 / SP4 - Windows 2000 Pro
I have a database that has Truncate on Checkpoint set for the Log file. The Log file is set to AutoGrow. Is it necessary to to run dbcc shrinkdb (or the like) to get Log file to contract? Is there any harm in not contracting the Log file? I'm looking for best efficiency and least-likely-to-fail path as DB sits 'really remote' and there is little opportunity for observation.
Does anyone have any recommendations on re-indexing? I have one table that bears the most growth. It has a clustered index. What would be a suitable data point to watch? I run a SP to save DBCC SHOWCONTIG info along with the duration of a test query, but haven't seen a clear breakover point.
TIA -RC
Can anyone assist with this problem.
Every now and then my overnight backups (backup Exec) fail due to the truncate log on checkpoint being enabled. This occasionally occurs on Master MSDB databases. I have unchecked the truncate log on checkpoint box numerous times and the backups work fine. Then mysteriously the box is checked again and the backups fail once more. I am stuck as to why this can happen. Is there a generic stored proceedure that checks this box ?
Help with this will be welcomed.
The log on one of my databases keeps filling up, even though I have it set to truncate on checkpoint. the only real difference between this database and the others on my server is that it is built from the dump of another database (on another server) where the tables are marked for replication.
I'm wondering if the fact it is built from a replicating database could be causing this. I've noticed I can't drop any of the table, even though my database isn't set to replicate (or publish).
two questions
1) Any ideas?
2) Is there anyway I can make my server realize I'm not replicating so it will let me drop those tables? (nothing in Enterprise manager indicates that my database is replicating or publishing).
Thanks,
Jim
Hello,
I am DB Developer (not admin), excuse me if this is a silly question.
I don't know much about CHECKPOINT background But I feel, this process is slowing down performance of my sps which runs slower than normal in some cases.
Especially when I see any of my process goes in the suspended mode and its wait type is SLEEP_BPOOL_FLUSH and CHECKPOINTs process is also suspended and its wait type is CHECKPOINT_QUEUE.
More important anything else is... this background process (which I always find its spid is 11) BLOCKS all other user processes when it goes into suspended mode and its wait type is SLEEP_BPOOL_FLUSH
I dont know my analysis is correct (claiming checkpoint as culprit), need experts advice and help
can someone give info on checkpoint and how this effect server performance
We're running the Microsoft product SMS 2003 SP1 for software deployment, patching, hardware inventory, etc. The back-end is SQL 2000 Enterprise SP4 which is installed on the same box as the SMS 2003 SP1 product, and the DB is 145GB's.
We started noticing that the server would freeze every minute or so for 30 seconds. We started logging stats via perfmon and saw that the average disk queue length for the physical drive of F: would skyrocket between 400 - 500 for 30 seconds at the same time the freezing occurred. I have determined that this is occurring during the checkpoint. The recovery interval option is set to the default of 0 on SQL, when I changed the setting to every 5 minutes, the average disk queue length for the physical drive of F: would skyrocket between 400 - 500 every 5 minutes and would subside after 2 minutes. I understand the need for the checkpoint / recovery interval option, but don't believe this high average disk queue length should be occurring.
Does anyone know why this is happening and how to fix this ? The freezing of the box while checkpointing is killing me.
Thanks
I have a package with settings SaveCheckpoints=True and CheckpointUsage = Never. After the package failed I fixed the cause of failure by setting a database column to allow nulls. Then I went to our web app that we built to monitor package execution and clicked on the button to restart the package. The web app loads the package and then sets the CheckpointUsage property of the package object to 'Always'. Then it executes the package in a new thread. The package then produced this error:
Checkpoint file "E:Package1Checkpoint" failed to open due to error 0x80070005 "Access is denied.".
Since there was only one remaining task to run in the package I ran it manually.
Now here is the really interesting part. I then needed to run the same package but with different parameters. When I attempted to run it with the saved checkpoint settings (CheckpointUsage=Never, SaveCheckpoints=True) I got this error:
"An existing checkpoint file is found with contents that do not appear to be for this package, so the file cannot be overwritten to start saving new checkpoints. Remove the existing checkpoint file and try again. This error occurs when a checkpoint file exists, the package is set to not use a checkpoint file, but to save checkpoints. The existing checkpoint file will not be overwritten. "
So I then attempted to rename the checkpoint file so it would not interfere, however, it would not let me, saying that the file was in use.
So what I had to do was add a configuration entry which set SaveCheckpoints to False. Then I was able to run the package.
Any ideas?
hi everyone,
Does anyone ever used or created something like that? What kind of format it might be and so on...
TIA
Hi All,
Is there a checkpoint at record or row level? i mean let say i have 2 million rows and i want to transfer from one db to another, when i start tranfering about 1 million records the package fails, thus next time i run i want to continue from where it was, i.e. since 1 million records were transfered i don't want to repeat them, just like checkpoint at Task Level. Farther information I am working on SSIS.
Thank you,
Sami
I have several packages within secuence containers and into one main dtsx package with a checkpoint configuration and when I run it some succeed and some don´t. The problem is that when I rerun it checkpoint doesn´t seem to work ´cause some of the successful packages are rerun as well (and not skipped as it should be...) In other words, the process does not begin on the point of failure..
Seems to be that packages that finish after the failure point (and succeed) are not registered in the checkpoint file, then when I rerun the main package these succeeded packages are rerun too....
Any ideas??