Comments
Sort by recent activity
The 3,200 character limitation is a restriction of the SQL Server Agent's job steps, rather than with SQL Backup directly (which in v5 can support up to 64,000 characters in a command).
In addition to what Peter suggested, if you have generated the command using the GUI, you have the option to save the backup command as a "template". You can then use the following syntax to execute the backup command via the template:
execute master..sqlbackup '-USE "<template_name>"';
This may help get around the limitation...
Hope that helps,
Jason / comments
The 3,200 character limitation is a restriction of the SQL Server Agent's job steps, rather than with SQL Backup directly (which in v5 can support up to 64,000 characters in a command).
In addition...
Normally, the memory allocations are caused by SQL Server fragmenting the memory space. However looking at the included memory profile, that doesn't appear to be the case, so could have been caused by the server being particularly busy at that period in time.
Some of the commands available for checking the backup are (with optional password keyword, can remove WITH PASSWORD... if a password is not required):
execute master..sqlbackup '-SQL "RESTORE FILELISTONLY FROM DISK = ''...'' WITH PASSWORD = ''...'' "';
execute master..sqlbackup '-SQL "RESTORE SQBHEADERONLY FROM DISK = ''...'' WITH PASSWORD = ''...'' "';
execute master..sqlbackup '-SQL "RESTORE VERIFYONLY FROM DISK = ''...'' WITH PASSWORD = ''...'' "';
* RESTORE FILELISTONLY will return a list of the database files that are included in the backup. Invalid information here would indicate a corrupted backup.
* RESTORE SQBHEADERONLY returns the header information about the backup... this is usually finalised at the end of the backup, so if the backup "failed" this information would be invalid or incomplete.
* RESTORE VERIFYONLY performs some basic validation checks on the file... but does not go as far as doing a full file integrity check. It may spot major problems with the file, but smaller issues like corrupted bytes of data would not be spotted with this command.
Hopefully the three commands will be enough to feel happy about the integrity of the backup... between them they should indicate that the backup not only started, but was finished successfully, despite what the job status may indicate.
Hope that helps,
Jason / comments
Normally, the memory allocations are caused by SQL Server fragmenting the memory space. However looking at the included memory profile, that doesn't appear to be the case, so could have been cause...
Hi,
The reason that the job "failed" is because SQL Backup issued a VDI error 1010, and a SQL Server error 3013; which will have been passed back to the SQL Server Agent, and raised as a failure.
Infact, what has happened is that the VDI error (caused mostly by memory allocation issues) causes SQL Backup to try again, with a smaller memory requirement, up to a maximum of 4 retries.
In your case, on the first retry the backup succeeded, so the backup will be fine.
If you want to do some checks, you can use the RESTORE SQBHEADERONLY, RESTORE HEADERONLY and RESTORE FILELISTONLY commands to do some basic checks on the backup, but ultimately for full peace-of-mind, the safest solution would be to restore to a temporary server if possible (although I realise that's easier said than done when talking about 600GB of data).
Hope that helps,
Jason / comments
Hi,
The reason that the job "failed" is because SQL Backup issued a VDI error 1010, and a SQL Server error 3013; which will have been passed back to the SQL Server Agent, and raised as a failure.
I...
Editing the restore job in management studio, you can modify the restore job to force the files to be in a specified location.
To do this, you need to add the "MOVE" keyword, and specify where each of the database files should move to. This way there is no "default assumption", which may not be what you intended.
For example assuming the following restore command:
master..sqlbackup '-SQL "RESTORE DATABASE [mydatabase] FROM DISK = ''c:\backups\mydatabase.sqb'' WITH RECOVERY "';
To ensure this always goes to the right location, rather than some guessed SQL Server default, you can add the WITH MOVE syntax (a database file, followed by a file location) as follows:
master..sqlbackup '-SQL "RESTORE DATABASE [mydatabase] FROM DISK = ''c:\backups\mydatabase.sqb'' WITH RECOVERY, MOVE ''file1'' TO ''d:\data\file1.mdf'', MOVE ''file_log'' TO ''d:\data\file_log.ldf'' "';
Hope that helps,
Jason / comments
Editing the restore job in management studio, you can modify the restore job to force the files to be in a specified location.
To do this, you need to add the "MOVE" keyword, and specify where each...
Hi,
It appears that the issue is indeed due to the french settings in SQL Server. The error you mention is easily reproducible in the following scenario:
SET LANGUAGE FRENCH;
SELECT CONVERT(DATETIME, '2006-03-30');
GO
SET LANGUAGE ENGLISH;
SELECT CONVERT(DATETIME, '2006-03-30');
The first generating the error message you mention, the second working correctly. The problem being due to the date format not being recognised correctly in the french language.
I will investigate and see if there is a workaround for this in the meantime and get back to you, and will also raise this to be fixed in the next release of SQL Backup.
Thanks,
Jason / comments
Hi,
It appears that the issue is indeed due to the french settings in SQL Server. The error you mention is easily reproducible in the following scenario:
SET LANGUAGE FRENCH;
SELECT CONVERT(DATETI...
Hi,
I've tried the statement you have supplied and I'm not able to reproduce your error. However, there are a couple of things I would suggest trying that may help:
* Change nvarchar(max) to nvarchar(4000) - SQL Backup has a limit of 64,000 characters for the command, and while you shouldn't hit that limit, defining a max variable may cause issues.
* For your @sqls set statement, you should prefix it with an N for unicode, so that it's clear what you are executing.
Also, what output do you get when the command runs? Even if the command fails to do a backup or restore, you should see two grids below - the first window titled "SQL Backup v5.2.0.2807", and the second has two columns, one called "name" and one "value".
If you don't see any output, it would be useful to run a profiler trace when executing the script to establish what is actually happening behind the scenes... that may give more insight into where the problem lies.
Thanks,
Jason / comments
Hi,
I've tried the statement you have supplied and I'm not able to reproduce your error. However, there are a couple of things I would suggest trying that may help:
* Change nvarchar(max) to nvarc...
To check the progress of the backup, execute the following command in Query Analyser or Management Studio:
master..sqbstatus 1;
In particular, note the "processed (bytes)" and "compressed (bytes)" column. If these change when you run the command again a minute later, then the backup is still performing.
However, if the values are unchanged after 2-3 minutes, and the "processed (bytes)" value is not comparable to the size of the database, it suggests that something may have gone wrong, which will need further investigation.
The size of the SQB file is a bad indicator of progress, since the value reported by Windows can remain unchanged, even though the file size is increasing. The above command will give you the exact progress of the backup, from the perspective of the SQL Backup service.
Hope that helps,
Jason / comments
To check the progress of the backup, execute the following command in Query Analyser or Management Studio:
master..sqbstatus 1;
In particular, note the "processed (bytes)" and "compressed (bytes)" ...
Hi,
There is a known issue with backups sometimes stalling if the drive runs out of disk space... unfortunately the only solution is to restart the SQL Backup service (which will release any locks on the file) and restart the backup.
Because the backup is incomplete, it will not be "good", so really it needs to be started from scratch.
I'll pass this issue on to the developers to look into this again, since while nothing disasterous happens per-say, it's obviously far from ideal that no error or email notification is being issued.
Thanks,
Jason / comments
Hi,
There is a known issue with backups sometimes stalling if the drive runs out of disk space... unfortunately the only solution is to restart the SQL Backup service (which will release any locks ...
Hi,
Unfortunately, the method you describe ("browse to backup files") is the simplest way to restore a "deleted" database. While the information is still retained in the internal SQL Backup data store, it is not displayed in the GUI on the restore wizard to try and keep the list of databases as simple as possible.
One workaround is to create a blank database with the same name as the one you've deleted. This will then result in all of the available backup and restore history being displayed in the SQL Backup GUI, and you can quickly select the backup file you wish to restore. It's not an ideal workaround, but would at least achieve what you are looking for.
I'll pass your idea on to our developers, and they can consider including it explicitly in a future release of SQL Backup.
Hope that helps,
Jason / comments
Hi,
Unfortunately, the method you describe ("browse to backup files") is the simplest way to restore a "deleted" database. While the information is still retained in the internal SQL Backup data s...
Hi Matt,
When a server is first registered, the GUI will pull all activity history from the SQL Backup data store, and also from the msdb database in SQL Server. It collates this information together to populate the list shown in the GUI.
If you have a significant amount of data (tens of thousands of entries), this can take a while to collate, which will result in the spinning behaviour described.
There are two possible solutions to this:
- Leave the GUI open while the Activity History is collated. Once this has been done, it should not need to pull in all the information again.
- Alternatively, you can trim the backup and restore history, using the settings in the "Options" dialog. While this will reduce the time for the activity history to be collated, the old history will no longer be available.
Hope that helps,
Jason / comments
Hi Matt,
When a server is first registered, the GUI will pull all activity history from the SQL Backup data store, and also from the msdb database in SQL Server. It collates this information toget...