I created a backup device and a job to backup a full dbs and transaction logs. When I first created the backup statement it was created with RETAIN DAYS = 10
Its been 15 days and the ones created after 10 days appear to be marked for deletiong but I am obviously missing a statement in my backup statement or in another job I need to schedule to come through and delete the old files from the backup device. Please help me figure out what this statement needs to be.
All the backups and T-log backups are written to one device which is one file.
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?
i want to delete the backup files older than 15 days. how can i do this? Actually i backup the db and log using the TSQL, and the backup files are in d:ackup folder. I tried sqlmaint,xp_sqlmaint but its not running .
Ok in SQL 2000 this is within the maintaince plan, where as i have to create a seprate one for sql 2005 being i assume the 'clean up history' I choose the backup andrestore option choose 2 weeks and then run the job once created it runs sucssessfully but yet it dosnt delete a thing. I have backups going back almost 2 months now. What on earth am i not doing?
I am experiencing an issue with SQL Server 2000 Maintenance Plan. DB Backup job fails to delete old backup files from the file server (I am backing up to the network share - actually, a DFS). Backup part of the maintenance plan/job succeeds, but then cleanup part fails.
I made sure that service account under which SQL Server Agent is running, has sufficient privileges over the network share by logging in and successfully deleting files in question.
I was not able to locate any log entries either on the SQL Server machine or on the file server machine that would indicate the root of the problem. Even though I turned on auditing for Delete operations for the destination folder, its subfolders and files, I could not find anything in the Security event log.
I would appreciate any ideas on how to troubleshoot and correct this problem.
I want to delete all backup files from a folder older than a specific date. But if I use the beklow query, I need to pass how many days of older backup files I need to delete whereas in my case, I dont know how many days/month/syears of old backup files are there in the backup folder.
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....
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.
I have a Device of 6GB and a Transaction logof device of 1.5Gb , the database of the associated device had gone suspect.so i dropped the database , now i a m trying to delete the device it says cannot delete as its in use by some database , i have checked all the Dbs butits not associted with anything what shold i do????
I need to remove log segment of one database from current position to another drive. The current log segment is used by two databases. This was created by mistake. Now we need to fix the problem and create another log segment for the other database. If we keep the log setment as is, we will have problems deleting one of the databases in the future.
Is 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.
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
I have taken ownership of a SQL 7 Server that has many DB's with each DB's devices spread all over the 6 disks that exists in the Server.
e.g.
DB - Reference has
Data devices on D (20mb), E(40mb) , F(60mb) drives
and Logs having a similar spread.
Now what I want to do is to restore a full backup of the database into a new db but with only one data device and one log. So that basically the server is tidied up. The reason the devices are spread is not due to performance reasons.
The QUESTION IS : How do I restore a full back up of a db with many devices in to a NEW db with only 1 data device and 1 log device.
I have some simple files but they are failing because the delete history task is failing as it is looking for files in a non existent directory.
It is looking for files in C:Program FilesMicrosoft SQL ServerMSSQL10_50.INSTANCEMSSQLLog whereas it should be looking in C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog
how I can get this corrected so I can get the Maintenance Plans to run correctly.
I have tried deleting and recreating the Plan but to no avail
When you create a dump device, then add backups with the NOINIT arg, the space taken up on the device grows, is there a way of removing individual backups from the device without (i guess) purging the whole lot by running WITH INIT? I can get a list of the files by running the RESTORE WITH HEADERONLY, but I can't see how to remove individual ones.
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.
I 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.
I have a SQL Server 7.0 with and exteranl SCSI tape device. When I attempt to backup a database in EM the tape device is being greyed out.
I have add the backup device using sp_dumpdevice and made sure that it is in sysdevices table. It will not allow me to add this backup device from the drop down.
1. Why does EM not see this device ? 2. If I try to back this up using T-SQL will I be able to use the registared tape device.
I'm trying to set a backup job to back up my databases to a server other than the server where Sql server is installed. The only options I can see are the local drives on my sql server when I try to set a location or backup device. If I want my backups to go to another server , How do I do that. Currently I'm trying to use a UNC path like \ServerNameBackups. When I try to use a UNC path I get an invalid directory error.
I'm fairly new to SQL Server as you can tell!
Can anyone help me? (I'm using sql server 2000) Thanks GEM
I am a newbie in SQL Server Administration. I decided to make the move within my company that no one else wanted.
I have SQL Server 2000 running at this time; users are connecting and using it. My question I guess pertains to the backups.
I have two drives setup for data and transactions logs (D: E:)
My backups are running but backups are being sent to the d:systems datamssqlackup folder. once backups are comlete I normally copy the backups to a network share that is being backed up nightly to tape.
My question is this:
How do i create a device location under backup properties to point to a network share? i would like for the backups to go straight to a network drive so i do not have to copy the backups every morning when i come in.
I want to be able to take a backup device to another server and then restore the last backup file within the device.
My script creates the backup device using the filename of the file from the other server but when I do the restore it only restores the first file in the device.
How can I get it to restore the last file? In EA I can do a manual resotre and look inside the device and choose say backup number 2.
Hi, I was wondering if someone could help me. I'm having problems using backup in Enterprise Manager, the backup continually fails and points to a hardware problem. However, I have been able to backup files using different software to tape which rules out hardware. I'm trying to create a new backup device incase the problem lies there but I receive the error 'error15061: Add device request denied. A physical device named '%s' already exists'
I want to create a backup device for the tape drive. So I go in "Server Objects"=>"Backup Devices" and create a new device. Problem is: In "Destination" I can't select "Tape:".
The ntbackup.exe program recognizes the tape drive, but SQL2005 not.
I have this: -SQL2005 Enterprise edition 64bit. -Tape drive HP StorageWorks Ultrium 960.
Someone knows how I can create a backupdevice for the tape?
Thanks
IF Debugging = removing bugs from program THEN programming := putting bugs in program;
appending new backup on a same device - yes or no?
if somebody can tell me, is ok to constantly create new backus on a same device? i'm asking that, because in that case, defice file it will grow and grow.
is there any limit or suggestions how many appending is recomended, cause of file growth?
I 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?
I've got a new HP ML370 G4 Server 2003 SP1 with a fresh install of SQL 2005 SP1. I'm trying to create a backup device for the internal HP DAT 40 and tape is greyed out as an option. I have the newest HP software drivers and firmware. The tape drive is seen by Server 2003 device manager and by NTBACKUP. I can backup using that program. Why doesn't SQL 2005 see it? I tried a new HP Ultrium tape drive with the same results. Anyone else see this and can recommend a fix? TIA
I am trying to configure the backup device on SQL Server 2005. I cannot get the Tape device list. The Tape Device is attached physicaly to the server ! (IBM Ultrium-TD3 scsi). I can 'see' the device using Windows Backup; but I cannot even get the list of tapes on SQL Server.
but how to append more than one databases( SQL 2005) to the same tape.
Having said this, what is the solution for " I've got a new HP ML370 G4 Server 2003 SP1 with a fresh install of SQL 2005 SP1. I'm trying to create a backup device for the internal HP DAT 40 and tape is greyed out as an option .........." Pls advice me.
