Disaster Recovery Week

Steve Jones from SQLServerCentral.com

This week we highlight the practice of being prepared for disasters at SQLServerCentral. More »

Logging – Level 11 of the Stairway to Integration Services

Andy Leonard from SQLServerCentral.com

Learn how you can add custom logging to your Integration Services packages in this installment of our Stairway to Integration Services. More »

In The Real World - Disaster!

Steve Jones from SQLServerCentral.com

A real world account of disaster recovery. (This article is being republished after the recent hurricane that hit the US East Coast).  More »

Win a Collection of SQL and .NET Books. And a Bookshelf

Press Release from SQLServerCentral.com

After winning a number of awards for our software, Red Gate is giving away books to 300 people as a celebration. More »

Handling Backups for Rapid Resilience

Additional Articles from SimpleTalk

The backup and restore system in SQL Server hasn't changed a great deal over the years despite a huge growth in the typical size of databases. When disaster strikes, and an important service is taken offline while a restore is performed, there is often time to reflect on whether it might be possible to design databases for a more rapid recovery of the most critical parts of a database application. More »

From the SQLServerCentral Blogs - Logins vs Users

Kenneth Fisher from SQLServerCentral.com

Logins are not Users. It’s a pretty easy concept but one that seems to give a lot of people problems.... More »

There was a large hurricane in the US a short while back. It was a devastating storm for many people, and my heart goes out to those that suffered or are still suffering. There are lots of lessons to be learned in many areas, but a few surprising ones for those people that run technology infrastructures. A number of data centers were shut down because of physical flooding, but others were shut down after electrical substations failed and they were unable to run their generators.

When I evaluated data centers a decade ago, I was always shown the number of UPSes on site, and the high capacity diesel generators with large fuel tanks that were available just in case of extended outages. Salespeople would bring out their contracts that showed suppliers would commit to refilling their fuel tanks, providing for every contingency.

Except a lack of diesel. In Denver we wouldn't have the need for a staircase bucket brigade, but we might have the need for a roadside chain gang carrying containers in a blizzard. You cannot plan for every contingency because there are many factors out of your control. When a disaster gets large enough, it doesn't matter what you have contracted for. There will be outside influences, like the lack of elevators or the inability of trucks to physically reach your location.

One of the Red Gate customers was in New York and almost lost their data center after the storm. They asked our technical team to help them prepare scripts to restore data backups for their clients in the event they had to send the full, diff, and log backups (along with application code) to the customers. It was an last-ditch effort to allow their customers to continue to run their service. Fortunately they never had to use any of the scripts, but it did help the company realize they need to build more options for business continuity in the event of future disasters.

I hope none of you ever experiences anything like Hurricane Sandy. You can't completely prepare, but you can practice your recovery skills on a regular basis and be prepared to respond when disaster strikes.

Steve Jones from SQLServerCentral.com

» Join the debate, and respond to today's editorial on the forums

The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.

• Watch the Windows Media Podcast - 22.4MB WMV
• Watch the iPod Video Podcast - 21.4MB MP4
• Listen to the MP3 Audio Podcast - 4.3MB MP3

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed: or now on iTunes!

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

You can also follow Steve Jones on Twitter:

» To submit an article, rant or editorial, log in to the Contribution Center

Today's Question:

Suppose there is a procedure defined as:

create proc rename_test
 as
begin
 select *
  from mytable
end

Now change the procedure name using sp_rename:

exec sp_rename rename_test,rename_stored_proc

Now run the following statements.

1. sp_helptext rename_stored_proc
2. select OBJECT_DEFINITION(OBJECT_ID('rename_stored_proc'))

Now create a new procedure as

create proc rename_test2
 as
begin
 select *
  from mytable
end

Now right click on the procedure and select 'modify', get the alter proc script and change the procedure name to 'rename_by_alter'. Execute the script.

Now run the following statements

3. sp_helptext rename_by_alter

4. select OBJECT_DEFINITION(OBJECT_ID('rename_by_alter'))

The question is what procedure name the statements 1,2,3 and 4 show respectively when executed

By swaran kalair

Think you know the answer? Click here, and find out if you are right.

This question is worth 1 point in this category: T-SQL. We keep track of your score to give you bragging rights against your peers.

We'd love to give you credit for your own question and answer. To submit a QOD, simply log in to Contribution Center.

Performance Tuning with SQL Server Dynamic Management Views

This is the book that will de-mystify the process of using Dynamic Management Views to collect the information you need to troubleshoot SQL Server problems. It will highlight the core techniques and "patterns" that you need to master, and will provide a core set of scripts that you can use and adapt for your own requirements. Grab your copy today from Amazon!

Let's have 2 instances of SQL Server 2012 with firewalls disabled: Srv1\M1 and Srv2\M2. Let's have a database Db with full recovery model on the first instance. Follow these instructions:

1. Make a full backup of Db on Srv1\M1.
2. Restore a Db on Srv2\M2 with NORECOVERY.
3. Create and start mirroring endpoint on Srv1\M1 (assume port and endpoint name are available):

   CREATE ENDPOINT [Mirroring]
   AS TCP (LISTENER_PORT = 5023)
   FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)
   GO
   ALTER ENDPOINT [Mirroring]
   STATE = STARTED
   AS TCP (LISTENER_PORT = 5023)
   FOR database_mirroring (ROLE = PARTNER)

4. Create and start mirroring endpoint on Srv2\M2 (assume port and endpoint name are available):

   
   CREATE ENDPOINT [Mirroring]
   AS TCP (LISTENER_PORT = 5024)
   FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)
   GO
   ALTER ENDPOINT [Mirroring]
   STATE = STARTED
   AS TCP (LISTENER_PORT = 5024)
   FOR database_mirroring (ROLE = PARTNER);

5. Link mirror server from Srv1\M1

   
         ALTER Db
         SET PARTNER =’TCP://Srv2:5024′
       

6. Link principal server from Srv2\M2

         ALTER Db
         SET PARTNER =’TCP://Srv1:5023′
       

What is the result of the previous procedure?

By Honza Mensdorff

Answer: Error

Explanation: The procedure lacks the backup and restore of the transaction log. Without it the mirroring session cannot be established.

Ref: http://msdn.microsoft.com/en-us/library/ms190941.aspx
http://msdn.microsoft.com/en-us/library/ms175883.aspx

» Discuss this question and answer on the forums

SQL Server 2012 Integration Services Design Patterns

SQL Server 2012 Integration Services Design Patterns is a book of recipes for SQL Server Integration Services (SSIS). Design patterns in the book show how to solve common problems encountered when developing data integration solutions. Because you do not have to build the code from scratch each time, using design patterns improves your efficiency as an SSIS developer. In SSIS Design Patterns, we take you through several of these snippets in detail, providing the technical details of the resolution.

Get your copy from Amazon today

Report Server Report Usage

Dennis Post from SQLServerCentral.com

Get information about how often, how fast your reports run and how big they are. More »

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2005 : Administering

How to notify when SQL Server Agent stopped automatically? - Hello all, I have many jobs running and for some reason sql server agents stops automatically not sure why..Can someone tell...

URGENT: CPU 100% SQLSERVR.EXE but no high CPU process - This morning on our production SQL 2005 9.0.3353 box it is running at 100% CPU. I have looked at sysprocessses but...

Database list from multiple SQL servers and instances??? - Hi I'm currently looking for a script to get a list of all databases from our SQL 2005 and above...

Find out why a maint plan failed - Hi all, a maintence plan failed last night and im trying to find out why. now apart from the fact that...

get total disk space per instance from within SQL without cmdshell. - For policy reasons I can't enable cmdshell. I can get disk free space via xp_fixeddrives. But is it possible to get...

SQL Server 2005 : Backups

Determining a database's completed restore date and time... - I am familiar with the various queries that can be run to return information out of the 'msdb' database for...

SQL Server 2005 : SQL Server 2005 General Discussion

can any one explain how this query execute - CREATE TABLE department(dept_no CHAR(4) NOT NULL, 3> dept_name CHAR(25) NOT NULL, 4> location CHAR(30) NULL) 5> 6> insert into department values ('d1', 'developer', 'Dallas') 7>...

Failed to notify ''operator name'' via email? - Following command sends email fine: EXECUTE msdb.dbo.sp_notify_operator @name=N'operator name',@body=N'test message' However, SQL Job with a notification to above operator doesn't work. Job history...

SQL Server 2005 : SQL Server 2005 Strategies

How to notify through DB MAIL when sql server agent stopped automatically? - Hello all, Can someone tell me how can we send email when my sql sevrer agent stops automatically, i have my...

SQL Server 2005 : SS2K5 Replication

Merge replication - some deletes not replicated - I'm currently testing a merge replication setup and I've discovered a problem with it. I ran a delete statement on...

SQL Server 2005 : SQL Server Express

Does Installing SSMS stop the database - Hi, I've got a SQL Server Express 2005 installed on a server for vmware. The install didn't come with SQL Server...

SQL Server 2005 : SQL Server 2005 Integration Services

Demanding and difficult logic qns. Sorry but i need help. - Hi pros out there, I am introduced to SSIS to do ETL for repporting in SSRS very recently. I am given...

Confused about FlatFile Connection Manager Error - I'm getting this error on my package "A valid file name must be selected". Here is why this confusing to...

AS400 to SQL Server using SSIS - I have data in the AS400 file which should be extracted. The file would be available on the AS400 or...

SQL Server 2005 : SQL Server Newbies

Split input string into multicolumn - multirows - Hello all - I am having hard time to split an input string into multicolumn - multirows. Task - Create a stored procedure that...

Column Headings help - Apologies if I am in the wrong part of the forum, I'm new, and newbies seemed a good place to...

Quickest Way to Find an Index - Hi Folks can comeone tell me the quickest way to find an index in some 100 plus databases ? is there a...

SQL Server 7,2000 : Administration

SQL Server on a virtual server - Hi, I'm collecting information, recommendations about advantages and disadvantages of usage a virtual server for a [b] SQL Server in production[/b]...

SQL Server 7,2000 : T-SQL

Select GROUP BY QUERY - I have a table like: CREATE TABLE [dbo].[tbl_FaceBookScore]( [ROWGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL, [Quiz_No] [bigint] NULL, [Mobile_No] [varchar](20) NULL, [Score] [nchar](10) NULL, [TotalTime] [bigint] NULL, [TotalQuestion] [int]...

SQL Server 2008 : SQL Server 2008 - General

delete top(8000) takes 1 second, delete top(9000) takes 42 seconds !! - Hi, Delete top(8000) takes 1 second, but delete top(9000) takes 42 seconds, any ideas why? In management studio, if I do the...

query comp - hi friends i have a small doubt in sql plz tell me how to find it which query give more...

Sql Query to display records in all tables - Hi Team, I need a query to display all the records from all tables in a database or export records tables wise...

SSIS mail task error - Hi, I am getting the following error with my ssis mail task. is it caused by McAfee protection? if so how...

Timeout Expired Error. - Hi, I have recntly migrated my production server from sql server 2008 to sql server 2012, (Side by Side) migration. And...

How to find last database access date/time? - Friends, Would need your help for the situation below: I have a development server with many DB instances. I would like to...

Cursor to generate Stored procedures Script - Hi Team, is it possible to get all stored procedures script using a cursor. Can u please provide the query

SQL 2008 User List - Dear Gurus I have find following users in my Database as a global users. These users are under Instance --> Security...

Inconsistent Reads after tuning the query - Hi All, I am very new to Performance tuning hence any help provided will be appreciated. I hav a batch query which...

help pls.. - Women Art Revolution (2010) "!Next?" (1994) "#1 Single" (2006) "#ByMySide" (2012) "#Follow" (2011) "#nitTWITS" (2011) "$#*! My Dad Says" (2010) "$1,000,000 Chance of a Lifetime" (1986) "$100 Makeover"...

Find Failed Jobs - Hi all, Does anyone have any scripts to find a list of all the failed jobs in the last 10 hours. I...

Optimization advice for huge tables - Hello, I need tuning advices for large tables query performance. I have 3 big tables and I need to join these 3...

Find non-overlapping time per day for staff productivity report... - Hello! I am trying to determine the amount of direct service time *staff* spent during a given work day. Table...

Divide by zero from implicate conversion - I have a select statement where I am doing some basic math with a datediff function. I have looked at...

CASE statement in WHERE clause - I am sending a parameter @Finclass and @FinclassCategory to a sp. I want to use the WHERE clause based on the...

sum on fields between 2 dates - I want to be able to add together values from fields that come between 2 dates, I've created a table...

How to solve blocking issue - after finding blocking spid. How to solve that problem. deadlock and blocking both are same are different.

Changing from osql to sqlcmd - Hi All, I have one use of osql in my system and I want to change it to sqlcmd. Here's an...

SSIS drop table on spreadsheet - Hi There pls assist. I am trying to create a package that will query sql server and return results to excel,...

MERGE for INSERT alone - Hi Friends, We are in the process of Migrating data from one DB to another DB. The tables in the source...

Problem in Configuring Mirroring - Dear, I am trying to configure mirroring. Principal server is [b]Serv1[/b] and Mirror server is [b]Serv2[/b]. Both servers are running SQL...

Memory usage - HI In our server when we have time out in select & insert we have lack of memory. the total memory is 24G,and...

Problem In ssis Job creation - when i am trying to create a job to execute an ssis package then while trying to search for configuration...

XML format issues - Hi, [code="sql"] DECLARE @t TABLE (cn VARCHAR(50), cq VARCHAR(50), vq VARCHAR(50), td VARCHAR(50), lo VARCHAR(50), im VARCHAR(50) ) INSERT INTO @t SELECT...

Alternative to xp_cmdshell - i need to delete some backup files as per daily operation through SQL ,currently i am using xp_cmdshell ,is there...

Running Total - Hi all I have a table with columns in that I want to do a running total on. I want...

SQL Server 2008 : T-SQL (SS2K8)

Sql Server 2008: Conversion From Text data type to Datetime - Hi All, How do I convert a text data type column to datetime? The query below returns this error: Explicit conversion...

What is key and non-key column wrt to indexes - Hi, Since this might be the most basic question hence i did not find its answer anywhere. Could anybody tell me...

Filter rows based on two columns - Hi there, Apologies for the elusive subject title. I'm trying to filter only rows on one field only when another field...

Making groups using a separator line - Hello there, I have a little challenge that makes me thinking for hours now. I have a list of data and...

identifying records in the same table - Hi everyone I have a table in SQL server that contains the following : ID, Date, Description, Type, Manager and Site....

SQL PROCEDURE TO FIND THE LIST OF JOB FAILURES FOR CURRENT HOUR - Hi Friends, I need a procedure which should capture the job failure. This SP should capture the below elements: 1. Server...

4 functions, 3 functions...2 & 1 is possible? - create table emp ( last_name varchar(50) ) insert into emp select abc_worldbaank union select xyzabc_countrybank union select yyybb_districtbank union select zzzaaa_internationalbank my requirement is to display the text after '_' worldbaank countrybank districkbank internationalbank I used two...

How To Generate XML File....From the Table... - Hi, I Successfully Imported the Following XML File ... Again I Want to Export (Generate) the Same XML File Format... Using The Output...

How To Export The XML File From The T-SQL Table.... - Hi, I Successfully Imported the Following XML File ... ------------------------------------------------------------------------------------- [code="sql"] DECLARE @idoc int DECLARE @doc xml DECLARE @xmldata Table (FirstName varchar(110), LastName varchar(210), Email ...

sads - sorry Dont Consider it

SP Help... - Below is the SP that i am using in different SP to Populate table ([tVDetail]) that has more than 15...

Trend Rolling Table - Development has created 7 day rolling tables on multiple servers. I want to pull this data over to another table...

error in stored procedure - ALTER PROCEDURE [dbo].[usp_delete] @tablename sysname, @pid int ,@pidname varchar(10) AS DECLARE @SQL varchar(500) SET @SQL = 'delete from ' + @tablename + ' where '+ @pidname + ' = '+ @pid [b]Conversion failed...

Dynamic SQL - Hi, has anyone got an example of a Dynamic SQL and what is it and why it would benefit using this? Thanks

SQL Server 2008 : SQL Server Newbies

Receiving an error with replication - Hey Guys, I'm running SQL server 2008 R2. The Programming department wants to have a database replicate to a different...

Need help getting totals by ZipCode - Hi all, I am trying to get grand total, totals of each ethnicity and percentage of each ethnicity by zipcode. I can...

Precendence Constraint Logical AND/OR Confusion - Hi All, I am wet behind the ears with all things SQL and I'm currently just beginning to study for my...

SQL Server 2008 : Security (SS2K8)

Login Auditing - I have been asked to provide documentation on where SQL Server displays messages for login auditing. We set the auditing...

Vulnerability in Windows Common Controls Could Allow Remote Code Execution - MS12-060, KB2687441. http://technet.microsoft.com/en-us/security/bulletin/ms12-060 http://support.microsoft.com/kb/2687441#AppliesToProducts I am researching applying this patch and wanted to see if others have installed this. When reading...

how to create a sql login and add to database user which already exists - hi everyone , how to create a sql login and add to database user which already exists 1. user "appp" is...

SQL Server 2008 : SQL Server 2008 High Availability

2nd Live Copy - Hi All. I have a set of databases on a server in a facility in another city. We do our...

transactional Replication- Two log files - For some reason we have two log files on one of our production database. We have transactional replication setup on...

Dear All this post is about logshipping DC- DR Drill [switch over and switch back (or)failover and failback ] - Iam a starter in sql server dba handling 3.5 to 4 yrs wrk i too googled and taken feedback from so...

SQL Server 2008 : SQL Server 2008 Administration

Failed to map 8388608 bytes of contiguous memory - My customer is running a small e-commerce site on a SQL Server 2008 R2 SP2 Workgroup Edition x64. The SQL...

Strange blocking issue - Hi Experts, I came across a blocking issue in which the below two process are involved the second statement is blocked...

convert a coloumn datatype ntext to varchar - How to convert a coloumn datatype ntext to varchar . The table have around 1 lack rows.

Deadlock due to intra-query parallelism - Hi We have been having deadlocks a lot and to track them I enabled Trace Flags 1204 and 1222. The output...

DB Mail issues with DLL - Dear All, I have been facing the problem when I am sending mail , in example in Test mail I am getting...

Grant (permission) on database:: ... where can I see these? - If run the following statement: [code] use someDB; grant select on database::someDB to someuser; [/code] Is there anywhere I can see this permission using the...

Executing Insert statements in a batch / group - Dear All, Hi! I need to generate Insert scripts of more than 1 million records & execute the same on another...

Discrepancy Between sys.dm_os_performance_counters and DBCC SQLPERF(logspace) - Background - A deployment gone bad. Deployment caused the transaction log to grow to the point where I needed to add...

SQL Server index fragmentation is high after rebuilt - I have few indexes in my db, which report high fragmentation even after we rebuild indexes. The page count on...

SQL Server 2008 R2 SP1 Install failed - Hi for some reason my install of SP1 crashed and I cant re-run the installation again. it gives me error...

Career : Certification

Microsoft Certified Master - There's been a lot of debate recently about whether certifications are worth doing and whether they provide you or your...

Programming : General

Sending Emails From SQL Server - Okay, so i have a query that i run several times a day. After running the query i then analyse...

SQLServerCentral.com : Anything that is NOT about SQL!

Fantasy football 2012 - Only 2.5 short months to football, can you believe it? I'm not ready, hardly feels like it's summer time yet. You...

Today's Random Word! - HI When you woke up today, or logged-onto Opera Forums, you may have had a dream, a thought, a scene...

Are the posted questions getting worse? - Is it me, or are the posted questions getting worse these days? I just read a post by someone apparently in...

SQLServerCentral.com : SQLServerCentral.com Website Issues

Anyone having trouble browsing the site on a Surface with Windows RT? - I am using a Surface and am having trouble browsing. I have three issues actually: 1. Notification emails sent to my...

Reporting Services : Reporting Services

no calendar icon on RS 2008 date parameters - Hi, I'm having a problem on one of our clients PC. Seems like the calendar icon on the date parameter is [b]not...

Data Warehousing : Integration Services

EDI x12 output from ssis - Hi, I have a requirememnt converting few pervasive maps to SSIS packages. We have couple of maps that has EDI x12 (837)...

String Expression 4000 characters limit - Hi all, I have a query more than 4000 characters limit .I am setting with variables to the query.while i...

Error trying to run an SSIS Package - Hi Forum I getting the following error: [b]SSIS Error Code DTA_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit vesion...

Data Warehousing : Strategies and Ideas

Item/Foreign Currency Question - Each item has a currency (USD, AUD, CAD, etc.) and a currency amount. Each currency amount has a starting effective...

Data Warehousing : Analysis Services

mdx tail function not working on currentmember - Hi All mdx gurus, I was checking out the tail function to achieve same the rolling 3 month sales which works...