SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Backup of standby database


Backup of standby database

Author
Message
bpportman 52825
bpportman 52825
SSC-Enthusiastic
SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)

Group: General Forum Members
Points: 148 Visits: 400
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.
pmaynard
pmaynard
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
chuck.hamilton
chuck.hamilton
Old Hand
Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)

Group: General Forum Members
Points: 364 Visits: 402
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.
derek.colley
derek.colley
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1946 Visits: 603
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??!Crazy

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.

chuck.hamilton
chuck.hamilton
Old Hand
Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)

Group: General Forum Members
Points: 364 Visits: 402
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.
derek.colley
derek.colley
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1946 Visits: 603
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 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.

derek.colley
derek.colley
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1946 Visits: 603
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 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.

chuck.hamilton
chuck.hamilton
Old Hand
Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)Old Hand (364 reputation)

Group: General Forum Members
Points: 364 Visits: 402
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
phil.vacca
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 188
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
phil.vacca
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 188

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search