Backup of standby database

Question

Backup of standby database

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 1

What about combining it with weekly snapshot replication too? I think this will also cover off the DDL angle and while it's true that eg a table definition change won't be replicated with tx rep., it should be covered in the snapshot. I thought from the OP that you were mainly looking for ways of backing up the standby? Unless your schemas change daily, replication could do the trick?

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 2

Dupe post removed, sorry 🙂

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

chuck.hamilton SSCommitted Points: 1539 More actions · Answer 3

Another concern I have with replication are the infamous system deletes. MS in their infinite wisdom decided that keeping the publisher and subscriber in sync is more important than transaction durability. If something goes wrong in the replication process, committed transactions can silently be deleted later. This is a headache I don't care to deal with.

On a different note, I think I may have found a way to run a regular backup of a log shipping standby database. I found this on MS Connect

https://connect.microsoft.com/SQLServer/feedback/details/466121/allow-backup-of-database-in-standby-mode

1. Create a new DB with the same number of files as the secondary to be backed up.

2. Take the new DB and the log shipped secondary offline.

3. Copy log shipped secondary's files over the new DB's files.

4. Bring both online.

5. The new DB is consistent and operational and can be backed up, etc.

I'm testing it now. One thing I havent understood yet though is why I dont need to do a "restore database NEWDB with recovery" after step 4. I guess it has something to do with the state of the DB in the MASTER database.

phil.vacca SSC Enthusiast Points: 165 More actions · Answer 4

Thank you! This method worked for us as well, and nothing else I tried would. Sure it seems a little unorthodox, and it involves stopping the server (ugh); but at least it works.

phil.vacca SSC Enthusiast Points: 165 More actions · Answer 5

Thank you! This method worked for us as well, and nothing else I tried would. Sure it seems a little unorthodox, and it involves stopping the server (ugh); but at least it works.
0808 (8/14/2008)
Here's what we did: Setup a daily scheduled OS task (not Agent!) that runs a .BAT file.
This .BAT file does this:
1. runs a SQL script that:
- sets single user
- drops the development DB
- creates an empty development DB
2. uses "net stop" commands to stop SQL Server services. Order is important, e.g. stop Agent before stopping sql engine)
3. does an OS copy of the read only database files (MDFs and LDFs). e.g.:
- copy RO_database.mdf Dev_database.mdf
- copy RO_database.ldf Dev_database.ldf
4. restarts SQL services (in reverse order from #4)
5. runs a sql script to "activate" the dev database
- EXEC SP_DBOPTION 'Dev_database', 'read only', 'false'
- EXEC SP_DBOPTION 'Dev_database', 'dbo use only', 'false'
- EXEC sp_change_users_login 'Auto_Fix', 'username'
- ALTER DATABASE Dev_database SET RECOVERY SIMPLE
Note that I was having some timing problems on some of the steps above, with certain tasks sometimes failing because the previous step wasn't completely finished. Those problems disappeared once I put a 5 second sleep/delay between every step.
It was a bit of a hassle to setup, but requires no ongoing maintenance. My Dev database is refreshed from production every day. A potential drawback is that the SQL server is completely down for a while each night. This wasn't a problem for us.

. . . Probably should have quoted the method that worked, eh?

chuck.hamilton SSCommitted Points: 1539 More actions · Answer 6

I know this is an old topic but I thought I'd update it anyway. I did find a way to backup standby databases using the "backup" command. It works in SQL 2008 R2 and should work in any version that supports standby. I found it here.

The one requirement is that the source DB that you're backing up must be in standby read-only mode.

Basically you create a dummy database with the same # and types of files as the DB to be backed up, offline both databases, copy the files from the source DB over top of the dummy DB, and then online both databases. Your dummy DB is now a copy of the source DB at the point in time when it was offlined. Backup the dummy DB and you have a backup of the source DB.

I would suggest also backing up the *.tuf file associated with the source DB. You'll need it if you ever plan to do a restore and roll forward the transaction log backups.

When I do this, I leave the LSCopy job enabled but disable the LSRestore job so it doesn't screw anything up.