Showing posts with label shipping. Show all posts
Showing posts with label shipping. Show all posts

Wednesday, March 7, 2012

backups interfering with log shipping?

I have some questions on backups and log shipping.

Before I get to them, though, the goal:

Back up a SQL 2000 database (~5GB in size) completely every day.
Back up its transaction log hourly.
Maintain a warm backup DB server using log shipping.

Here is what I have done so far, which has led to my questions:

I have stopped using maintenance plans for backups, feeling that
then the process would be less 'black boxy'

I first created two jobs to perform the daily db and hourly tlog backups
that saved the backups in files using the naming formats
<name>_db_<yymmdd>.bak and <name>_tlog_<yymmddnnss>.bak.

Issue 1: doing backups this way means that it's a little difficult for the
log shipping jobs to figure out the filenames (esp. the tlog ones), if log
shipping is going to use the files generated by the backup jobs.

Issue 2: I thought maybe I'd have log shipping generate its OWN backup
files, but would that cause problems with the transaction log? Say the
normal tlog backup fires, then fifteen minutes later the shipping tlog
backup fires. Would the shipping tlog backup file be missing the transactions
that were backed up during the normal log backup?

Issue 3: To try to help with the naming issue, I tried switching the backups from
creating new files each time to file devices whose names would be constant.
This worked, but since my database is about 5GB in size, that meant
that, with expiring backups after 7 days, the database backup device
would settle in at about 25GB (I skip weekends) and I'd have to copy
or cab-copy-uncab that file over to my warm server every day. That
seemed a little inefficient. Does anybody have alternative ideas?

My last question is for general info:

Where does SQL Server keep the information on the backups that exist?
Whether I was using individual files or file devices, I was able to go to
Database-All Tasks-Restore Database... in Enterprise Manager and it
would show me the backups that existed. I imagine these must be
stored in a system table somewhere, but I did not see any obvious place
to look (no 'sysbackups' table, etc.).

Many thanks in advance for your input!

GeoffBoiling my above messgae down to bite-size questions:

1. Where does SQL store information related to the backups that currently exist? Or does SQL scan for backup files and figure out what's in them on the fly (seems unlikely to me)?

2. Say I want to run two transaction log backups per hour. One is included in the normal backup plan and one occurs, say, 15 minutes later for log shipping purposes. Is my log shipping going to fail because the transactions that were backed up during the normal bacup will not be in my log shipping backup?|||It's in the msdb in a table called backupset.

I run log shipping where I backup the transaction log to a file name like DBNAME_TRAN.TRN. I then copy one to the SHIP TO server and apply it while copying the other to a directory where I add the data and time. I have scripts that determine which logs to apply in case I have to restore the database from the Full, Incrimental and then apply transaction log backups.

Thursday, February 16, 2012

Backup/DR configuration using mirroring and log shipping

I'm trying to come up with a db backup/Disaster Recovery plan using SQL
Server 2005 Mirroring and Log shipping and I need help understanding which of
the following plans is the most possible/practical.
The entire system has 2 loacation (primary & DR) with 2 servers at each.
Scenario #1
DB1 & DB2 are at Loc1
and
DB3 & DB4 (DR servers) are located at Loc2
DB1 is the primary pricipal server and DB3 is the mirror
Log Shipping is done from DB1 to DB2 and from DB3 to DB4
Question with scenario #1 is can log shipping be set up to run from from a
mirror server?
Scenario #2
Log Shipping from DB1 (Loc1) & DB3 (both Loc2)
and
mirroring from DB1 to DB2 (both Loc1) and from DB3 to DB4 (Loc2)
Can Mirroring be done from a server that is being udated via Log shipping?
Scenarion #3
Mirroring from DB1 to DB2
and
LS from DB1 to DB3 & DB4
Any suggestions welcom
Thanks
--
MGHave you read Books Online?
http://msdn2.microsoft.com/en-us/library/ms177412(SQL.90).aspx
How about this great article?
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
There is a section titled: Database Mirroring and Log Shipping in this
article
Keith Kratochvil
"MGeles" <michael.geles@.thomson.com> wrote in message
news:BDB83C22-02A2-438E-9A09-5B2BED126836@.microsoft.com...
> I'm trying to come up with a db backup/Disaster Recovery plan using SQL
> Server 2005 Mirroring and Log shipping and I need help understanding which
> of
> the following plans is the most possible/practical.
> The entire system has 2 loacation (primary & DR) with 2 servers at each.
> Scenario #1
> DB1 & DB2 are at Loc1
> and
> DB3 & DB4 (DR servers) are located at Loc2
> DB1 is the primary pricipal server and DB3 is the mirror
> Log Shipping is done from DB1 to DB2 and from DB3 to DB4
> Question with scenario #1 is can log shipping be set up to run from from a
> mirror server?
> Scenario #2
> Log Shipping from DB1 (Loc1) & DB3 (both Loc2)
> and
> mirroring from DB1 to DB2 (both Loc1) and from DB3 to DB4 (Loc2)
> Can Mirroring be done from a server that is being udated via Log shipping?
> Scenarion #3
> Mirroring from DB1 to DB2
> and
> LS from DB1 to DB3 & DB4
> Any suggestions welcom
> Thanks
> --
> MG

Backup/DR configuration using mirroring and log shipping

I'm trying to come up with a db backup/Disaster Recovery plan using SQL
Server 2005 Mirroring and Log shipping and I need help understanding which o
f
the following plans is the most possible/practical.
The entire system has 2 loacation (primary & DR) with 2 servers at each.
Scenario #1
DB1 & DB2 are at Loc1
and
DB3 & DB4 (DR servers) are located at Loc2
DB1 is the primary pricipal server and DB3 is the mirror
Log Shipping is done from DB1 to DB2 and from DB3 to DB4
Question with scenario #1 is can log shipping be set up to run from from a
mirror server?
Scenario #2
Log Shipping from DB1 (Loc1) & DB3 (both Loc2)
and
mirroring from DB1 to DB2 (both Loc1) and from DB3 to DB4 (Loc2)
Can Mirroring be done from a server that is being udated via Log shipping?
Scenarion #3
Mirroring from DB1 to DB2
and
LS from DB1 to DB3 & DB4
Any suggestions welcom
Thanks
--
MGHave you read Books Online?
http://msdn2.microsoft.com/en-us/library/ms177412(SQL.90).aspx
How about this great article?
http://www.microsoft.com/technet/pr...5/dbmirror.mspx
There is a section titled: Database Mirroring and Log Shipping in this
article
Keith Kratochvil
"MGeles" <michael.geles@.thomson.com> wrote in message
news:BDB83C22-02A2-438E-9A09-5B2BED126836@.microsoft.com...
> I'm trying to come up with a db backup/Disaster Recovery plan using SQL
> Server 2005 Mirroring and Log shipping and I need help understanding which
> of
> the following plans is the most possible/practical.
> The entire system has 2 loacation (primary & DR) with 2 servers at each.
> Scenario #1
> DB1 & DB2 are at Loc1
> and
> DB3 & DB4 (DR servers) are located at Loc2
> DB1 is the primary pricipal server and DB3 is the mirror
> Log Shipping is done from DB1 to DB2 and from DB3 to DB4
> Question with scenario #1 is can log shipping be set up to run from from a
> mirror server?
> Scenario #2
> Log Shipping from DB1 (Loc1) & DB3 (both Loc2)
> and
> mirroring from DB1 to DB2 (both Loc1) and from DB3 to DB4 (Loc2)
> Can Mirroring be done from a server that is being udated via Log shipping?
> Scenarion #3
> Mirroring from DB1 to DB2
> and
> LS from DB1 to DB3 & DB4
> Any suggestions welcom
> Thanks
> --
> MG

Monday, February 13, 2012

Backup up warm-standby server

Hello all-
We have a two databases, BoxA and BoxB. BoxA is the live production server
and BoxB is a backup server that we do log shipping to.
Every couple of hours we ship logs from BoxA to BoxB. BoxB is a warm
standby SQL server.
We would like to backup BoxB, the warm standby, however we receive the
following error message:
"
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3036: [Microsoft][ODBC SQL
Server Driver][SQL Server]Database '' is in warm-standby state (set by
executing RESTORE WITH STANDBY) and cannot be backed up until the entire
load sequence is completed.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
terminating abnormally.
"
Now is there any easy way to backup the database on BoxB without dropping
the maintenance plans?
Any help would be greatly appreciated.
We just had a discussion about this. See the thread "How can I backup a log-shipped database?".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"anonymous" <a@.b.bad> wrote in message news:%23QWkYf5jEHA.3852@.tk2msftngp13.phx.gbl...
> Hello all-
> We have a two databases, BoxA and BoxB. BoxA is the live production server
> and BoxB is a backup server that we do log shipping to.
> Every couple of hours we ship logs from BoxA to BoxB. BoxB is a warm
> standby SQL server.
> We would like to backup BoxB, the warm standby, however we receive the
> following error message:
> "
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3036: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Database '' is in warm-standby state (set by
> executing RESTORE WITH STANDBY) and cannot be backed up until the entire
> load sequence is completed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
> terminating abnormally.
> "
> Now is there any easy way to backup the database on BoxB without dropping
> the maintenance plans?
> Any help would be greatly appreciated.
>
|||Instead of trying to backup the database that is constantly recovering, why
not add another target server to your logshipping configuration?
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"anonymous" <a@.b.bad> wrote in message
news:%23QWkYf5jEHA.3852@.tk2msftngp13.phx.gbl...
> Hello all-
> We have a two databases, BoxA and BoxB. BoxA is the live production
server
> and BoxB is a backup server that we do log shipping to.
> Every couple of hours we ship logs from BoxA to BoxB. BoxB is a warm
> standby SQL server.
> We would like to backup BoxB, the warm standby, however we receive the
> following error message:
> "
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3036: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]Database '' is in warm-standby state (set by
> executing RESTORE WITH STANDBY) and cannot be backed up until the entire
> load sequence is completed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
> terminating abnormally.
> "
> Now is there any easy way to backup the database on BoxB without dropping
> the maintenance plans?
> Any help would be greatly appreciated.
>

Backup up warm-standby server

Hello all-
We have a two databases, BoxA and BoxB. BoxA is the live production server
and BoxB is a backup server that we do log shipping to.
Every couple of hours we ship logs from BoxA to BoxB. BoxB is a warm
standby SQL server.
We would like to backup BoxB, the warm standby, however we receive the
following error message:
"
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3036: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Database '' is in warm-standby state (set by
executing RESTORE WITH STANDBY) and cannot be backed up until the entire
load sequence is completed.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE i
s
terminating abnormally.
"
Now is there any easy way to backup the database on BoxB without dropping
the maintenance plans?
Any help would be greatly appreciated.We just had a discussion about this. See the thread "How can I backup a log-
shipped database?".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"anonymous" <a@.b.bad> wrote in message news:%23QWkYf5jEHA.3852@.tk2msftngp13.phx.gbl...[vbcol
=seagreen]
> Hello all-
> We have a two databases, BoxA and BoxB. BoxA is the live production serve
r
> and BoxB is a backup server that we do log shipping to.
> Every couple of hours we ship logs from BoxA to BoxB. BoxB is a warm
> standby SQL server.
> We would like to backup BoxB, the warm standby, however we receive the
> following error message:
> "
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3036: [Microsoft]
[ODBC SQL
> Server Driver][SQL Server]Database '' is in warm-standby state (set by
> executing RESTORE WITH STANDBY) and cannot be backed up until the entire
> load sequence is completed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE
is
> terminating abnormally.
> "
> Now is there any easy way to backup the database on BoxB without dropping
> the maintenance plans?
> Any help would be greatly appreciated.
>[/vbcol]|||Instead of trying to backup the database that is constantly recovering, why
not add another target server to your logshipping configuration?
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"anonymous" <a@.b.bad> wrote in message
news:%23QWkYf5jEHA.3852@.tk2msftngp13.phx.gbl...
> Hello all-
> We have a two databases, BoxA and BoxB. BoxA is the live production
server
> and BoxB is a backup server that we do log shipping to.
> Every couple of hours we ship logs from BoxA to BoxB. BoxB is a warm
> standby SQL server.
> We would like to backup BoxB, the warm standby, however we receive the
> following error message:
> "
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3036: [Microsoft][ODB
C
SQL
> Server Driver][SQL Server]Database '' is in warm-standby state (set by
> executing RESTORE WITH STANDBY) and cannot be backed up until the entire
> load sequence is completed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE
is
> terminating abnormally.
> "
> Now is there any easy way to backup the database on BoxB without dropping
> the maintenance plans?
> Any help would be greatly appreciated.
>

Backup up warm-standby server

Hello all-
We have a two databases, BoxA and BoxB. BoxA is the live production server
and BoxB is a backup server that we do log shipping to.
Every couple of hours we ship logs from BoxA to BoxB. BoxB is a warm
standby SQL server.
We would like to backup BoxB, the warm standby, however we receive the
following error message:
"
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3036: [Microsoft][ODBC SQL
Server Driver][SQL Server]Database '' is in warm-standby state (set by
executing RESTORE WITH STANDBY) and cannot be backed up until the entire
load sequence is completed.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
terminating abnormally.
"
Now is there any easy way to backup the database on BoxB without dropping
the maintenance plans?
Any help would be greatly appreciated.We just had a discussion about this. See the thread "How can I backup a log-shipped database?".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"anonymous" <a@.b.bad> wrote in message news:%23QWkYf5jEHA.3852@.tk2msftngp13.phx.gbl...
> Hello all-
> We have a two databases, BoxA and BoxB. BoxA is the live production server
> and BoxB is a backup server that we do log shipping to.
> Every couple of hours we ship logs from BoxA to BoxB. BoxB is a warm
> standby SQL server.
> We would like to backup BoxB, the warm standby, however we receive the
> following error message:
> "
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3036: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Database '' is in warm-standby state (set by
> executing RESTORE WITH STANDBY) and cannot be backed up until the entire
> load sequence is completed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
> terminating abnormally.
> "
> Now is there any easy way to backup the database on BoxB without dropping
> the maintenance plans?
> Any help would be greatly appreciated.
>|||Instead of trying to backup the database that is constantly recovering, why
not add another target server to your logshipping configuration?
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"anonymous" <a@.b.bad> wrote in message
news:%23QWkYf5jEHA.3852@.tk2msftngp13.phx.gbl...
> Hello all-
> We have a two databases, BoxA and BoxB. BoxA is the live production
server
> and BoxB is a backup server that we do log shipping to.
> Every couple of hours we ship logs from BoxA to BoxB. BoxB is a warm
> standby SQL server.
> We would like to backup BoxB, the warm standby, however we receive the
> following error message:
> "
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3036: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]Database '' is in warm-standby state (set by
> executing RESTORE WITH STANDBY) and cannot be backed up until the entire
> load sequence is completed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is
> terminating abnormally.
> "
> Now is there any easy way to backup the database on BoxB without dropping
> the maintenance plans?
> Any help would be greatly appreciated.
>

Sunday, February 12, 2012

Backup to UNC path failing

Hi,

I have been trying to set up log shipping on a database with a backup size of ~190MB. However, I have been getting two error messages as stated below during the initial backup of the DB before it is restored on the warm standby.

1st Error
-----
Windows was unable to save all of the data for the file \Device\LanmanRedirector\<<UNC Path>>. The data has been lost. This error may be caused by a failure of your computer hardware or network connection. Please try to save the file elsewhere.

2nd Error
----
Write on '<<UNC path>>' failed, status 64. See the Sql Server eror log for more details. BACKUP database is terminating abnormally.

Backing up the database out side of the logshipping set up runs fine if I use a mapped drive, but it fails again when I use a UNC path. All the permissions are correct (I have backed up a smaller DB to the UNC path which completes successfully) so I am a little stumped.

Any help would be greatly appreciated

Thanks

RichI think this is a caching issue - check to see if you have network redirector caching enabled. The following link shows you how to disable:

link (http://support.microsoft.com/default.aspx?scid=kb;en-us;Q163401)

The following is another link that might be helpful if the above information does not solve your problem:

link (http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q321733&)|||Thanks for coming back so quickly. I thought that the cache could be the problem but noticed from the link you sent that it was specific to NT. I forgot to mention in my initial post that I am running on Win 2k SP2 Adv. Server boxes. Could this still be a problem with the more recent OS?

The second link you mentioned relates to a very specific record in the event log. The error I am experiencing is almost identical to that mentioned in the KB article all exceppt for the final staus code. I do not have c0000022 in my event log. Am I correct in therefore assuming that the workaround will not make any difference.

I know this is just more questions, but I do not have access to the boxes for the next couple of days and am trying to workout the best approach so as to try to reolve the problem when I get on site as quickly as possible.

Cheers

Rich