backup and restore taking too long

raybouk

I have 2 SQL Backup procedures that are taking too long to complete. The procedures backup a database to a NAS device and then restore the database from the NAS device to another SQL Server several hours later.

Below is the backup script from the main production server. It is scheduled for 7 PM daily. This one ran for 17 hours before I noticed it and stopped the job. It should run in a few hours.

=====
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 script from the restore server. It is scheduled for 3 AM daily. This one ran for 53 hours before I noticed it and stopped the job. It too should run in a few hours.

====
DECLARE @success int

EXEC @success=master..sqlbackup '-SQL "RESTORE DATABASE [MyDB]
FROM DISK = ''\\MyNas\MyDB.sqb''
WITH RECOVERY,
REPLACE,
MOVE ''MyDB_data'' TO ''D:\MSSQL\Data\MyDB.mdf'',
MOVE ''MyDB_log'' TO ''d:\mssql\data\MyDB.ldf''" -E'

/* SQLBackup returns 0 for failure and 1 for success */
IF @success=0
BEGIN
RAISERROR('Restore Failed', 16,1)
END
====

This restore job is preceded by a step to set the database to single-user mode.

Any ideas on how to get these procedures to run correctly?

Thanks,
Ray
SS2K

petey

Is this the first time that you are using the NAS device? Can you make a test backup and restore to determine the maximum throughput when using this device?

raybouk

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.

petey

I would suggest taking some measurements to identify the problem.

If you have a small database (about 100-200 MB), try making a backup to a local drive, and then to the NAS. What's the throughput in each case?

Then try copying a reasonably sized file to the NAS, from this SQL Server and another one, and compare the speed.

raybouk

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.

raybouk

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)

raybouk

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.

petey

Is my understanding correct on the following points:

1. when backing up via the GUI to the local drive or NAS device, no problems.

2. when backing up via Query Analyzer, process never ends.

3. when backing up via a SQL Server Agent job, process never ends.

In the last example that you provided, where does <AUTO> point to?

Do you encounter the same problems if you backup using the command line e.g.

sqlbackupc -sql "BACKUP DATABASE northwind to DISK = '<somewhere>'"

Are you using SQL 2000 with SP3 or later applied?

raybouk

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:

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.

raybouk

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 \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?

petey

Given that the extended stored procedure does nothing but pass the commands to the command line interface, could you pls now try running the same backup but via Query Analyzer to test e.g.

sqlbackup '-sql "BACKUP DATABASE ImportDB TO DISK = [D:\MSSQL\Backup\RedGateTest.sqb]"'

If that fails, then the problem lies in the extended stored procedure.

In an earlier post, you mentioned that the backup tasks start, the sqb files get created but the job never ends. What were the size of the sqb files? Were they 0 bytes, or somewhere close to a size you expect them to be?

raybouk

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 \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).

raybouk

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.

petey

There really is no difference in the syntax. The square brackets were just to make the code a little clearer.

Re the failures, are they are entries in the Windows application logs (when viewed via the Event Viewer)?

And I also forgot to ask: what version of SQL Backup are you using (3.2.0.1 or later)?

Thanks.

raybouk

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.

petey

I would suggest writing to support requesting for a patch version, stating that you encounter errors where jobs ran through SQL Server Agent sometimes do not end and that you are using 3.2.0.1.

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.