Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Pidi
Starting Member
5 Posts |
 Posted - 2006-03-11 : 06:16:21
|
| I´m looking for some feedback on an automated restore system for SQL server i wrote. Any hints and discussion will be appreciated. Here is the concept. It´s not only an idea, it is existing and it is working:GoalIf our main SQL servers may have crashed and cannot be fixed, what we all want is to restore our lasted backup of the database to another system in the shortest possible period of time.All this begann, when I realized, that a restore of a full backup + all TRN-Backups to a new "clean" Sql system would be a nightmare when having some 80 TRN-files (we save the T-Logs every 10 minutes), since that new Sql server won´t have any backup history. One of the solutions I was offered, was the usage of ArcServe, but I´m not one of the fans of that software.Besides that, I wanted to shorten the time, until my StandBy Server would be up und running to a MINIMUM.My current solution for this are 2 VB-scripts I wrote. YES - this has been realized with VBS. The Transact SQl commands are only the subsequent steps in that szenario.What we´ve gotWe have a main SQL server, and a "Standby Server" with the SQL Server software pre-installed. If the main server crashes (although there is extreme redundancy) we want to RESTORE the latest full backup plus all TRNs stored after that full backup to the Stby Server and work can go on.We do a full backup every night at 1:00 am and TRNs every 10 minutes from 6:00 am to 9:00 pm, and store these files to a 1.5 TB NAS device. We don´t like tapes, have no experience with those and the VN scripts will only work for backups sent to disk!VB-script 1This script is executed every morning at 5:00 am on the Standby machine by the task manager. It:- searches the latest Backup on the NAS device- deletes an existing DB on the StdBy with the same name as the one to restore by calling OSQL.EXE with an TSQL command (sp_detach_db)- additionally deletes all files oh that DB (sp_detach should do that, but doesn´t ...?)- again calls OSQL.EXE with another TSQL command, that is generated dynamically by the VB script and contains the path and name of the full backup with the NORECOVERY option.So when this script has been executed automatically, we do have a restored Database from the recent backup with the state "loading ..."(we´ve got it all in german, where it´s "Lädt...")VB-script 2This script will ONLY be used, when the main SQL server has crashed and the DB is lost. It:- searches all TRN files on the NAS device, that have been stored AFTER the latest full backup and stores them to an array- all of the found TRN-files within the array will be sorted by date and time to get into correct order- it writes the sorted TRN-files to a <xxx>.sql file by adding TSQL code > "RESTORE LOG .... <trn-file> ... NORECOVERY"- for the last LOG to restore it will of course end the line with a RECOVERY statementThat means, by running this 2nd VB script we get a TSQL script file, that we can load into our query analyzer on our standby server and execute all commands with a singe F5 hit. The gerenation of the TSQL script takes less than 10 seconds in our environment. The execution of the RESTORE LOGS commands of course depend on their size, but usually this is a job of minutes ...ConclusionSince we do the full backup every morning automatically, we can get a fully restored Standby server within less than 5 minutes until this system will be up and running.CommentsI´m a real beginner to SQL server, and may have made severe mistakes or choosen the wrong way - please let me know. If anyone ist interested in the VB scripts send me an e-mail, or let me know, where to upload them in this forum.Pidi |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-11 : 11:56:46
|
| Sounds like you could use "Log Shipping"Basically each TLog backup you make on the Main Server is copied to the Standby Server and restored - but the database is kept in StandBy mode. So you can restore the next log 10 minutes later, and so on, and then actually just put eh Standby Server "live" if the main server fails.You could consider generating some DIFFERENTIAL backups on your Main Server, say, hourly - then you would only have to restore HOURLY DIFF and the subsequent 10-minutely TLog backups.Kristen |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-03-11 : 12:35:21
|
| I have a few comments about this.1. The following is unnecessarily complicated. You can just do a restore with the move option. There is an example in the RESTORE subject in SQL Server Books Online. "- deletes an existing DB on the StdBy with the same name as the one to restore by calling OSQL.EXE with an TSQL command (sp_detach_db)- additionally deletes all files oh that DB (sp_detach should do that, but doesn´t ...?)- again calls OSQL.EXE with another TSQL command, that is generated dynamically by the VB script and contains the path and name of the full backup with the NORECOVERY option."2. I don’t see the point of doing any of this in VB Script. I have general purpose stored procedures that do the restores that you are doing with your VB Script. I think if you search this site and the blogs, you can find plenty that do this.3. Did you consider using SQL Server’s built-in log shipping capability? It is designed to keep the servers in-sync at all time, and automates the whole process. If minimal downtime is your objective, it is something you should think about.4. What are you doing to keep logins, DTS packages, and jobs in-sync? It’s easy to forget about new logins, DTS, and scheduled job changes.5. One last thing. The Script Library is not the place to post questions about administration issues. From the Script Library description: “This section is for any originally written scripts you'd like to share with the group.”CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-11 : 14:16:31
|
| "SQL Server’s built-in log shipping capability"MVJ: Is that perhaps only in the Enterprise Version?Kristen |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-03-11 : 16:42:40
|
| iirc MS Log Shipping requires Enterprise Edition. Look at Log Shipping in the BOL. You can get to it, and play with it, from Maintenance Plans (and only maintenance plans).Of course, could can always do the log shipping yourself - that gives you more control - for example, my current log shipping solution allows us to selectively log ship certain databases, and allows us to trigger ad hoc log shippings. That last bit was the important piece for use - we are using log shipping to create specific point-in-time copies of several databases, for reporting - we have a requirements for a regular schedule - refreshes every 2 hours during working day, yet we have specific reporting requirements that require databases backups made at the precise point-in-time our "overnight" process finishes.hmm - I digress a lot, and way off from the initial requirements - but is does (i hope) show the strength and versatility of a custom log shipping solution. BTW, we are currently log shipping 9 databases that range from 35gb to about 1 gb (total +- 50 GB). Currently, we get a refresh through in 20 minutes - that will improve significantly once I get time to work on a trickle feed for the log backups (we do log backups every 15 minutes atm, and copy as part of the "log-ship" process, so that 20 minutes includes the copy time for 8 log backups for each of the 9 db's - 72 files copies).*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-03-12 : 13:12:33
|
| Have a look athttp://www.nigelrivett.net/SQLAdmin/s_RestoreDatabase.htmlIt goes with other scripts on the ame site to do backups and restores and maintain a standby server.They replace the sql server log shipping which I think isn't very well implemented.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Pidi
Starting Member
5 Posts |
Posted - 2006-03-12 : 13:27:10
|
| Thanks a lot to all of you for the replies. I mentioned, that I´m, a beginner with SQL server, but I was sure, there is a better way to get a minimum recovery time.However, your answers show me, that the system house which is supposed to take care of our SQL server have no idea, what they are doing, since they not even know the "Log shipping" (we do own the enterprise version).And sorry for choosing the wrong place by posting this within here.PidiNew owner of SQl Server for Navision |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-13 : 04:02:54
|
"the system house which is supposed to take care of our SQL server have no idea"I've seen that before I'm afraid  Good luck, and come back with any other questions we might be able to help with.Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-03-13 : 05:29:55
|
| A company I was at relied on the host to do log shipping and backups.The database was in simple recovery mode and no one would believe me when I said they couldn't have set up log shipping.Testing the backups was done by just requesting a restore of the current database on another server - worked fine. I said they should request a restore of a backup from a specific data - eventually they agreed and discovered that the host was only performing backups when they were requested.At this point they admitted they didn't know what log shipping was and didn't have any sql server expertise at all.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-03-13 : 10:52:26
|
| ... at which point, someone took out the contract, and sued them? (yeah right)..... It's amazing how companies/people get away with thigns like this.*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-03-13 : 11:19:17
|
| NopeAt which point someone took out the contract and realised they were mistaken.Besides it was too late to find another host.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Automatic Restore by script ...