tracker

TQMC

TQMC has acquired wide Domain Knowledge and Experience. You can FREELY access it here and here

DISCLAIMER: This matter here is a guide only. For authentic and up-to-date information, please contact TQMC.

The DIRECTIVES and STANDARDS listed here may have been subsequently REVISED . You must refer to the CURRENT REVISION and AMENDMENTS if any.

Wednesday, September 24, 2008

SonaSafe® for Microsoft SQL Servers
SonaSafe® for SQL Server offers very unique high availability and disaster recovery functionality. With Sonasafe for SQL Server implementation, failing over to secondary or destination server, is as simple as clicking a few buttons on an easy to use web-user interface. From there, when the old primary site, or source server, comes back, failing back is again very simple without the need to perform the full backup and full restore.
The Sonasoft Standby Solution builds easy-to-manage standby plans that effectively replicate your data without interfering in normal business operations. This includes CPU utilization on the primary server as well in limiting network contention by reducing network traffic through on the fly, high speed compression.
The Sonasoft Standby Solution automates all of the critical steps involved, making the process completely transparent to the user. The solution is driven by self-explanatory screen wizards, armed with highly powerful monitoring capabilities at different levels. When disaster strikes, role switching is just one click away, as a series of complex database operations are performed in the background to switch the destination server(s) to the primary role.
Key Capabilities:
Support for SQL 7.0, 2000, and 2005
Point-of-failure or point-in-time recovery
Cluster aware; support for both active-passive and active-active SQL clusters
Compression reduces storage costs by up to 95%
Web-based interface; can be accessed from anywhere in the world
Automated policy-based purging functionality
Ability to manage multiple servers through a management console
Ability to create user defined ports to enhance security
Ability to backup and restore virtual systems like VMware and Microsoft Virtual Server
Ability to execute scripts automatically before and after a backup task
Excellent diagnostic capability
Key High Availability and Disaster Recovery Capabilities:
Affordable, easy to use high availability and disaster recovery solution for SQL server
When disaster strikes, role switching is one click away
The failover SQL server can be anywhere; no distance limitation
Create standby using standard edition of SQL server
Supports one-to-one, many-to-one and one-to-many failover scenarios
Allows use of read-only Standby Server for reporting purposes
Failover/Failback capability without the need to apply full backup or restore
Provides built-in network resilience that assures safe transfer of data
SonaSafe® for Microsoft SQL Server Diagrams:
Disaster Backup Plan
Create Standby Plan

more

disaster recovery







Chad Boyd
Automate a Database Restore
It's quite common to see automated/custom procedures for backing up a database/log - nearly everywhere I go companies have custom backup procedures and processes to handle backups, logging of backups, naming standards, locations, etc. However, it's rare that I find processes/procedures in place to automate the restore of database/log backups - so rare in fact that I can recall only a single time in the last 3 years that I've seen it. Kind of odd considering you typically want to ensure the recovery process goes quickly when needed - nothing underlines this more than an actual disaster, and when you're in the middle of one, the last thing you want to have to worry about is writing lines of code to build the restore statements for your recovery path, which could be made up of hundreds or thousands of backup sets.
Enter the sp_backup_restoredb procedure - I wrote this procedure to automate the most common restore paths in a variety of scenarios. With it you can automate the restore of a database using msdb backup history information for the given database - don't have the history information, or are restoring to another server? No worries, just point it at the directory location (or locations) that hold the backups using an optional pattern filter and let it rip. Need to restore just a single file, file group, or page(s)? No problem either. Want to rename the database during the restore? Ok, no problem. Need to move the locations of the log/data files for the database during the restore? Just tell it where you want them to go and it will do the rest (and you don't even have to know what files exist in the database, or where they previously existed, nor do you have to know even how many files there are within the database - it will simply round-robin the files among the locations you specify). Want to use LiteSpeed? Sure, we can do that. Do you use a single mediaset for each backup, or do you use a single mediaset for a group of backups, or do you use a single mediaset forever and always? No problem, handle them all. Need to recovery to a specific point in time? Just specify the value. Want to recover the database? Leave it in recovery? Use a checksum (or not)? Silently ignore restore errors? Suppress execution and output just the restore statements? Check, check, check, check, and check.
There are lots of interesting uses for this type of procedure beyond just disaster recovery situations - can be leveraged to test recovery processes, to restore varying types of backups to a single reporting server, to validate what you have for backups, or for migration scenarios (and there are probably other cases as well).
Here are some samples executions:
-- Restore the testDb database, suppressing actual execution, using data from msdb, not performing
-- recovery, native restore, and the most efficient path
exec dbo.sp_backup_restoredb @dbname = 'testDb', @opts = 1;
-- Same thing, only instead of using data in MSDB, use the 2 specified locations for any .bak file
-- starting with 'testDb'exec dbo.sp_backup_restoredb @dbname = 'testDb', @restorepaths = 'c:\temp;\\backupServerB\backupShare\testDb;',
@filePattern = 'testDb*.bak', @opts = 1;
-- Same thing, only use LiteSpeed syntax...
exec dbo.sp_backup_restoredb @dbname = 'testDb', @restorepaths = 'c:\temp;\\backupServerB\backupShare\testDb;', @opts = 5;
-- How about changing the name on restore?exec dbo.sp_backup_restoredb @dbname = 'testDb', @newDbName = 'testDb_newName',
@restorepaths = 'c:\temp;\\backupServerB\backupShare\testDb;',
@opts = 5;
-- What about moving the log/data files around? Here we will place data files for the database in the
-- 4 specified locations (semi-colon delimited) - if there are less than 4 data files, they will simply
-- be placed in the locations in the order specified up to the number of data files there are (so, if
-- there were 2 data files, 1 would go to M:\SqlData and 1 to N:\SqlData). If there are more than 4
-- data files, they will continue to round-robin among the specified locations in order specified
-- until there are no more files (so, with 7 data files, you'd end up with 2 in M,N,O and 1 in P)exec dbo.sp_backup_restoredb @dbname = 'testDb', @newDbName = 'testDb_newName',
@restorepaths = 'c:\temp;\\backupServerB\backupShare\testDb;',
@moveLogsTo = 'l:\SqlLogs\',
@moveDataTo = 'm:\SqlData\;n:\SqlData\;o:\SqlData\;p:\SqlData',
@opts = 5;
-- Want to stop at a particular point?exec dbo.sp_backup_restoredb @dbname = 'testDb', @newDbName = 'testDb_newName',
@restorepaths = 'c:\temp;\\backupServerB\backupShare\testDb;',
@moveLogsTo = 'l:\SqlLogs\',
@moveDataTo = 'm:\SqlData\;n:\SqlData\;o:\SqlData\;p:\SqlData',
@stopAt = '2008-07-29 15:52:20.310',
@opts = 5;
-- Same thing, only ignore the use of an DIFFERENTIAL backupsexec dbo.sp_backup_restoredb @dbname = 'testDb', @newDbName = 'testDb_newName',
@restorepaths = 'c:\temp;\\backupServerB\backupShare\testDb;',
@moveLogsTo = 'l:\SqlLogs\',
@moveDataTo = 'm:\SqlData\;n:\SqlData\;o:\SqlData\;p:\SqlData',
@stopAt = '2008-07-29 15:52:20.310',
@opts = 21;
-- Perform recovery at the end of the restore process...exec dbo.sp_backup_restoredb @dbname = 'testDb', @newDbName = 'testDb_newName',
@restorepaths = 'c:\temp;\\backupServerB\backupShare\testDb;',
@moveLogsTo = 'l:\SqlLogs\',
@moveDataTo = 'm:\SqlData\;n:\SqlData\;o:\SqlData\;p:\SqlData',
@stopAt = '2008-07-29 15:52:20.310',
@opts = 23;
-- Force existing users out of the new database prior to restoring...exec dbo.sp_backup_restoredb @dbname = 'testDb', @newDbName = 'testDb_newName',
@restorepaths = 'c:\temp;\\backupServerB\backupShare\testDb;',
@moveLogsTo = 'l:\SqlLogs\',
@moveDataTo = 'm:\SqlData\;n:\SqlData\;o:\SqlData\;p:\SqlData',
@stopAt = '2008-07-29 15:52:20.310',
@opts = 31;
-- Perform a PAGE level restore, getting the pages to be restored from the msdb
-- suspectpages database table...
exec dbo.sp_backup_restoredb @dbname = 'testDb', @opts = 65;
The usage output looks like follows:
USAGE:
exec dbo.sp_backup_restoredb @dbname, @restorepaths, @moveLogsTo, @moveDataTo, @fileFilGroupPageString, @newDbName, @filePattern, @stopAt, @opts
PARAMETERS:
@dbname
DB to be restored
@restorepaths
Path(s) to the files containing backups to be restored from, semi-colon delimited...if not passed, we try to grab information from MSDB table instead
@moveLogsTo
Path to location that log files for the database being restored should be moved to...semi-colon delimited list...
@moveDataTo
Path(s) to location(s) that data files for the database being restored should be moved to...semi-colon delimited list...if more data files exist than paths are passed, data files are simply restored in a round-robin fashion to the locations specified...if more paths are specified here than there are data files, the first paths listed are used up to the # of data files, then the other paths are simply ignored...
@fileFilGroupPageString
Is a string of either a file, filegroup, or page string that will be used (if passed) as the portion of the restore string - should match the proper format as outlined in BOL for this section exactly, since we basically just append here.
@newDbName
Name of the restored database - if left default/null, the @dbname is used...
@filePattern
Pattern of files to match for within the @restorepaths - by default, is everything (i.e. *) - only valid if a value is specified for @restorepaths
@stopAt
Date/time to stop at within the restore, if specified...
@opts
Options that drive execution for the proc. As follows: 1 bit - If set, execution is suppressed and the strings are simply output... 2 bit - If set, recovery is performed at the end of all restores...by default, db is left in norecovery state... 4 bit - If set, LiteSpeed is used for recovery statements... 8 bit - If set, we will forcefully drop existing connections to the db in order to allow restore... 16 bit - If set, we will NOT use diff backups in the restore, only full and tlog backups... 32 bit - If set, CHECKSUM is used for the restore - this is only valid if a native restore is used... 64 bit - If set, PAGE level restore is used, and the pages to be restored are built from the data in the suspect_pages table in the MSDB. This cannot be used currently with LiteSpeed restores... 128 bit - If set, and a value is set in @restorepath, we will try to find a time/date stamp within the name of each file found in the @restorepath matching @filePattern - we will simply try to find 14 concurrent numbers within the name to signify as such... 256 bit - If set and the 1 bit is not set (i.e. we are executing), errors raised during the execution of the restore statements will be silently captured and reported without re-raising back to the calling code. Error number and message will be output as a print statement, but no error will be raised...
Enjoy!
Chad Boyd ~~~ This posting is provided "AS IS" with no warranties, and confers no rights. Use of any included script samples are subject to the terms specified at http://www.mssqltips.com/disclaimer.asp and http://www.mssqltips.com/copyright.asp.

sp_backup_restoredb.sql
Published Jul 29 2008, 06:19 PM by Chad Boyd
Filed under: ,
Attachment: sp_backup_restoredb.sql
Comments

Christopher Steen said:
Link Listing - July 29, 2008
July 30, 2008 6:48 AM

Christopher Steen said:
Sharepoint Silverlight Drag, Drop, Import and Export [Via: Michael Washington ] Creating a WSS / MOSS...
July 30, 2008 6:49 AM

primer200 said:
Great post!
Most of the times we require restore a database to another server to a point in time. All we know is the orginal server name, database name, the target server name, target database name, the file path for mdf and ldf files on the target server and a point in time.
In this case we need first query the msdb of the original server to get a list of files and its location required to restore to the point in time. If you can include this, I rekon the sp is even more useful. I have done this myself, but I I had to use some ugly coding such as cmdshell.
August 19, 2008 9:18 PM
About Chad BoydChad is an Architect, Administrator, and Developer with technologies such as Sql Server (and all related technologies), Windows Server, and Windows Clustering. He currently works as an independent consultant and also spends a significant amount of time writing, talking, presenting and blogging about Sql Server in person and online at http://mssqltips.com. In the past, Chad has worked with companies and organizations such as Microsoft Corporation and The American Red Cross, and provided consulting/support services at companies such as Bank of America, HP, Citigroup, Qualcomm, Scottrade, TJX, SunTrust, and Zurich Financial Services. For over 3 years with Microsoft Corporation Chad was responsible for providing onsite and remote support, guidance, and advice with SQL Server products to some of Microsoft’s foremost enterprise customers running the largest, most complex SQL Server installations and configurations in the world. This included all SQL Server products and versions, including SQL Server 7.0, 2000, 2005, and recently 2008, the SQL Server database engine, Reporting Services, SSIS/DTS, Notification Services, and Analysis Services on both 32 and 64 bit systems. Chad's primary responsibilities today include troubleshooting critical server situations, performance tuning and monitoring, disaster recovery planning and execution, architectural guidance for new Sql Server related deployments, and delivering deep technical workshops/presentations/proof-of-concept sessions covering a variety of technologies and functionality. Chad regularly posts Sql Server related content, tools, and advice with the mssqltips team at http://blogs.mssqltips.com/blogs and http://mssqltips.com. Chad can be contacted via his blog or email at chad dot boyd dot tips at gmail dot com.
This Blog
Home
Contact
About
Syndication
RSS
Atom
Comments RSS
Recent Posts
Estimating Data Compression ratios for all...
Automate a Database Restore
Installing Clustered SQL Servers - Outline, Checklists, Document Sheets
Katmai (Sql 2008) - Transaction Logging Enhancements
SSD and SQL - Fragmentation Impact
Tags

























































Blog Roll
SQL Server Storage Engine
Statistics IO
Charlie Smith from AdCenter
The Power of Software
All about Microsoft
Clustering and High Availabilty Team at Microsoft
Storage Bits
The Social Web
Googling Google
Emerging Tech
The Apple Core
Tuaw
SqlSkills
Douglas Laudenschlager and SSIS
SQLCAT.com
SQL Server Security Team
SqlCat Team on MSDN
SQLBlog.com
PSS Engineers Team
Powershell Team at Microsoft
Joel on Software
Lies, damned Lies
Michael Manos - Microsoft Data Centers
Mike on Ads
Coding Horror
Craig Freedman
Data Dude - GertD
Green Data Center Blog
Jamie Mac
Stack Overflow
Archives
September 2008 (1)
July 2008 (1)
June 2008 (1)
May 2008 (1)
April 2008 (1)
March 2008 (5)
February 2008 (6)
November 2007 (3)
October 2007 (24)
Copyright (c) 2006-2008

more
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)