Disaster Recovery Week

Steve Jones from SQLServerCentral.com

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

Departures from Origins and Arrivals at Destinations

Dwain Camps from SQLServerCentral.com

A suggested design for creating flight schedules makes querying easy. Includes basic airport and airlines data. Get started now with this tricky query problem. 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 »

SQL Server Backup Questions We Were Too Shy to Ask

Additional Articles from SimpleTalk

During presentations about doing database backups and restores, there seem to be two two types of questions that are commonly asked, Those that come from the floor during the presentation, and those that are asked in private afterwards. These are sometimes more interesting, and challenging to answer well. More »

Reporting Services Disaster Recovery

Dave Lumley from SQLServerCentral.com

Dave Lumley presents a Reporting services disaster recovery solution for SQL Server Standard Edition, using 2 servers. Worth the read if you don't run Enterprise. More »

From the SQLServerCentral Blogs - T-SQL: CASE Statement

Jen McCown from SQLServerCentral.com

For the month of December, I’ll be taking a little holiday from blogging. In the meantime, enjoy a few of... More »

I was talking with someone the other night about their database systems and they mentioned they had implemented TDE (Transparent Data Encryption) to comply with HIPAA regulations. This person had verified that they were backing up the Database Encryption Key, which you definitely need if you want to restore a backup from a TDE encrypted database. However they weren't sure if the certificate that protected the DEK, and the master keys on that instance were being backed up. Probably most scary to me, they hadn't tested any restores of the database.

Encryption is serious business, and if you are going to implement it in your databases, you had better be sure you understand how the various keys and certificates work. You better be sure you have protected your passwords, and that you can find them in the event of some issue.

Most importantly, though, is that you need to practice recovering your database to another instance. Preferably you'd learn how to recover on an instance that hasn't ever enabled encryption as well as one that has a different SMK or DMK.

Practicing restores isn't just about encryption and the potential for data loss because you don't have a key. Practicing restores is important for all of your systems to be sure you have the skills to successfully complete a restore. It helps ensure you know where the files, tapes, disks, or any other resources are located. Most importantly it ensures that your backup process is actually running smoothly.

Please don't assume your backup process works. Whether you're an accidental DBA stuck with their first SQL Server, or a ten year senior DBA that has performed hundreds of restores at previous jobs. You need to test your process and ensure that you can perform restores on the systems you are managing.

Steve Jones

The Voice of the DBA Podcasts

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

• Watch the Windows Media Podcast - 14.1MB WMV
• Watch the iPod Video Podcast - 18.2MB MP4
• Listen to the MP3 Audio Podcast - 3.6MB 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:

Steve Jones from SQLServerCentral.com

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

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

Today's Question:

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

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

This question is worth 2 points in this category: Administration. 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.

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

I have the following T-SQL

DECLARE @str VARCHAR(6) = 'ServeR'

DECLARE @stx VARCHAR(4) 

I execute the follow T-SQL:

-- S-1
SELECT STUFF(@str, 1,6,@stx) AS 'S-1' 

-- S-2
SELECT STUFF(@str, 0,5,@stx) AS 'S-2'

What is returned by the T-SQL statements (S-1 and S -2)?

By bitbucket

Answer:

• S-1 returns a zero length string
• S-2 returns a Null

Explanation: If the start position is 0, a null value is returned.

Ref: http://msdn.microsoft.com/en-us/library/ms188043.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

Find Single use plans in Plan Cache

SQLSACT from SQLServerCentral.com

This script will return all the Adhoc Single use Plans currently in your Plan Cache. It also excludes Plan Shells created from auto/forced parameterization. Works for 2005/2008  More »

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

SQL Server 2005 : Administering

MS12-070 & scaled out SSRS - I have a scaled out deployment of Reporting Services, which end does this update need to be applied? DB, front-end...

what is the use of dbo.dtproperties, can this be deleted? - Hello, One of our customer is running a desktop utility that compares DB structure to the strucuture included in the application....

shrink fails with error - File ID of database ID cannot be shrunk as it is either being shrunk by another process or is empty - This is an fyi (no response necessary). I got this error and researched it and found no good answer for how...

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 : Data Corruption

Stand by/read only database on SUSPECT - Hi all, I had the issue twice back to June, and Oct this year. Our environment uses log shipping between two...

checkdb error on index ID 0 - I need help on fixing checkdb error. Here's the output of checkdb: Server: Msg 8909, Level 16, State 1, Line 1 Table error:...

SQL Server 2005 : Development

how to calculate row1 - row2 - row3.... - I have a table which has some n no of rows. The table has Key, value1, value2, value3, etc fields....

SQL Server 2005 : SQL Server 2005 General Discussion

Auto Save / Auto recover in Management Studio? - I have come in today to find my PC rebooted, I suspect due to windows updates, (it decides to reboot...

SQL Server 2005 : SS2K5 Replication

Merge Replication - How to start between Live databases without Drop object for FK - Hi, Just learned that SQL2012 do not support "transaction replication with update-able subscriber", But i believe the issue with merge 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

detach multiple databases using sp_detach_db - All I'm trying to detach multiple databases with the sp_detach_db. All the databases (about 20 of them) have a similar...

SQL Server 2005 : SQL Server 2005 Performance Tuning

How to check when was the last Time re-indexing was performed on a DB - hello All, Can someone suggest me a scrpit or a command that can provide me information about last time re-indexing performed...

SQL Server 2005 : SQL Server 2005 Integration Services

Data conversion in SSIS - Hi everyone, I am newbie to SSIS, just picked that up ytd. Currently i am given a sample view table to...

SQL Server 2005 : T-SQL (SS2K5)

Email attachment script ? - I have a sql table that contains 100 email addresses & a word document that needs to be sent to these...

Time covered in a day for staff productivity - OOPS! I just saw I posted this in the wrong Topic Folde. Please forgive the newbie! I'll re-post in the...

Missing Number Find out - Hi I am doing University Project.In my scenario I have two tables.One table name called studentmaster otherone examapplication. in following...

SQL Server 2005 : SQL Server Newbies

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

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 : Backups

not part of a multiple family media set. - Because we have not had a good backup for a few days, I am trying to do a backup to...

SQL Server 7,2000 : Performance Tuning

SQL Profiler Reads vs Set Statistics IO logical reads - I created a table with a single varchar(10) column and ran a select statement against it. In Profiler, it shows...

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]...

Search specific string in data in all tables - Hi Experts, Is there a way to find specific string in data in all the user tables, not column names or...

SQL Server 2008 : SQL Server 2008 - General

where to find the {script/stored procedure} in SQL SERVER DATAWAREHOUSE which populated DimTime table - Hi Friends, I need your suggestion. We have a SQL SERVER 2008R2 dataware house. In this we have a table "DimTime". This...

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...

Remote Query Speed Issues: - Any query run against the server remotely takes a ridiculous amount of time and I am not sure where to...

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

Which backup will work why? - Here i have one doubt. 1) here we are scheduled the backups full, differential,transactional log with same time and date. here which...

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...

SQL SERVER 2012 CLUSTERING - Dear Team , I have littil bit confusion : Sql server 2012(Standard edition) and windows server 2012(Standard edition) will support AlwaysOn High...

Last Modified Dates on Database & Tables? - Hello, I'm sure this is an easy one for all you knowledgeable people out there. How can I find the modification date...

Sorting - Custom Based. - Hi Team, Is it possible to sort records in custom. Eg: i've below records in a table. john Peter smith Ellen Jack David i want in below order Smith Jack...

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...

Which system trigger fires? - When system trigger fires when we use below code sp_configure 'show advanced options',1 GO RECONFIGURE GO i need to write insted of triggers on...

Log backup question - Hello, My transaction log backup today took longer time to complete than usual. Daily it runs at 1:00PM for about 2...

SSIS Derived Column question - I'm moving data from a text file to a table using SSIS. Below is an example of my data and how...

Continually running SQL Trace - Hi, I am planning to run a server side trace on server. I am bit worried about the trace file growing...

Appropriate range for partitioning - Hi I have a large table which there is just select and insert on it.It has a field named 'TranType'(int)...

sign of corrupt database? - When I manually run this insert script below I'm usually expecting to see (2 lines due to a trigger that...

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...

First Post with a very basic query request - Hello all but i'm trying to make a very basic query and i'll give an example of the db here...

SSIS packege run good on BIDS, but when set up to run in SQL Agent job, always fail duw to access - Hello: Here is my problem, Create a new project in BIDS, new package, go into Control Flow, Create an execute SQL...

Distributing the greatest value by rank in SQL - This seems as though it would be terribly simple...but I am stuck. I need to distribute as "average sales" value among...

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...

Permission problem on the database 'mssqlsystemresource' (Microsoft SQL Server, Error: 229) - Hi all, I recently backed up a SQL 2000 (SP4) database and restored it on to a new server 64-bit Windows...

Performance - Hi, I am developing one tool. I need to write to stored procedure for each and every functionality. Should i write single...

TSQL question (join tables) - Dear all, I have a question concerning a view UI need to create. Let's me explain the situation. I have 5 tables Reports...

How to rename a SQL Server 2005 Failover Clustered instance - Hi , I neew to know how to rename a SQL Server 2005 Failover Clustered instance. Thanks for all your assitance. Thanks

SSRS 2008 R2 page break after every 4th person in group - Hi Everyone, I need to add a page after the 4th Patient in a report. The report has two groups. Organization and...

SQL Server 2008 : T-SQL (SS2K8)

How to use NOT IN or Not Exist custom in Datetime? - i use 2 table, i want first table not data in table two of show on to database. [b]Ex. "filesTA"[/b] [quote]EmpNo | ChkDate 00001...

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...

Group By with Case - I'm trying to query for Group By with a Case. I want a total per Date but there are some Departments...

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 find out who updated/Deleted on SQL Server - Hi, How to find out who deleted or updated the values in table in SQL Server. This scenario may occur when...

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

Case Statement for Where Clause - Good afternoon everyone! I'm not a regular developer so please excuse my question for being easy for what would be a...

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

How to Solve the issue ... while Importing XML File... - Hi , I am Try to Execute the bellow Mentioned Query ... but i am getting the only one row... I want to...

Update Gaps - I'm trying to update gaps in a table using a set based approach, but have been struggling with the solution....

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

An alternative to split for performance benefits? - Guys, I'm wondering if you can help me think of a different idea/way of tacking something which will produce faster results......

Interview Questions - Hi..I had an interview and had few questions: Can anyone please help me finding out answers for the below questions? 1) Design...

Straight & Reverse Check - kcehc eserveR & Straight - All, I have two table as below. [code="sql"]create table currency ( currencypair varchar(30) not null, rate_value numeric(18,6) null ) insert into currency select 'BNGCZK',null union select 'CHFCLP',null union select 'CHFLKR',null union select 'CHFPEN',null union select 'CHFSIT',null union select...

SQL Server 2008 : SQL Server Newbies

Which table join should I use - I've got 2 tables Order & Despatch and I'm trying to add some keys to the tables. The OrderNo field is...

Trigger set based check - Hello, I have a question about creating a trigger that works set based. I have no clue how I can check...

Blocking Situation - Hi All I was dealing with an issue that I encountered a few days ago I was doing my regular Index rebuilds...

DB gone offline - Hello Masters, What are the reasons for any DB gone Offline ? How could I know at what time and who and...

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...

High availability best practice? - I have a vendor supplied database driven application, I need to place a copy in another city for DR purposes,...

SQL Server 2008 : SQL Server 2008 Administration

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...

replication latency - i have 2 servers, which have transactional replication between them,connected physically between them with a cross cable. the subscriber pull's the...

Slow Full backup in SQL SERVER 2008 R2 - Hi every one, We have database(4.6TB) in SQL SERVER 2008 R2.On weekly bases we take full backup from this database.It used...

network monitor - Hi, Is there a third party tool (Free or paid) which allows me to monitor the activity of the network at...

Job to query multiple instances at once - Like most environments, we have a bunch of SQL servers. I would like to set up some jobs to query...

RML files - Hi Experts, Anyone have any idea what a .RML file is i can hundreds of file in hundreds of folder in...

move db files to another directory - I would like to move database files to another drive. I used sql like below: ALTER DATABASE Z_20090501_coreSPSS MODIFY FILE ( NAME = coreSPSS,...

2008 R2 SP1 Error: Given key was not present in the dictionary - When I tried to upgrade to SP1, I'm getting an error "Given key was not present in the dictionary." Any ideas...

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 : Connecting

connection sql server 2000 - hello, someone can help me, please when i try to execute a sql script with a link server i got this...

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...

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

Adding a detail row when none exist - I initially thought I would not need detail rows in my VS 2008 BIDS report, and deleted them while keeping...

Reporting Services : Reporting Services 2005 Development

Running Total (sum according to a criteria) - I'm trying to summarize the Total amount according to the type of paycode (IncomeCode, BenefitCode).. The IncomeCodes starts with 1...

Database Design : Design Ideas and Questions

SEQUENCE Problems - Hello. I've started using a SEQUENCE in a table instead of an identity. I seem to be experiencing problems of the sequence...

Database Design : Hardware

Lots of Memory or Fusion I/O - My first post so hello to everyone for the first time :) I work for a company who make a large...

Data Warehousing : Integration Services

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...

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

Date Dimension permissions for MS excel 2010 - Hi Customer has data from 2002 to current. However they want to give the personnel a focused Analytic db of...