Hi, I am reading the book of MCTS Self-Pace Training Kit. If I set up the option of Recovery Model in the properties of a database, Does SQL Sever will generate/update the log file per transaction? What is the relationship between Transaction Log files and Recovery? When we want to recover a database, we can use the database's backup. Why we need to keep Transaction Log?
Sincerely,
Julian
We take a full backup in the early morning and hourly transaction log back during the working hours for one database in the production server. The application team made certain changes to the design of the said database in their development server. The backup from the development server was restored to the production server during working hours. After the restoration should we take a full backup before next transactional logbackup? Would the transactional log backup with out a full backup after the restoration of a database be valid?
View 5 Replies View RelatedHello, everyone:
I just heard that for restore purpose, ths full backup and transaction log backup should be from one maintenance plan. Otherwise transaction log backup files cannot be restored after restoring full backup files.
Is it true? Can anyone offer official documents?
In my system, full and transaction backups are from one maintenance plan. Restores are doing fine. I am not sure that ideal is true or not.
Thanks
ZYT
Please correct me if I am wrong with this:
I am using SQL Server 2000, when I do a backup I use the database maintenance plan at enterprise manager. I select my database and then I schedule the complete backup and transaction log backup to "everyday at 22:00:00"
My question is, do I really need to do the transaction log backup? If I am not wrong with the complete backup I can already recover all my data, the transaction log backup is useless if done at the same time that I do the complete backup, right?
I have this database "DB1" which is in FULL recovery mode.
I run full daily backup -Monday to Saturday
DB1_Mon.bak
DB1_Tue.bak
DB1_Wed.bak
DB1_Thur.bak
DB1_Fri.bak
DB1_Sat.bak
Come Sunday - for the first time I have run transaction log backup.
DB1_Sun.trn
And now, I need to restore DB1 - but the only full backup that I have is the Monday - "DB1_Mon.bak"
Is my database complete if I restore only "DB1_Mon.bak" plus the "DB1_Sun.trn"?
Jeboy
Please correct me if I am wrong with this:
I am using SQL Server 2000, when I do a backup I use the database maintenance plan at enterprise manager. I select my database and then I schedule the complete backup and transaction log backup to "everyday at 22:00:00"
My question is, do I really need to do the transaction log backup? If I am not wrong with the complete backup I can already recover all my data, the transaction log backup is useless if done at the same time that I do the complete backup, right?
hi, I would like to know the correct reaction for a crash in both senarios.
First senario,
I made a full back up at 6 am , then scheduled sql server to make transaction log back up every 2 hours (8,10,12,2 pm,4,6,8) . If I have a crash at 12:30. How would I resotre the data in the first senario....Can I restore the full back up done at 6 am then restore the last transaction log backup ( which is 12 Noon ) . I am not sure If I need to resotre the whole tran from 6 am till the time it was crashed.
Second senario,
I made a full back up at 6 am, then scheduled sql server to make Incremental backup every 2 hours (8,10,12,2 pm,4,6,8) . If I have a crash at 3:00 pm. How would I resotre the data in the second senario. ....Do I restore the full backup at 6 am then restore each incremental backup backwords ( 2,12,10,8)
AS you can see, I am not sure how to deal with this issue, I do appreciate your feedback.
Best regards
Ahmed
In MS SQL 2005 when you do a Full Backup does it also backup andtruncate the transaction logs or do I need to back the transactionlogs up separately?Thanks.Brian
View 4 Replies View RelatedUsing Ola Hallengren's scripts I do a full backup of a database on a Sunday. Then differential backups every 6 hours and log backups every hour. I would like to keep a full week of backups based off the full backup done on Sunday. Is there a way for me to clear out the diff and log folders after the successful full backup on Sunday nights?
View 2 Replies View RelatedNeed to restore database,here's the scenario:
Data got deleted on Friday evening, need to have database restored to FRiday afternoon and also some data has been entered on Monday, which needs to be there.
I have a database that is just over 1.5GB and the Full backup that is 13GB not sure how this is since we have compression on for full backups and my other full backups are much smaller than there respective databases...Now my full backup is taken every Sunday night and the differentials are taken every 6 hours after the full backup. Now I have been thrown into this DBA role with little to no experience just what I have picked up and read. So my understanding of backups are limited but what I think I understand is that we take a full backup and the differential only captures what changes in the database so my question is why is my database 1.5GB but my differential is 15.4GB? I have others database that are on the same instance and don't seem to have this problem. I also just noticed that we do not rebuild the index before a full backup like we do on other instances...
View 13 Replies View RelatedIf my backup starts at 8PM and take 1 hour to complete, will the changes made to the database during that hour be captured in the full backup?
Stated another way, will my backup be a snapshot of:
a) 8PM when the backup started
b) 8PM with some of the changes made between the hour
c) 9PM when the backup finished?
Anybody know the exact way SQL Server handles that logic?
Thanks,
Marc
Hi
I am using the Simple recovery model and I'm taking a weekly full backup each Monday morning with differentials taken every 4 hours during the day.
On Wednesday afternoon, a programmer ran a process that corrupted the db and I had to restore to the most recent differential. It was 5pm in the afternoon and a differential backup had just occured at 4pm. No problem, I figured.
I restored the full backup from Monday morning and tried to restore the most recent differential backup. The differential restore failed. Since I had used T-SQL for the initial attempt, I tried using Enterprise Manager to try again.
When viewing the backup history, I see my initial full backup taken on Monday plus all the differentials. BUT, on closer inspection, I noticed another full backup in the backup history that was taken early Tuesday morning. I can't figure out where this Tuesday morning full backup came from. It wasn't taken by me (or scheduled by me) and I'm the only one with access to the server. My full backups are usually named something like HCMPRP_20070718_FULL.bak. This erroneous full backup was named something like HCMPRP_03a_361adk2k_dd53.bak. It seemed like it was a system generated name. Not something I would choose. To top it off, I could not find this backup file anywhere on the server and when I tried to restore using this full backup, it failed.
Does anyone have any clues as to where this full backup might come from? Does SQL Server trigger a full backup on its own if some threshold is reached?
I ended up having to restore using the differential taken just before this erroneous full backup and lost a day of transactions.
Any insight is greatly appreciated.
Hello,
I have MS SQL 2005 server with 300+ databases on it. The application is set up that way that it creates a new database as needed (dynamically). Do not ask me why - I hate this design... So, it can create 3-4 databases a day (random time).
I've scheduled full backup of all databases to run once at night, and it runs just fine. Besides that, I have scheduled tran logs backup of all databases to run every hour. This backup fails from time to time with the following error:
Executing the query "BACKUP LOG [survey_p0886464_test] TO DISK = N'D:\backups\log backups\survey_p0886464_test_backup_200708072300.trn' WITH NOFORMAT, NOINIT, NAME = N'survey_p0886464_test_backup_20070807230002', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "BACKUP LOG cannot be performed because there is no current database backup.
BACKUP LOG is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
So, I think what happens is since my full backup of all databases are scheduled to run only once at night, and tran logs every hour, when new database is created during the day, there is no full backup for it, that is why tran logs backup fails. Becuase after the failure, if I run full backup again, then tran log runs just fine afterwards.
I am new to MS SQL Server, I am mostly working with Sybase IQ. Do you know if I can "trigger" full backup every time when new database created to avoid tran lof failure?
Or is it possible to schedule full backup to run if tran log backup fails?
Any advice will be much appreciated.
I have a full backup and several diff backup,now i want to restore
firstly,I restore full backup
RESTORE DATABASE ***
FROM DISK = 'D:databackup200610140000.bak'
WITH NORECOVERY
GO
it's working,then i don;'t know how to continue
Thanks in advance
If I create an adhoc db backup that takes, say 30 miuntes to complete, should I suspend the tran log backups that run every 10 minutes, until the full backup is complete?
Drew
Hi,
Using SQL Server 2005, we have a 2.8Gb database under the Simple recovery model. The database contains ~50M rows and each night ~60k rows are loaded(appended) to the database by a SSIS task.
We configured a Maintenance Plan which is executed once a week to perform a full backup of the database. The resulting backup file is ~2.8Gb, as expected.
We also configured another Maintenance Plan which is executed every day, a few hours after the SSIS task is executed, to perform a differential backup. To our surprise, the resulting backup file is about the same size as the full backup, ~2.8Gb when it should only be a few MB (only 60k rows are added to the database)
When we launch the "Restore Database" wizzard we clearly see the different backup set, Full and Differential but they all have about the same size (same for the physical backup file on disk).
Is there anything we are missing, why are the differential backup that big?
Thanks for any advice.
what is the differences between a differenctial backup and transaction log backup?
View 1 Replies View RelatedI neglected to backup the transaction log as part of the process of backing up the database. Now i only have the backup file for the database and no transaction log backup. When i try to do a restore on the database, i get the error on a "tail log missing" message (which i'm assuming is that it's looking for the t-log backup?).
Is it possible to restore or even restore to a new database? I'm only looking to retreive data from 2 tables within the backup file.
Thanks!
SQL Server 2005 on Windows 2003 Server x64.
In SQL Server, whether the full backup of a database includes the backup of the log?
View 1 Replies View RelatedHi:
I have 30 databases on sql server 2005 that I need to do a full backup every morning at 7:00 and tran log backup every 30 minutes until 7:00 PM. If I create a maintenance plan for a backup using the wizard I have the option of starting a full backup at 7 am and then an option of doing tran log backups every hour using a different schedule. I plan on selecting the option to create a different folder for every database. I just need to confirm that in this way the way to restore the data would be
1. to restore a full backup
2. apply all the tran logs depending on the time they want to recover back to.
I just think this is the easiest approach to have 30 databases on the same backup scheme instead of creating a separate backup device for each database and doing a full backup on that device and appending all tran logs to that device which means just 1 bak file versus the above strategy with a number of tran log files. Please advise.
Thanks
How can I clear some logs in the transaction log?
I also found some solutions that must run DBCC. How to run DBCC in SQL Server 2000?
Thank you.
I have a transaction log which is 1 Gb and only has about 40 Mb free. When I run DBCC loginfo I find the first active log dates back to the middle of August. Does any one have any suggestions on the best way to approach this situation. How can I query the transaction log to find out what the old transactions are? I was going to use the detach database, rename log, attach database to shrink the log but don;t want to do this incase there is active data in the log. We are doing hourly log dunps.
Thanks
Grant
Hi all
I found that my transaction log is beig filled up and I tried to truncate it from DB properties window
and I failed.
I want to know how to truncate it and how to prevent it from growing up untill it's filled
Thanks
Mohamed
Our transaction log filled up the hard drive on the server and we are looking for a quick and easy way to delete it. Any ideas? We can't easily back it up because it is over 10500 meg in size.
View 4 Replies View RelatedHi,
I need help fast. I have a SQL Server 6.5 SP2 database that is 5000 MB and the transaction log is 1000 MB the log is 98 % full but will not truncate. I have tried all the truncate commands (Truncate only, no_log, DBCC) but it will only trucate the log like 10 MB.
Dose anyone know how to truncate this log??
Any help will be appreciated.
Thanks in advance for you help.
I have a problem with the transaction log of a database. when i truncate it and check the properties if says there are 0 MB available even though it is allocated 300 MB. this seems to be causing alot of problems.
Any idea why the log will not truncate??
regards
ML
I am getting an error when I try to DTS data to a database that
the transaction log is full.
I do: backup transaction db_name with truncate_only,
but I still get the error.
Any ideas on how to resolve this are appreciated.
Kellie
How do i empty transaction log? create another file and delete the other? or use dbcc shrinkfile or dbcc shrinkdatabase, w/c both im confused too.
View 2 Replies View RelatedHi there,
I'm still fairly new to SQL server and I've just ran into a problem when dropping some tables from my DB as my transaction log has become full. I looked at books online and it basically said to backup the log so it will also truncate the log. However I've ran out of disk space on my data drive so can't take a backup.
Is there a way to just truncate the active log to shrink the size?
Hope you can help??
Thanks
S
Today, one of our backups failed due to the transaction log being full. I expanded the transaction log and then the backup completed normally. For a short term resolution, I increased the maximum allowed size on our transaction log files.
We are doing a database backup only as follows:
backup database xxxxx to xxxx_data with init
I did find a note stating that the transaction log will eventually become full if doing a transaction log backup only. We do not generate a lot of transactions so I had not seen a need to backup the log during the day. The full database backup is done daily.
My question is, in order to permanently resolve this error do I have to:
- take a transaction log backup as well. Should I take it take right after the full database backup or at some later point.
- do I have to schedule/run the shrink database command.
iHi everyone
I have a big transaction log took over all the disk space, I know I can backup the log to free the space but my backup drive is on the same drive. Is there any other way to clean the Transaction log without backing up?
Thanks so much!!!!!!!!!!!!!
I was trying to create a table on a Database and it gave me this message:
'tblSuggestion' table - Unable to create table. The transaction log for 'Database' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
And I don't see the log_reuse_wait_desc column anywhere.
Any ideas?
dave