Copying useable databases to a Bacup SQL server

bobgalway

I am an Access developer teaching myself SQL Server 2005 (but very new to SQL Server). I am trying to do something that sounds simple but has not been easy for me: backup the databases on a production SQL server 2005 to a backup server (so I can switch to it if the production goes down). Can I do this w/ SQL Backup 5? How? Many thanks.

Jason Cook

If you are using the SQL Backup 5 GUI, you need to perform the following steps:

1) Use the "Backup Wizard" to create the backup
2) In the progress dialog, you'll be told where the file was created (if the backup was successful), make a note of this location
3) Copy the file in question across to the new server
4) Use the "Restore Wizard" on the second server to restore the backup... you will need to use the "Browse for backup files to restore" option.

Alternatively, if you want to keep the "backup" (or "standby") server up-to-date with any changes, you can use "Log Shipping", which is described in the SQL Backup 5 help file here:

* http://help.red-gate.com/help/SQLBackup ... gShip.html


[Additional Reading Material]
If you want any more information on how SQL Server deals with backups and restores, a good reference is provided here:

* http://msdn2.microsoft.com/en-us/library/ms187048.aspx

The SQL Backup Help can also give you a lot of useful information on performing backups and restores, and is available online here:

* http://help.red-gate.com/help/SQLBackup ... Backup.htm


Hope that helps,
Jason

bobgalway

I am using an evaluation version of SQL Backup 5 and trying to test Log Shipping. When I do so, it will show the SQL server but the drop-down for database is blank (yes, there are databases!). The documentation talks about SQL Backup Pro as being needed: is this my problem? How do I evaluate SQL Backup Pro? (I am using SQL Server 2005 on both servers). Thanks!. Bob Galway

Jason Cook

By default, the SQL Backup "evaluation" or trial version is equivalent to SQL Backup Professional (for the 14 days of the trial).

The drop down list will include any databases that are in the "FULL" recovery model - any databases in the "SIMPLE" recovery model will not allow log backups, which are required for log shipping to function.

An overview on the different recovery models provided by SQL Server, and the trade-offs of each is covered here: http://msdn2.microsoft.com/en-us/library/ms189275.aspx

If you want to be able to log ship the database, you will need to change from the SIMPLE recovery model to the FULL one.

Hope that helps,
Jason

bobgalway

As I proceed along in the Log Shipping WIZARD, I am at step #4..after having some problems w/ the non-intuitive network share dialog, I got to the point where the shares are shown and the folders and sub-folders are shown. NOW, however, it (step #4) cannot proceed further because it insists that the "Folder does not exist" - when it does! Any help to get me past this would be appreciated. Thanks. Bob Galway.

Jason Cook

Out of interest, what is the location that you are trying to use (as written in the GUI)?

The location needs to:
* Already exist at all folder levels - if any of the interim folders need creating, they have to be created before the log shipping is completed.
* Be visible to *both* machines, using the location format provided (e.g. "server" may work for one machine, but the other may require "server.domain.com")
* Be accessible with the supplied credentials, both for reading and writing.

Let me know and I'll be able to come back with some more information to try and identify why you are getting that message.

Thanks,
Jason

bobgalway

On my primary SQL server, the location is BOTH a folder on my E: drive (physical drive on server) called SQLlogShip AND a network share (\\p1800\SQLlogship). That share is seen on my backup SQL server. It is also seen and available by the Network Share browser button in Step#4 in the Wizard. As a test, I have even used your program to put a backup file in that same location and it works just fine. (log follows)

P1800\SQL2005: Backup - Successful
==================================

Performing full backups - Successful

---

This operation completed successfully.

Backing up PO (full database) on SQL2005 instance to:
E:\SQLlogShip\FULL_SQL2005_PO_20070831_090343.sqb
Database size : 3.438 MB
Compressed data size: 395.500 KB
Compression rate : 88.76%
Processed 256 pages for database 'PO', file 'PO' on file 1.
Processed 3 pages for database 'PO', file 'PO_log' on file 1.
BACKUP DATABASE successfully processed 259 pages in 0.152 seconds (13.911 MB/sec).
Validating files:
E:\SQLlogShip\FULL_SQL2005_PO_20070831_090343.sqb
The backup set on file 1 is valid.
SQL Backup process ended.



Updating SQL Server information - Successful

---

This operation completed successfully.




Any help is appreciated.

Jason Cook

The "share" location needs to be the same for each server, and visible to both servers. Therefore in this case, you would need to use the "\\p1800\SQLlogship" location, rather than a local one such as E:

If that doesn't work, you can run the following tests to identify the problem:

* On *each* of the nodes, run the following transact-sql statement:

execute master..sqbutility 999, 'RWE', '\\p1800\SQLlogship';

If this accesses the location correctly, it will return a value "1", otherwise a value "0" with an error message.

If this returns a "Folder does not exist" when run on the 'remote' server, you need to check the following

* That the share is visible to the remote server (try accessing it via Windows Explorer)
* Check that the SQL Backup Agent account has sufficient permissions to access that account - both in terms of folder permissions, and share permissions (i.e. not just "read" access).

Hopefully that will help to identify the problem, if not... let me know what messages you get back from the sqbutility 999 command on each of the two servers, and I'll look into it further for you.

Jason

bobgalway

I should tell you that the backup SQL Server is running SQL Server 2005 Express...could that be the issue?????

Jason Cook

While SQL Server Express will not cause the problem you are describing, unfortunately you will not be able to use the Log Shipping "jobs" created because the Express version does not have the SQL Server Agent available.

The thread here ( http://www.red-gate.com/messageboard/vi ... php?t=4966 ) discusses the same scenario... there are not too many options available when using SQL Server Express, as Microsoft has disabled most of the scheduling functionality.

There is nothing to stop you using SQL Backup to perform the backup and restore manually (and all the functionality of SQL Backup should be available for that), but it would be a lot trickier to "synchronise" to a second server when using SQL Server Express.

Jason