Comments
Sort by recent activity
I tried using the stored procedures listed here: http://help.red-gate.com/help/SQLBackup ... Backup.htm
to change the authentication, with no luck. I then removed the ServerCore install and re-installed it entering my domain credentials. However the backups still fire off as NT Authority\NETWORK SERVICE
The SQL Backup Agent is running under Domain\Administrator as well. / comments
I tried using the stored procedures listed here:http://help.red-gate.com/help/SQLBackup ... Backup.htm
to change the authentication, with no luck. I then removed the ServerCore install and re-inst...
Ok doke. I can run the script out of a Query Analyzer logged in with windows authentication.
EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASE [XYZ] TO DISK = ''C:\SQLBACKUP\DAILY\DB\<AUTO>.sqb'' WITH COMPRESSION = 1, ERASEFILES_ATSTART = 5, INIT, MAILTO = ''me@domain.com'', THREADCOUNT = 4, VERIFY"'
That works great, so somewhere.. the script is executing under the wrong permissions from SQLBackupC. / comments
Ok doke. I can run the script out of a Query Analyzer logged in with windows authentication.
EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASE [XYZ] TO DISK = ''C:\SQLBACKUP\DAILY\DB\<AUTO>.sqb'' W...
Nevermind, I got it. [image] / comments
Nevermind, I got it.
petey wrote:
Yes.
Perfect. This morning's backup went flawless and all the older data has been erased. / comments
petey wrote:
Yes.
Perfect. This morning's backup went flawless and all the older data has been erased.
petey wrote:
Sorry, you should use the value 1 i.e. FILEOPTIONS = 1.
The value 2 instructs SQL Backup to only delete files if the file does not have the archive attribute. In cases where you back up the files to tape, this option prevents you from deleting files that have not yet been backed up to tape.
So this will delete both the Primary and COPYTO locations after "X" days (ERASEFILES setting)? / comments
petey wrote:
Sorry, you should use the value 1 i.e. FILEOPTIONS = 1.
The value 2 instructs SQL Backup to only delete files if the file does not have the archive attribute. In cases where you ba...
Hello. Thank you for the answer, however that does not seem to have worked? SQL Backup log file 5.2.0.2824
10/8/2007 3:00:00 PM: Backing up DHL (transaction log) to:
10/8/2007 3:00:00 PM: C:\SQLBACKUP\Daily\TRN\DHL\LOG_(local)_DHL_20071008_150000.sqb
10/8/2007 3:00:00 PM: BACKUP LOG [DHL] TO DISK = 'C:\SQLBACKUP\Daily\TRN\<database>\<AUTO>.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', VERIFY, ERASEFILES = 4, COPYTO = 'E:\SQLBACKUP\Daily\TRN\<Database>\', FILEOPTIONS = 3, COMPRESSION = 1, THREADCOUNT = 3
10/8/2007 3:00:04 PM: Backup data size : 39.000 MB
10/8/2007 3:00:04 PM: Compressed data size: 6.294 MB
10/8/2007 3:00:04 PM: Compression rate : 83.86%
Processed 3869 pages for database 'DHL', file 'DHL_Log' on file 1.
BACKUP LOG successfully processed 3869 pages in 1.306 seconds (24.263 MB/sec).
10/8/2007 3:00:04 PM:
10/8/2007 3:00:04 PM: Validating files:
10/8/2007 3:00:04 PM: C:\SQLBACKUP\Daily\TRN\DHL\LOG_(local)_DHL_20071008_150000.sqb
10/8/2007 3:00:04 PM:
10/8/2007 3:00:04 PM: RESTORE VERIFYONLY FROM DISK = 'C:\SQLBACKUP\Daily\TRN\DHL\LOG_(local)_DHL_20071008_150000.sqb'
The backup set is valid.
10/8/2007 3:00:07 PM: Copied C:\SQLBACKUP\Daily\TRN\DHL\LOG_(local)_DHL_20071008_150000.sqb to E:\SQLBACKUP\Daily\TRN\DHL\LOG_(local)_DHL_20071008_150000.sqb.
10/8/2007 3:00:07 PM: SQL Backup process ended.
10/8/2007 3:00:07 PM: Deleted msdb entries older than 8/9/2007 3:00:07 PM
10/8/2007 3:00:07 PM: Deleted local history entries older than 8/9/2007 3:00:07 PM
I have backups from 10/3/07 still in my copyto folder, which is older than 4 days old (which is what my ERASEFILES) is set to. / comments
Hello. Thank you for the answer, however that does not seem to have worked?SQL Backup log file 5.2.0.2824
10/8/2007 3:00:00 PM: Backing up DHL (transaction log) to:
10/8/2007 3:00:00 PM: ...
Jason Cook wrote:
When you use the browser, at the top left where it says "Network Shares", you can change this to the local server, and backup to a local drive. There should be no technical restrictions to using a local drive.
To clarify the difference between the MIRRORFILE and COPYTO keywords, the mirroring occurs during the backup itself (so using a USB drive will slow the backup down), the copying occurs after the backup.
Jason
Jason,
Yes I selected the local server however E: does not show up, but I can manually enter E:\path\to\backup so I'm going to give that a go.
Right now I use SQLEM to backup and then robocopy to copy to the E: drive as a second step in the process.
I appreciate your ZIPPY responses. What a company! / comments
Jason Cook wrote:
When you use the browser, at the top left where it says "Network Shares", you can change this to the local server, and backup to a local drive. There should be no technical re...
Jason Cook wrote:
In step 4 of the backup wizard (step 5 of the scheduling wizard) there is an option called "Copy backup to network", which is the GUI representation of the COPYTO keyword.
Jason
Yes but for whatever reason that does not allow me to select a local drive E drive in this case is a usb storage device attached to the SQL server. I assumed this was the mirror function and not the copy function. / comments
Jason Cook wrote:
In step 4 of the backup wizard (step 5 of the scheduling wizard) there is an option called "Copy backup to network", which is the GUI representation of the COPYTO keyword.
Jaso...
Jason,
Thank you. When I do 1 hr transaction log backups with SQLEM it is apprent that the size changes quite readily however. One hour it can be 100k, the next it can be 20k etc. This depends on the level of work being done but I can see by the size of our log when my users were most active on the database.
That's why I was confused because these were both very similar in size. Right now my log file is a bit out of whack, though. It's got a lot of inactive (empty) space according to SQLEM with very little data in it. I need to shrink it down which I guess remove inactive entries would do if I decided to tick that check box, right? / comments
Jason,
Thank you. When I do 1 hr transaction log backups with SQLEM it is apprent that the size changes quite readily however. One hour it can be 100k, the next it can be 20k etc. This depend...
Ah yes. Forgive me, it's early morning here yet. I had my truncate and shrink mixed up.
Thanks again for the help. The backup test I did last night took a less than a third of the time the SQLEM backup took, so this is promising. The only thing I cannot seem to do with the GUI scheduling is use the 'copyto' function, I can use mirror.. but that will not allow me to copy to another attached drive (backup) on the system. can this only be done with the dos command or the stored procedure?
Jason Cook wrote:
Truncating the log / "Remove inactive entries from transaction log" will indeed remove the inactive entries, but it will not shrink the file size down.
If you want to physically shrink the size of the file(s) there are two options, one shrinks a single file (e.g. the log file), one shrinks all of the files in a database:
DBCC SHRINKDATABASE (http://msdn2.microsoft.com/en-us/library/ms190488.aspx)
DBCC SHRINKFILE (http://msdn2.microsoft.com/en-us/library/ms189493.aspx)
Due to the nature of the command, the CPU usage and I/O load can be noticable for a sustained period of time (which depends on the size of the files in question), so should only be executed during a period of low utilisation.
Hope that helps,
Jason
/ comments
Ah yes. Forgive me, it's early morning here yet. I had my truncate and shrink mixed up.
Thanks again for the help. The backup test I did last night took a less than a third of the time the SQLEM...