Hello all - I have a SQL Server 2000 database setup using the Full Recovery Model. Each night, we backup the entire database, and as such would like to truncate the log at this time as well.
Is the best way to do this to also backup the Transaction Log, and then perform a DBCC SHRINKFILE command? It just seems like there should be an easier way...?
Thanks!
Hi everyone I am fairly new to SQL server and need some help with creating backups. I am a little confused whether to append or overwrite my backup data when saving full, differential and transaction log backups to disk. Below is my current backup schedule.
1) A full backup every night at 2:00a.m. that overwrites my existing backup. Should this be an appended backup? Wouldn't my file keep growing? How do I manage the backup file size?
2) Differential backups every two hours that is set to append. How do I stop this file from growing? Should I be controlling the size of my backups through the backup set expiry option?
How do I incorporate transaction logs into my backup schedule and manage the size of the log? Should the log backups also be appended or overwriten?
I appreciate everyones help in advance.
question 1:
i found that database log file can contain more records after performing backup database statement.
for example:
i create a database and limit the log file to 2mb. then i create a table and insert data.
If i backup the database before i insert data , the database file can contain 192 records unitl the log file is full.
If i don't perform the 'backup database' statement.
The 'dbcc sqlperf(logspace)' indicate the utilization ratio is less than 40% after inserting 192 records
why?
I list my code:
Code Snippet
create database db_test
on primary
(
name=db_test,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test.mdf'
)
log on
(
name=db_test_log,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test_log.ldf',
maxsize=2mb
)
go
backup database db_test to disk='db_test.bak' --- if i don't execute this line, log file can contain a lot of record
go
create table db_test..table1(col char(8000))
--insert data to fill up the database log
declare @n int
set @n=0
while @n<192
begin
insert into db_test..table1 values(replicate('a',8000))
set @n=@n+1
end
question 2:
i create a database and limit the log file to 2mb. Then i create a table and insert data in an endless loop.
After the inserting operation executing for a while, the 9002 error occurs, indicate the log file for the database is full.
But the 'dbcc sqlperf(logspace)' command indicate the unilization ratio is low, and log_reuse_wait_desc in sys.database is 'CHECKPOINT'
And I can insert data , and i'm sure the state of log_use_wait_desc is 'CHECKPOINT'.
As i known, the checkpoint can't truncate log under full recovery model. Only the back log operation can truncate the transaction log.
So log is not full, why 9002 error is encounterd. and why the log_reuse_wait_desc return 'CHECKPOINT'?
I list my code:
Code Snippet
create database db_test
on primary
(
name=db_test,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test.mdf'
)
log on
(
name=db_test_log,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test_log.ldf',
maxsize=2mb
)
go
create table db_test..table1(col char(8000))
--insert data to fill up the database log
declare @n int
set @n=0
while @n<>-1
begin
insert into db_test..table1 values(replicate('a',8000))
end
any suggestions?
thanks in advance.
Dear all
I am pretty new to the database administration and was wondering if i could get some advice here so i have a head start.
Unfortunalety i have came to the conclusion the hard way that the SQL Agent on Ms SQL 2000 doesnt work properly when it comes to database backups. I discovered that the backups it creates and it claims that are performed succesfully do not work.
So i was wondering what would be the proper way to go in creating a backup of a highly critical database. The database is in MS SQL 2000. I am not required to have the application roll over to the backed up data in case the main one crashes. But it is absolutely necessary to be able to restore the databse to a most recent working copy. So i guess performing a backup once a day would do. Loosing data from a day would not be such a big deal since i have other ways of restoring that days data, as long as i have the full database up to the previous day.
I was looking at database mirroring but that is not available with sql 2000.
Also i though of as a possibility to run a replication on the SQL 2000 database and replicate to SQL 2005 database which aparently has got the SQL agent working properly.And then run the agent on the SQL 2005 database which would backup the copy daily?
Any advice is apreciate and if there are any white papers or books i could look at that would be great.
Cheers
I want to set a full and differential backup to one database in sql server 2000.
Is there a way to set both full and differential to just one database.
i want the full backup weekly once and differential every day to set up.
Please let me know
Hi Everybody,
I am kind of new to MS SQL server databases. I like to take a full export at database level. When I use DTS wizard, it did allow me to take one table at a given time. I have 1000's of table in my database. Manually doing so is not possible. Should i call the 'bcp' command line utility 1000 times to collect the table data to 1000 different flatfiles or is there any provision to take export of all the 1000 tables in one single command/tool.
Many thanks.
Hi all,If I set recovery model SIMPLE then truncation of log files will beautomated , but I will losedatabase changes made since the last backup was taken.Is there any way to automate truncation of log file andbackup of database upto last change has been done????Plz Help.
View 3 Replies View RelatedHi,
We have recently upgraded our production server from a dual pentium II 400mhz server with 384Mb of RAM to a triple 700mhz Pentium III system with 2gb of RAM . However, since switching over, all of our Access queries on the SQL7 databases are either running extremely slowly or not at all despite the DSN's being set up correctly . Does anyone have any ideas why, and more importantly, how I can resolve this???
Thanks
Pete Burton
(IT Support)
Durham Aged Mineworkers Homes Association
Need 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 would like to know what happens if i shrink the database with truncate only option and do a full backup or transaction log backup ? are the full backup or transaction log backup valid? I know that the performance of the database is bad if i shrink the database. What happens to full backup or transaction log backups?
View 9 Replies View RelatedCan anyone show me how to upload a pdf file to SQL 2005 and perform a Full Text Search on it and to extract record based on a word in the pdf. I have looked all over the web and I couldn't get any thing working as mentioned in other forums. Please mail me a small example if possible on salman.anwar@wmich.edu ASAP.
View 1 Replies View RelatedGreets!
I have been told that simply stopping the SQL server service and backing up the data directory is all I have to do to do a backup of my data. Is this accurate?
Thanks,
Jimmy Ipock
For developers, we often have a need to backup a production database and restore it on local or integration machines. This production database is enabled for service broker and operates at a relatively high traffic level. When the database is backed up, the size is nearly 12GB; when SET NEW_BROKER is subsequently executed on the restored database, the size goes down to about 800MB. It appears that most of this is residing in the xmit queue. So, my question is: how best to backup a production database with queues activated, etc. without ending up with a 12GB backup?
Thanks.
If a full backup kicks off at 6pm within SQL Server, and takes 2 hoursto complete, will transactions between 6pm and 8pm be included in thebackup?Tks
View 1 Replies View RelatedWindows 2003 backup utility uses the shadow copy option that allows it to copy open files.
Therefore, can I use this utility to backup the .mdf and .ldf files for my SQL 2000 database?
I can then attach the .mdf files if I need to restore the database to another server.
Can anyone tell me if this is safe? I've tried it and it worked but I'm worried there maybe some lurking danger in using this approach.
how do i modify this so that it will truncate the log before backup?
SQLMAINT.EXE -D mimi-BkUpDB E:SQLBackups -BkUpMedia DISK -DelBkUps 1 -Rpt E:SQLMaintmimi_maint.rpt
thanks
According to BOL<---BACKUP LOGSpecifies a backup of the transaction log only. The log is backed upfrom the last successfully executed LOG backup to the current end ofthe log. Once the log is backed up, the space may be truncated when nolonger required by replication or active transactions.--->Does this mean I could do something else to truncate the log or is itsaying that the backup log command may truncate the log if it feelslike it?I want ot get a log from a client site onto my server for analysis butI want ot make absolute certain that my backing up the log on theirserver won't truncate it there.
View 7 Replies View RelatedWe have a script that I had to rework a little bit for 2005 that doesa full backup for every database on the server... For some reason onsome nights the script does not backup all databases... Its like itskips over it for some reason... Output of the script below on thenight in question was:Executed as user: NT AUTHORITYSYSTEM. master [SQLSTATE 01000](Message 0) Status is ONLINE dbname / dbdevice = master / SQLBUmaster[SQLSTATE 01000] (Message 0) Processed 376 pages for database'master', file 'master' on file 1. [SQLSTATE 01000] (Message 4035)Processed 2 pages for database 'master', file 'mastlog' on file 1.[SQLSTATE 01000] (Message 4035) BACKUP DATABASE successfullyprocessed 378 pages in 0.169 seconds (18.298 MB/sec). [SQLSTATE 01000](Message 3014). The step succeeded.A normal night on this particular server includes two other databaseslike below:(Message 0) Processed 376 pages for database 'master', file 'master'on file 1. [SQLSTATE 01000] (Message 4035) Processed 2 pages fordatabase 'master', file 'mastlog' on file 1. [SQLSTATE 01000] (Message4035) BACKUP DATABASE successfully processed 378 pages in 0.711seconds (4.349 MB/sec). [SQLSTATE 01000] (Message 3014) msdb[SQLSTATE 01000] (Message 0) Status is ONLINE dbname / dbdevice =msdb / SQLBUmsdb [SQLSTATE 01000] (Message 0) Processed 688 pages fordatabase 'msdb', file 'MSDBData' on file 1. [SQLSTATE 01000] (Message4035) Processed 5 pages for database 'msdb', file 'MSDBLog' on file1. [SQLSTATE 01000] (Message 4035) BACKUP DATABASE successfullyprocessed 693 pages in 3.743 seconds (1.516 MB/sec). [SQLSTATE 01000](Message 3014) SBC [SQLSTATE 01000] (Message 0) Status is ONLINEdbname / dbdevice = SBC / SQLBUSBC [SQLSTATE 01000] (Message 0)Processed 11577184 pages for... The step succeeded.The script is schedule to be run nightly and it looks like this:ALTER PROCEDURE [dbo].[usp_backupFull] ASset nocount onDeclare @start_time datetime,@end_time datetime,@backupsize real,@status varchar(100),@cmd nvarchar(255),@monitor_server varchar(50),@recovery varchar(100),@db_name varchar(100),@dev varchar(100),@logvarchar(100),@backup_folder varchar(100),@dev_path varchar(255),@log_pathvarchar(255),@message_text varchar(255),@subject_text varchar(255),@error varchar(50)Select @backup_folder ='D:SQLBU'--Select @monitor_server ='MONITOR'CREATE TABLE #error (dbname varchar(50), error varchar(50))DECLARE db_cursor CURSOR FOR SELECT name FROM master..sysdatabaseswhere name not in ('Northwind','pubs','tempdb','model')OPEN db_cursorFETCH NEXT FROM db_cursor INTO @db_nameWHILE @@FETCH_STATUS = 0 BEGINSELECT @dev = 'SQLBU' + @db_nameSELECT @dev_path = @backup_folder + @dev + '.bak'SELECT @log = 'SQLBU' + @db_name + 'LOG'SELECT @log_path = @backup_folder + @dev + '_log.bak'PRINT ''PRINT @db_namePRINT ''IF NOT EXISTS (SELECT name FROM master..sysdevices where status=16and name=@dev) BEGIN-- Create new backup device if it doesn't existEXEC sp_addumpdevice@devtype='Disk',@logicalname=@dev,@physicalname=@d ev_pathPRINT ''ENDSelect @recovery =CONVERT(varchar(100),DATABASEPROPERTYEX(@db_name,' Recovery'))IF @recovery <'SIMPLE' BEGINIF NOT EXISTS (SELECT name FROM master..sysdevices where status=16and name=@log) BEGIN-- Create log backup device if it doesn't exist and logging not setto SIMPLEEXEC sp_addumpdevice@devtype='Disk',@logicalname=@log,@physicalname=@l og_pathENDENDSELECT @status = CONVERT(VARCHAR(100),DATABASEPROPERTYEX(@db_name,'Status'))print 'Status is ' + @status + ' dbname / dbdevice = ' + @db_name +' / ' + @devIF @status = 'ONLINE' BEGINSELECT @cmd = 'BACKUP DATABASE ' + @db_name + ' TO ' + @dev + ' WITHINIT'EXEC(@cmd)IF @@ERROR <0 BEGININSERT INTO #error VALUES (@db_name,'Full backup Failed-Check Log')--Select @cmd = 'osql -U srvMonitor -P backups -S ' +@monitor_server + ' -d Monitor -Q "insert into backups([date],server_name,db_name,backup_type,status) values (GETDATE(),'''+@@servername +''',''' + @db_name + ''',''Full'',''Failed'')"'--Execute master..xp_cmdshell @cmdENDELSE BEGINSELECT @start_time = backup_start_date, @end_time =backup_finish_date, @backupsize = (backup_size / 1024 / 1024) FROMmsdb..backupset WHERE (type = 'd') AND (database_name = @db_name) AND(backup_finish_date DATEADD(mi, -1, GETDATE()))--Select @cmd = 'osql -U srvMonitor -P backups -S ' +@monitor_server + ' -d Monitor -Q "insert into backups values(GETDATE(),'''+ @@servername +''',''' + @db_name +''',''Full'',''Success'',''' + cast(@start_time as varchar(50)) +''',''' + cast(@end_time as varchar(50)) + ''',' + cast(@backupsize asvarchar(50)) + ')"'--Execute master..xp_cmdshell @cmdENDPRINT ''SELECT @recovery =CONVERT(VARCHAR(100),DATABASEPROPERTY(@db_name,'Is TruncLog'))IF @recovery <'1' BEGINSELECT @cmd='BACKUP LOG '+@db_name+' TO ' + @log + ' WITH INIT'EXEC(@cmd)IF @@ERROR<>0 BEGININSERT INTO #error VALUES (@db_name,'Log backup Failed-Check Log')--Select @cmd = 'osql -U srvMonitor -P backups -S ' +@monitor_server + ' -d Monitor -Q "insert intobackups([date],server_name,db_name,backup_type,status) values(GETDATE(),'''+ @@servername +''',''' + @db_name +''',''Log'',''Failed'')"'--Execute master..xp_cmdshell @cmdENDELSE BEGINSELECT @start_time = backup_start_date,@end_time=backup_finish_date, @backupsize = (backup_size / 1024 / 1024) FROMmsdb..backupset WHERE (type = 'L') AND (database_name = @db_name) AND(backup_finish_date DATEADD(mi, -1, GETDATE()))--SELECT @cmd = 'osql -U srvMonitor -P backups -S ' +@monitor_server + ' -d Monitor -Q "insert into backups values(GETDATE(),'''+ @@servername +''',''' + @db_name +''',''Log'',''Success'',''' + cast(@start_time as varchar(50)) +''',''' + cast(@end_time as varchar(50)) + ''',' + cast(@backupsize asvarchar(50)) + ')"'--Execute master..xp_cmdshell @cmdENDENDPRINT ''ENDELSE BEGINPRINT 'The database was not backed up due to options that were setunder sp_dboptions'PRINT ''INSERT INTO #error VALUES (@db_name,'DB Not backed up due to DBoptions')--Select @cmd = 'osql -U srvMonitor -P backups -S ' +@monitor_server + ' -d Monitor -Q "insert into backups([date],server_name,db_name,backup_type,status) values (GETDATE(),'''+@@servername +''',''' + @db_name + ''',''Full'',''Not Backed up -Check DB Options'')"'--Execute master..xp_cmdshell @cmdENDFETCH NEXT FROM db_cursor into @db_nameEND--WHILE-- Open error cursor --DECLARE db_error CURSOR FOR SELECT dbname,error from #errorOPEN db_errorFETCH NEXT FROM db_error into @db_name,@errorWHILE @@FETCH_STATUS = 0 BEGINSELECT @message_text = @error + ' for ' + @db_nameSELECT @subject_text = '!!!!!! ' + @@servername + ' - Backupfailed for ' + @db_name + ' !!!!!!'--exec msdb..usp_Alerts @mess = @message_text ,@subj=@subject_textFETCH NEXT FROM db_error into @db_name,@errorEND --WHILEDROP TABLE #errorPRINT ''DEALLOCATE db_cursorDEALLOCATE db_errorset nocount off---------------------------------------------------------------Any help I would appreciate it... As you can see from the output aboveit looks like its not even getting the database name to backup in thecursor. But that just doesn't make any sense to me... why could thatbe.
View 2 Replies View RelatedHello
I wanna use backup database task in SSIS to implement SQL statement
BACKUP LOG WITH truncate only.
But in this task, seems that you must at least to choose a location to store backup. No way to just truncate log in SSIS?
any help will be appreciated.
I've got the below and have several variation and still cant seem to find a perfect way to query the server to bring back that last full backup per db. I'm shopwing mutilple records in the backup set db w/ type = 'D'. I look online and type D = Database. Which i assumed it meant full database backup. Apparently not. Try running the below on one of your full databases. Then check to see if the date is actually the last backup date.
DECLARE @db_name VARCHAR(100)
SELECT @db_name = DB_NAME()
-- Get Backup History for required database
SELECT TOP ( 30 ) s.database_name,
m.physical_device_name,
[Code] ....
Hi,
After some advice - I have a SQL Server 2005 database which is part
of an anti-virus setup. The main database is 25Gb is size, and it
is running in simple recovery mode. There are two backup jobs in
place, one to do a differential backup each Mon-Sat, and one to
do a full backup on Sun. Although the backups do get done they
are taking 5 hours to do. Any wiz out there care to suggest what
the problem is, I would've though that maybe an hour was more
acceptable ?
Cheers,
Gordon
Hi,
Is there any option to restore a specific object from a full database backup file?
I have been unsuccessful in getting the Backup Log with
No Truncate option to work.
Any tips, tricks, suggestions, etc., would be appreciated.
Thanks!!!
This is a silly question but I am not sure of the answer.
Each day I do a complete DB backup and houly I backup the Translog file with the append mode.
Question Is the Transaction log truncated each time I do a Transaction Log Backup?
Using 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 RelatedWe 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 RelatedI 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 RelatedI want to know how people are backup up their win2k system and SQL 2000 server. If you want a run backup once a night and first backup win2k file system and then run backup using sql agent will that work?
Thanks.
Recently I have faced one DBA interview, below is the question they asked me.
" Does AlwaysON secondary replica support Full backup, if it is not why"?
I know AlwaysON secondary replicas support only copy_only and tlog backups, why they wont support full backup?
If data is modified (by an insert, update, or delete) while the backup is running, will the backup contain those changes or will it be added to the database afterwards?
View 2 Replies View RelatedI am sending out an SOS.
Here is the situation:
We recently upgrade to 2005(sp). We have one report that ran fine in 2000 but leaves out data from certain columns (date related) in the results, so we chalked it up to being a non compatiable issue. So, I decided to try and switch the DB back to 2000 compatibility (in our test env) and then back to 2005. After that the report started returning the proper data. We can€™t really explain why it worked but it did. So we thought we would try it in prod (we knew it was a long shot) and it didn€™t work. So the business needs this report so we thought we would refresh the test system from prod, but now we are back to square one. I was wondering if anyone else has heard or seen anything like this. I am open to any idea€™s, no matter how crazy. J The systems are configured identically. Let me know if you need more information.
Thank you. Scott
If 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.