Activity overview
Latest activity by raybouk
My backup/restores have run without failure since I made some code modifcations after seeing your code. Below is the code that is working successfully. I notice that in my first posting, the code contained double single quotes instead of a single double quote in some locations. I think I copied straight from the GUI as well.
===
DECLARE @success int
EXEC @success=master..sqlbackup '-SQL "BACKUP DATABASE [MyDB]
TO DISK = [\\MyNas\MyDB.sqb] "
WITH NAME = "MyDB Database",
DESCRIPTION = "Database: MyDB Instance: (local) Server: Production",
INIT, COMPRESSION = 1" -E'
/* SQLBackup returns 0 for failure and 1 for success */
IF @success=0
BEGIN
RAISERROR('Backup Failed', 16,1)
END
===
Below is the restore code which has been relatively problem.
DECLARE @success int
EXEC @success=master..sqlbackup '-SQL "RESTORE DATABASE [MyDB]
FROM DISK = [\\MyNas\MyDB.sqb] "
WITH RECOVERY,
REPLACE,
MOVE "MyDB" TO "D:\MSSQL\Data\MyDB.mdf",
MOVE "MyDB_log" TO "D:\mssql\data\MyDB_log.ldf" -E'
/* SQLBackup returns 0 for failure and 1 for success */
IF @success=0
BEGIN
RAISERROR('Restore Failed', 16,1)
END
After all this, I think all is well now. Thanks for all your help. / comments
My backup/restores have run without failure since I made some code modifcations after seeing your code. Below is the code that is working successfully. I notice that in my first posting, the code...
There were a couple of hiccups in the backup & restore process last night. I'm hoping those will be fixed tonight.
Re failures - nothing is nothing in the Windows application logs.
I am using version 3.2.01. / comments
There were a couple of hiccups in the backup & restore process last night. I'm hoping those will be fixed tonight.
Re failures - nothing is nothing in the Windows application logs.
I am using vers...
Modifying the code you gave me, I was able to back up to the NAS device. I made similar changes to my production backup and restore. Hopefully it will run correctly tonight. / comments
Modifying the code you gave me, I was able to back up to the NAS device. I made similar changes to my production backup and restore. Hopefully it will run correctly tonight.
That worked. Why did it work? Your syntax is a litte different with the brackets and possibly the quotes.
Output from SQL Backup (3.2.0)
SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005
Serial number: < removed >
Backing up ImportDB (full database) to [image] \MSSQL\Backup\RedGateTest.sqb ...
Backup data size : 284.875 MB
Compressed data size: 85.539 MB
Compression rate : 69.97%
Process completed successfully.
Processed 36368 pages for database 'ImportDB', file 'ImportDB_Data' on file 1.
Processed 1 pages for database 'ImportDB', file 'ImportDB_Log' on file 1.
BACKUP DATABASE successfully processed 36369 pages in 16.379 seconds (18.189 MB/sec).
(1 row affected)
(11 row(s) affected)
The never-ending jobs created sqb files of the expected size (e.g., 87MB when using the UI and 87MB when using the job). / comments
That worked. Why did it work? Your syntax is a litte different with the brackets and possibly the quotes.
Output from SQL Backup (3.2.0)
SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005
Se...
Ok, the command line was successful. Below are the results:
C:\Program Files\Red Gate\SQL Backup>SQLBackupC -E -SQL "BACKUP DATABASE ImportD
B TO DISK = 'D:\MSSQL\Backup\RedGateTest.sqb'
SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005
Serial number: <removed>
Backing up ImportDB (full database) to [image] \MSSQL\Backup\RedGateTest.sqb ...
Backup data size : 284.875 MB
Compressed data size: 85.539 MB
Compression rate : 69.97%
Process completed successfully.
Processed 36368 pages for database 'ImportDB', file 'ImportDB_Data' on file 1.
Processed 1 pages for database 'ImportDB', file 'ImportDB_Log' on file 1.
BACKUP DATABASE successfully processed 36369 pages in 16.650 seconds (17.893 MB/
sec).
(1 row affected)
Any ideas? / comments
Ok, the command line was successful. Below are the results:
C:\Program Files\Red Gate\SQL Backup>SQLBackupC -E -SQL "BACKUP DATABASE ImportD
B TO DISK = 'D:\MSSQL\Backup\RedGateTest.sqb'
SQL Backu...
Your understanding is correct.
<auto> points to [image] \MSSQL\Backup on the local server
I will try the command line today.
Yes, we are running SQL Server Enterprise Edition w/ SP4. Here is my version:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Also, I have read on this site that I need access to application data directory with the logs. Currently I do not have this access. Also when I schedule the jobs, I schedule them as myself or sa - both of which have sysadmin privileges. / comments
Your understanding is correct.
<auto> points to \MSSQL\Backup on the local server
I will try the command line today.
Yes, we are running SQL Server Enterprise Edition w/ SP4. Here is my version:
M...
More info: when I try to run a simple query like this
master..sqlbackup '-sql "BACKUP DATABASE northwind TO DISK = [<AUTO>]"'
It also takes forever to run and cancel the query. / comments
More info: when I try to run a simple query like this
master..sqlbackup '-sql "BACKUP DATABASE northwind TO DISK = [<AUTO>]"'
It also takes forever to run and cancel the query.
Ok I created 2 SQL Server Agent jobs to backup a 600MB DB to the NAS device and locally. Neither job finished. I started them, they created the sqb files, but they never finished. I had to manually stop the jobs (or else they'll run for days).
To take the test a bit further, I used the SQL Backup application to backup locally and to the NAS. Both completed in a few seconds.
These are the local results:
1/16/2006 3:25:54 PM: Backup started ...
1/16/2006 3:26:13 PM: Backup completed.
1/16/2006 3:26:13 PM: Data size: 284.875 MB
1/16/2006 3:26:13 PM: Compressed size: 85.539 MB
1/16/2006 3:26:13 PM: Compression ratio: 70 %
SQL Server messages
Processed 36368 pages for database 'ImportDB', file 'ImportDB_Data' on file 1.
Processed 1 pages for database 'ImportDB', file 'ImportDB_Log' on file 1.
BACKUP DATABASE successfully processed 36369 pages in 16.645 seconds (17.898 MB/sec).
(1 row affected)
These are the NAS results:
1/16/2006 3:30:58 PM: Backup started ...
1/16/2006 3:31:26 PM: Backup completed.
1/16/2006 3:31:26 PM: Data size: 284.875 MB
1/16/2006 3:31:26 PM: Compressed size: 85.539 MB
1/16/2006 3:31:26 PM: Compression ratio: 70 %
SQL Server messages
Processed 36368 pages for database 'ImportDB', file 'ImportDB_Data' on file 1.
Processed 1 pages for database 'ImportDB', file 'ImportDB_Log' on file 1.
BACKUP DATABASE successfully processed 36369 pages in 24.610 seconds (12.105 MB/sec).
(1 row affected) / comments
Ok I created 2 SQL Server Agent jobs to backup a 600MB DB to the NAS device and locally. Neither job finished. I started them, they created the sqb files, but they never finished. I had to manua...
I'll take some measurements today. Last week I had some emergencies come up.
Just an FYI - these are the messages from my backup and restore jobs (the job step writes to files):
--- this is the backup - I would expect to see completion language but there isn't any:
Job 'Backup MyDB Database' : Step 1, 'backup MyDB' : Began Executing 2006-01-13 00:42:21
---- this is the restore - it either timed or or was stopped by me
Job 'restore MyDB nightly' : Step 3, 'restore MyDB' : Began Executing 2006-01-14 04:00:02
Msg 0, Sev 0: Operation canceled [SQLSTATE HY008]
Msg 0, Sev 0: Timeout expired [SQLSTATE HYT00]
Query cancelled. / comments
I'll take some measurements today. Last week I had some emergencies come up.
Just an FYI - these are the messages from my backup and restore jobs (the job step writes to files):
--- this is the ba...
This is not a new NAS device. We used to backup to this NAS device using the native SQL Server backups, but the backups would take too long which is why we switched to SQL Backup.
Another SQL Server backed up to the NAS device (using SQL Backup) with a throughput of 24.466 MB/sec. I would backup locally and copy to the NAS device, but there isn't enough space.
Last night I had to stop the backup job because it was taking too long. / comments
This is not a new NAS device. We used to backup to this NAS device using the native SQL Server backups, but the backups would take too long which is why we switched to SQL Backup.
Another SQL Serv...