Having a lot of problems with backup device creating backups with a new transaction log for each day. This is causing the backups to grow way to fast. Seems to be random with our clients. Created new device backups but getting same problem. A manual backup selecting overwrite all existing backup sets will fix it. But starts the cycle all over again.
View 9 RepliesI an trying to figure out a workable backup plan. I'd like to use a backup device, but I don't see how to keep a set number of days backups in it. Backing up to a backup device adds the backup in. How do I purge the expired backups so there is only, lets say, 3 days worth of backups in the backup device?
It's OS / SQL related. I backup to a UNC device, and frequently get a network related error - but not always. And the error is also OS related...
The scene: 64 bit Windows Server 2008 hosts 64 bit SQL 2008 Standard, and devices are created to point to 64 Server 2008 Server B (ServerBSQL_backupsdvc_DB.bak). The database is backed up daily, on a job, and when run the following error is returned:
Date2009/08/08 06:43:30 AM
LogJob History (Backup_AssetData_2009-08-08)
Step ID3
ServerSQL01
Job NameBackup_AssetData_2009-08-08
Step NameBackup Database - Second Attempt
Duration00:05:33
Sql Severity16
Sql Message ID3013
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: COMPANYSERVICE_USER. Processed 94096 pages for database 'AssetData', file 'AssetData' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1 pages for database 'AssetData', file 'AssetData_log' on file 1. [SQLSTATE 01000] (Message 4035) The operating system returned the error '64(failed to retrieve text for this error. Reason: 15105)' while attempting 'FlushFileBuffers' on 'dvc_AssetData(
iscsrv-dcm04RISCSRV-SQL01_Backupsdvc_AssetData.bak)'. [SQLSTATE 42000] (Error 3634) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
The file is generated, the same size as a successful backup, but the job terminates unsuccessful.I have tried to delete the devices' file, and then the backup usually succeeds. This may point to a permissions' type error, but the user the job is run under is a domain admin. The destination server is not unavailable during this time, and the network shares also remain active throughout the excersize - although I haven't got a way to "prove" this.
I have a few databases that backup to the UNC described above (different files) that don't fail..My SQL2000 server backs up it's databases to the SQL2008 server, and those backups don't fail with this error.
My research has led me to understand that the 64bit OS tries to buffer the files it receives from another server, and I was wondering if this could be influencing the backups as the destination server is also 64 bit Windows Server 2008.
##20090812## UPDATE
I tried another way to do the backup; with the same results:
Backup database Infovest to disk = '
iscsrv-dcm04
iscsrv-sql01_backupsdvc_Infovest_Z.bak' with INIT,STATS=1
[code]....
The space allocated to the Log in question is 180 GB. During this time period I was running TLog backups every 5 minutes, yet the log continued to chew through to 80 GB used, even after the process was complete and a final TLog backup had been taken. It continued to stay very large until the Full backup was complete -- or something else that I'm unaware of completed. Like every other DBA I typically take a TLog backup to shrink the log, but what appeared to be the case here was the Full completed and it released the used log space. All said, will Transaction Log backups not free up the log during Full backups?
View 3 Replies View RelatedDoes anyone know if it is possible to have a tape device another server as a tape dump device on SQL. Instead on removing the tape drive and installing it locally ?!?
Regards
Jasper
I am working on a task. Currently we are taking a database backup and keeping that backups in a folder. The backups doesn't have time stamp on it. My task is need to get the latest backup and copy that backups into some other server and then restore the database from there.I am planning to create SSIS package.Do we need script task for this task.How to get the .bak with latest create or moidified date. For now we doesn't have timestamp so need to go based on modified date?
View 9 Replies View RelatedI queried sys.databases in one of the sql server and found "is_encrypted" is "1" for four of the databases.
Does that mean that all those 4 DBs are encrypted ?
How to check if the database itself is encrypted or only the backups ?
Note :- I can see backups of key in a particular folder.
I make two full backups on Oct 1 and Oct 10. I want to restore the server to a state in Oct 5. So I just do as follows:
1.Perform a transaction log backup on the server on Oct 23. I have never backup transaction log in the past.
2. Restore the server with Oct 1 full backup with NORECOVERY option.
3.Try to restore to the point at Oct 5 12:00, with the transaction log.
But the restore fails and SQL Server said the transaction log does not contain the point. The point is too early. Why? Also my .LDF file is about 13G, but the transaction log backup is only 200MB. Why?
SQL Server 2008 r2 - 6 GB memory...I attempted a backup on a 500GB database but it was taking way too long. I checked the resources on the box and saw the CPU at 100%. I checked the SQL Server activity log and saw a hung query (user was not even logged on) that had multiple threads so I killed it and now the CPU utilization is back to normal.
Trouble is, now all of the threads in the activity monitor for the backup show 'suspended' and the backup appears to be not doing anything.
I've written a custom script to delete backup files from location. But unable to modify now to count the number of files are deleted. How to modify the script...
/* Script to delete older than N days backup from a specific directory */
USE [db_admin]
GO
IF OBJECT_ID('usp_DeleteBackup', 'P') IS NOT NULL
DROP PROC usp_DeleteBackup
GO
[Code] .....
This is probably a simple question but I have to ask it anyway. When backing up I can backup to a file on my local drive but I also can create a device to the same location on my local drive. Is this doing the same thing. If I so desire to backup to the local drive(bear with me) what is the difference between creating a device and a file called mybackup or just choosing to backup to a file called mybackup? Should I always create a device? I know these are dumb question but....
Jason
How do we know the list of all the backups that are present in a particualr backup device like file# and time of backup taken etc for the purose of restore?
When I run :
RESTORE HEADERONLY FROM BackupDeviceName
it doesn't give that info. Can anyone tell me the exact command to list all the files of a backup device so that I can restore the right one??
Thanks.
Shalini.
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 RelatedI'm performing SQL Server native backups and Comvault Backups are breaking the backup chain?I can work around them if they only perfume 1 backup per day.We have a 3rd party hosting our servers so I do not have much control as to what they do.I have concerns about being able to get my databases Comvault being restored.
View 2 Replies View RelatedAre there any problems or issues with backing up a transaction log to a physical file with the ".bak" extension? We are having some trouble with are hourly trans log backups and I was wondering if this could be part of the problem. Tom.
View 4 Replies View RelatedIs there any benefit in creating a backup device and using that in the backup statement over just using a disk file in the backup statement. It seems like extra work to create the backup device with sp_addumpdevice with the file location. Whereas I could just specify the file location right in the BACKUP statement by specifying DISK as the backup device.
View 1 Replies View RelatedI dump my databases to backup devices each night.
However would like to purge old backups - say older than a week - from the device.
Is it a case that I have to drop and re-create the devices every 7 days or can SQL do this for me?
I have seen the RETAINDAYS claus but it applies to tape backups only?
Thanks
In SQL 2000, is there an advantage to defining and using a backupdevice versus just backing up to disk?Currently, I've got a SQL Backup job set to run once per day, withtransaction log backups running every hour. The db and backups arewritten directly to folders on an external disk array. I've neverconfigured an actual Backup Device within SQL. Does creating a BackupDevice offer any advantages not available with my current backupmethod?Thank you for any help.Jason
View 1 Replies View RelatedI've created a simple application that uses a SQLCE 3.5 database. When I debug it SQLCE 3.5 is deployed to the emulator. However, I made a "smart device cab project" for my application and copied the cab file to my windows mobile 6 device and it does not deploy SQLCE 3.5. I don't see a way to specify the prerequisites of the "smart device cab project" like you can in a normal setup project. How can I get SQLCE 3.5 to deploy with my application...or even just get it on my device? I've tried installing it on my desktop with the device connected via active sync, but it doesn't install on the device like the compact framework did.
View 3 Replies View RelatedI need to write create table statements for the er diagram that I attached. I am new to sql and I have trouble integrating foreign keys with these bigger er diagrams.
These are the tables I need to create:
Create Table Author(...)
Create Table Writes(...)
Create Table Book(...)
Create Table Copy(...)
Create Table Loan(...)
Create Table Customer(...)
I issued the following statement on my database as my log file showed 100% full.
However after running this command and also running it again after expanding the size
to almost double the value of both my database log and data devices it still shows the log as being 100% full.
DUMP TRANSACTION <database> WITH TRUNCATE_ONLY (and also used the NO_LOG option).
So can anyone tell me whats going wrong here? I should note that I am still able to enter data into the database without
any errors.
Hi all,
I want to store bakup file on the computer on netword, so make a backup device on the other computer, it ok. after that i make an full backup, there is an error : "Can not open the bakup device " .
please show me,
best regard.
Hi
is there a stored procedure which allows me to query if there is an existing backup device? or which gives me a listing of existing backup devices?
Thanks!
Hi,
request you all to clear mine one doubt that,
Can creating a backup device for sql backup reduce the backup time comparing to giving full backup path.
Cheers!!
I received alerts from a couple of the production servers last night stating that the log file is running out of space. So, I took some log backups and shrunk those files. However, I would like to find out what made the log grow like that. After all, I have t-log backups scheduled every 30 minutes.Is there a way I can find out the reason behind the log growth?
View 9 Replies View RelatedI setup a maintenance plan to create full backups every night and to backup the transaction logs every hour. In the backup log, it always fails on backing up the transaction log for Master and MSDB. I believe this is because the recovery model for these two databases is set to "SIMPLE". Is it ok to change the recovery model on these two system databases to "FULL"?
Thanks,
Ken Nicholson
I am trying to create a backup for the transaction logs on one
of the databases I support. The problem is in EM, the only
options it gives me are a Full DB Backup or a Differential DB
Backup. I have other DB's with TL backup's, why can't I on this
particular one?
I have 5 databases on my sql server and they are taking backups on everyday twice. ANd we are not taking any transactional log backups.Now i have very larger log files which are more than 6 GB each.And the databases are less in size compared to the logfiles.
Should I take transaction log backups with truncate option...
PLease suggest me the best stratagy i should follow to overcome the problem.
Thanks.
Yesterday I received a message from the Trans Log backup job that it could not complete because a full backup wasn't completed. I checked and a full backup was completed that morning 5:00 AM. This happened during the 4 PM run and again at 5 PM. There was enough space to do the backup, even though I did a shrink file on the log. I, also, ran another backup between 5-6 PM. Between both of these activities, the job completed during the 6 PM run. Is this a common occurance? Has anyone else seen this happen. I am curious. Any light that can be shed on this, would be greatlt appreciated. Tom.
View 6 Replies View RelatedI have existing domain users with SQL logins and having different set of permissions, we are migrating to new domain, and we have new user ID's with new domain. Is there any way through query I can create an user and copy the permissions.
View 0 Replies View RelatedI created 2 jobs under sql jobs. How to set up the custom category for my new jobs. It is going under uncategorized but I want to create DBA Monitor category.
View 1 Replies View RelatedI have a transaction log which is backed up 5 times a day to a device.
The Transaction log is growing quite quickly in between each backup. The backup device is also growing at a much quicker rate.
realistically i would like to only keep 2 weeks worth of transaction log backups in the device.
for the transaction log backup i have tried the following transact-Sql
'Backup Log [imsv23] to [imsv23_TL_2] with RetainDays = 7, Init, NoUnload, Name = N'imsv23 TL V2', NoSkip,Stats=10,Noformat'
but due to all of the contents in the backup set not having expired the backup fails.
i need to reduce the size of the backup device. all of the backup sets have expiry dates but none of them are being deleted from device.
so my problem is 1 i need to remove the excessive backups from the device
2 i need to set up the automatic backups so that these datasets are removed when the expiry date is reached.
thanks in advance
If it can, is there some special way to set it up? I can only be successful setting up a tape drive.
Thanks,
Brian