asked
I need script for Continues backup and restore of SQL database.
I want T-sql script for the scenario like this... We need to take full backup weekly, differential backup daily and transaction log backup hourly these backups will be copied from production server to DR site by third party tool. Now we need to restore these backups at DR server whichever is the latest in the correct sequence. Please Help. Thanks in advance!
yes, but we can't do lopshipping as there will not be a connection between production and DR SQL servers. So both the SQL can not listen to each other. The backup files will be shipped trough third party tool only
Ah, I would look at Powershell as @Kev Riley suggests in the previous post
http://ask.sqlservercentral.com/questions/97753/automating-database-restores.html Or you could use ...
http://www.mssqltips.com/sqlservertip/1584/auto-generate-sql-server-restore-script-from-backup-files-in-a-directory/ as a start
Thanks sp_lock, but this is true if only your source and destination dbs reside on same SQL server...
Hope this may help........see this page in My own blog
http://sqltechi.blogspot.in/2012/11/miracle-scripts-for-ever-in-sql-server.html
@askmlx I've noticed you always check the community wiki on your questions and answers. Don't bother doing that. All of them I've seen so far are not wiki style questions or answers, so you may as well earn your points.
It's useful for backup purpose. But in my case we need to restore backup by searching which is the latest full\diff\trn backup and apply it with no recovery. Moreover source SQL server and destination SQL server are not connected.
Just because you're not using the official in-built "log shipping" tools, doesn't mean you can't do the same thing by other means. Here's the outline. 1. create the backup jobs as normal through SQL Server 2. create a separate Windows batch job / script / whatever to do the file transfer and maintenance 3. create a third job to do the TLog restores WITH NO_RECOVERY Things to note that may be of assistance: * You can keep track of the most recent backup / tlog file that was successfully restored by querying the `msdb.dbo.restore_history` table * you can schedule the various tasks so they don't overlap; or, you can be a bit cleverer, and make sure that you keep the target a few hours behind by appropriate file-naming and date/time manipulation in the Restore script. I've had to do this before - only had one problem in two years running, and that was caused by a network outage.
OK, so if you need a script to restore latest full / diff / tlogs, I've lashed together a blog post about the script I wrote and am using...
http://thelonedba.wordpress.com/2013/01/24/bulk-restores-response-to-a-question-on-ask-sqlservercentral/
15 People are following this question.
