Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Backup of standby database Expand / Collapse
Author
Message
Posted Thursday, April 28, 2011 1:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 6:39 AM
Points: 22, Visits: 306
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.


I followed the steps except I did not run the 4 commands at the end and it works.
Post #1100525
Posted Tuesday, May 31, 2011 8:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 3, 2011 7:38 AM
Points: 1, Visits: 6
Hello, What you describe is exactly what I'm looking for, it would work perfectly in our environment. Is it possible to get a copy of the bat file? I know how much effort it takes to create it and I would really appreciate it if anyone could post an the actual working bat file. I would reallly appreciate it . Phil

Post #1117455
Posted Thursday, May 24, 2012 8:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 7:54 AM
Points: 49, Visits: 236
Have you ever tested recovering from a standby database backed up in this manner? I'm working on a very similar backup strategy. The one thing I'm doing differently is that instead of stopping the SQL Server services and copying the raw database files all at once, I've written a tsql script that runs through the secondary databases one by one and...

1) disables the recovery job
2) offlines the db
3) xcopy the db's files to the backup directory
4) onlines the db
5) enables the recovery job

This works. So far so good.

I have 100+ standby databases on my secondary server and by doing it this way I can continue to allow the logs to be copied and applied on other secondary databases while I'm backing up the one.

Here's my question though and it pertains to how to use these backups to restore the primary database.

Full recovery - this is a piece of cake. I end recovery on the standby (recover database ... with recovery), take a backup, and restore the backup on the primary.

Point in time recovery - Is this possible and if so how?

Lets say I need to restore to a point in time prior to when the last log was applied. I can restore an older copy of the mdf and ldf files, as well as the necessary log backups up to the point where I want to recover. How do I attach the mdf/ldf files (presumably under a different DB name) to begin rolling forward the logs? If you try to do this you get the "You cant attach a database that was being recovered" error and the attach operation fails.
Post #1305920
Posted Thursday, May 24, 2012 9:07 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 22, 2013 9:13 AM
Points: 496, Visits: 601
What about using transactional replication to do this? To minimise load on the production, set up the distributor on a separate server (even the subscriber) and have one-way transactional replication from production to your non-production box (if performance is important, use pull rather than push replication). Both DBs will be online. Then you can run your reports from your non-production box knowing it's up-to-date. You can also backup this database too.

I think there's a stairway about it on this site.



---

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

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
Post #1305942
Posted Thursday, May 24, 2012 9:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 7:54 AM
Points: 49, Visits: 236
I'm not a big fan of replication in any of its forms. I want this to be a worry free backup solution.

Log shipping copies everything. Unless I am mistaken, replication does not. For example changes in table structures, stored procedures, functions, views, indexing, etc. Those things can't be published as an article AFAIK, but log shipping covers it all.
Post #1305959
Posted Thursday, May 24, 2012 11:50 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 22, 2013 9:13 AM
Points: 496, Visits: 601
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??!

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
Post #1306037
Posted Thursday, May 24, 2012 11:50 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, August 22, 2013 9:13 AM
Points: 496, Visits: 601
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??!

Want to get the best help? Click here http://www.sqlservercentral.com/articles/Best+Practices/61537/ (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.
Post #1306038
Posted Thursday, May 24, 2012 12:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 7:54 AM
Points: 49, Visits: 236
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.
Post #1306069
Posted Tuesday, July 31, 2012 8:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:35 PM
Points: 39, Visits: 185
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.
Post #1337950
Posted Tuesday, July 31, 2012 8:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:35 PM
Points: 39, Visits: 185

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?
Post #1337951
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse