SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

Featured Script

The Voice of the DBA

The Need for Tape

This editorial was originally published on Mar 26, 2009. It is being re-run as Steve is away at DevConnections.

One of our SQLServerCentral authors sent me this post, which I found to be pretty amazing. Apparently hackers broke into a backup server at WebHostingTalk, which is a community for hosting providers to discuss issues. They deleted backups first, and then moved on to deleting tables from the database.  It resulted in some downtime, and then a restore of an old copy of their database prior to trying to restore more recent copies. More information from the admin is here.

Now that's just malicious and nasty.

Hopefully this wasn't an inside job, though I could see that as a possibility. For a service like this, that just provides a place for a community, let's people interact and talk, this is just vandalism. It serves no purpose. It's likely no one even knows it was "M@M@sB0Y" or some other hacker, so there's no fame, and it disrupts people who just want to converse with colleagues.

I'd hate to think about this happening here. We have lots, and lots of posts from people all over the world, and while it wouldn't kill us, it would really annoy many people that have volunteered their valuable time to help others by losing their work. We definitely need to make sure we don't have an issue here.

I see two takeaways from this incident for most DBAs and administrators. First be sure that your backup servers are just as secure as your production ones. There is production data in the forum of backup files here, and you should be providing as much security for them, and perhaps more, as on other servers. Don’t treat these servers lightly.

Second I think this shows that there's a need for tape backups, or some type of non-linked backup. A tape grabs the files from your server, completely separately from the SQL Server (or other application). It is a pull link, and it's not obvious from the source server where these files have gone. That's good in that it prevents some type of attack on the main server from propagating on. Most people use a push from their server to a remote device as part of the backup process. That's OK, but it provides an easy link for someone to attack the backup server from the main one.

And one more benefit of tape? Usually they're rotated out, so even if a hacker or insider knows how to get to the backup system, without physical access they can't touch your tape.

Steve Jones


The Voice of the DBA Podcasts

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.

You can also follow Steve Jones on Twitter:

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.

I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.

Steve Jones from SQLServerCentral.com

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

ADVERTISEMENT
SQL DBA Bundle

SQL DBA Bundle

The seven tools in the SQL DBA Bundle support your core SQL Server database administration tasks.

Download a free trial now.

SQL Developer Bundle

SQL Developer Bundle

The SQL Developer Bundle can transform the way you and your team collaborate. Countless happy developers. One award-winning bundle.

Download your free trial now.

SQL Monitor

SQL Monitor

SQL Monitor checks performance data every 15 seconds, so you can fix issues before your users even notice them.

Start monitoring with a free trial.

Featured Contents

 

Database Backup and Recovery in the Age of Big Data

Additional Articles from Database Journal

Big data is the latest craze. Hardware and software vendors have overwhelmed IT departments with high-speed analytical software, proprietary high-performance hardware, and columnar-based data stores promising quick access and lightning-fast answers to ad hoc analytical queries. Forgotten in this blast of technology are the database administrators' most important responsibilities: backup and recovery. More »


 

Data Mining Introduction Part 7: Microsoft Association

Daniel Calbimonte from SQLServerCentral.com

This article is part of the Data Mining introduction series. This article is about the Microsoft Association algorithm. More »


 

SQL in the City Atlanta 2013

Press Release from Red-Gate

SQL in the City is coming to Atlanta on October 11. The second stop on the SQL in the City US Tour brings you SQL Server MVP experts including, Steve Jones and Grant Fritchey. Learn top tips and best practices for SQL Server database development and administration, discover the latest Red Gate tools, and network with fellow data professionals. More »


 

SQL Monitor Performance Metric: SQL Server: Plan Cache: Cache Pages Total

Additional Articles from Red-Gate

This metric collects the total amount of memory, in kilobytes, used by the plan cache of an instance to help identify memory pressure or plan cache pollution. It is similar to the SQL Server: memory manager: SQL cache memory counter, but instead of providing the number of 8-kilobyte pages that make up the plan cache, it provides the total memory used. More »


 

From the SQLServerCentral Blogs - Detaching a database

Kenneth Fisher from SQLServerCentral Blogs

In my general quest to figure out the T-SQL to do common GUI tasks I did some browsing through BOL... More »

Question of the Day

Today's Question (by Dave):

Evaluate this statement: after repeated warnings since SQL Server 2005, the extended stored procedure application programming interface feature has finally been removed from SQL Server 2014.

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


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

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

ADVERTISEMENT

Training Kit (Exam 70-461): Querying Microsoft SQL Server 2012

Ace your preparation for Microsoft® Certification Exam 70-461 with this 2-in-1 Training Kit from Microsoft Press®. Work at your own pace through a series of lessons and practical exercises, and then assess your skills with practice tests on CD—featuring multiple, customizable testing options.

Maximize your performance on the exam by learning how to:

  • Create database objects
  • Work with data
  • Modify data
  • Troubleshoot and optimize queries

You also get an exam discount voucher—making this book an exceptional value and a great career investment.

Yesterday's Question of the Day

Yesterday's Question (by sqlnaive):

We have created following table:

CREATE TABLE dbo.T_EMPLOYEE
( EmpNo VARCHAR(20) NOT NULL PRIMARY KEY, 
  EmpName VARCHAR(50), 
  EmpContact VARCHAR(20), 
  EmpManager VARCHAR(20)
 FOREIGN KEY REFERENCES dbo.T_EMPLOYEE(EmpNo));

Here is the data populated:

INSERT INTO dbo.T_EMPLOYEE SELECT 'E1001', 'Steve Smith', '+1-723-2311', NULL
INSERT INTO dbo.T_EMPLOYEE SELECT 'E1002', 'Raj Shekhar', '+1-723-2145', 'E1001'
INSERT INTO dbo.T_EMPLOYEE SELECT 'E1003', 'Kelly Brook', '+1-723-1648', 'E1001'
INSERT INTO dbo.T_EMPLOYEE SELECT 'E1004', 'Sam Samson', '+1-724-2386', 'E1002'
INSERT INTO dbo.T_EMPLOYEE SELECT 'E1005', 'Keith Hunt', '+1-724-5689', 'E1003'

Now I try the following command:

TRUNCATE TABLE dbo.T_EMPLOYEE

What will be the result?

Answer: Command(s) completed successfully.

Explanation:

You cannot use TRUNCATE TABLE on tables that are referenced by a FOREIGN KEY constraint UNLESS that table has a foreign key that references itself. Check the BOL link: http://technet.microsoft.com/en-us/library/ms177570.aspx


» Discuss this question and answer on the forums

Featured Script

Permission scripting over all databases

W Louw from SQLServerCentral.com

This stored procedure is helpful in scripting permissions for a user in all databases it exist in.

If @newuidname has a value of '', it will scroll all databases and search for its permissions.

If @newuidname has a valid username, it will only script out the permissions for @uidname where @newuidname does not already have the same permissions or roles assigned.

If @RolePermissions = 1 the permissions of the roles @uidname belong to will also be scripted.

I initially used sp_helprotect, but then I had to do it database by database individually, this way I can run it once for all. This sp can be compiled on any database and it will still go through all databases.

Hope it is useful.

Telammica

More »

Database Pros Who Need Your Help

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

SQL Server 2014 : Administration - SQL Server 2014

Pro Active DBA - Hi Guys, I've recently been appointed to maintain my companies internal DB's. Must also stress the point that the DB's are...


SQL Server 2014 : Development - SQL Server 2014

The special character changed to question mark in DB - Hi.. When i am trying to insert delta symbol into db, its converted to question mark(?) symbol. I am passing...

how to convert date in varchar format into datetime - Hello Everyone, I have table in which date is in varchar format i.e. '29/09/2013' now I want to insert the records in...

How check constaint works? - Hi All! I have added check constraint on table. I am calling one function in that check constraint. Actually I have to...


SQL Server 2012 : SQL 2012 - General

how to solve this - two tables table1 table2 StudentId studentid TestId teacherid QuestionId classid PointsAchieved PointsPossible Write a query that would return teachers who’s students had highest overall percent correct on...

Can I have SQL 2012 AND SQL Express 2008 R2 installed on the same machine? - I'm looking at removing the SQL Server 2008 R2 Developer Edition that I have installed on my machine (a Windows...

Output based on Mapping Table and Orders Table - I have two tables, 1st Table is the Order Table which has order no,old items, price and their quantity. 2nd Table is...

ssis vs jobs - I just started as dba at new co. I'm looking over what the previous guy did. He has some ETL...

Printed Books Vs E-Books - ;-)

Error in attaching Adventureworks 2012 database - hi i have problem in attaching Adventure works 2012 database Attach database failed for Server '..........\MSSQLSERVER2012'. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Serve

Sql Server 2012 can't see 2nd drive - I installed Sql Server 2012 Express. Everything went without a hitch until I attempted to create a new database and...


SQL Server 2012 : SQL Server 2012 - T-SQL

XML Level/Aliasing Help - Hi, I have this XML file. It's my first time trying to load one. It has three levels, and for some...

Searching for n-categories - Hi there I have a challenge where I need some help. May someone can help me? I have a table where a...

Trigger with RAISERROR - Hi, Why doesn't a RAISERROR inside an AFTER trigger "break" the transaction? Is there any flag or SET option to do...

how to update column city value from 'A' to 'B' and 'B' to 'A' in single query - hi, I have a table like id city 1 A so i want to update city column from A to B and again...


SQL Server 2008 : SQL Server 2008 - General

How to DEFINE REGEX for CSV FILE - Im transfering data from csv file and it is fixed format and only date changes. so i want to split...

Trouble with nested CTE - I need to create a query that is comprised of subqueries, I am using a CTE, but one of the...

NOdeA is crashed ..on ctive active cluster. - Hi, We have active active sql 2008 r2 running on Win 2008 r2 cluster. Instance_A is running on Node1 Instance_b is runing...

deleting all data\logins\views - What is the best way to remove all data/views/logons from a database ? The scenario is this: We have a database...

Error Message: BACKUP failed to complete the command BACKUP LOG msdb - Hi, I am getting following error message daily into my error log. I am running FULL Backup, Diff Backup an T-Log Backup. This...

Passing external sproc params to internal variables - Explanation? - Sometimes I find the reason behind a poorly performing stored procedure lies in passing the external parameters to internal variables...

How to run multiple stored procedures in parallel? - I have a stored procedure which runs in database DB1 This stored procedure will have other stored procedures inside it which...

Using CASE Condition for Prefixing a field from a table - Hi All, I want to retain the same Employee Code when an employee is rehired and prefix with A,B,C based on...

SQL Server Database Backup Monitoring - Hi SQL Masters, Greetings! Do you have a script that will determine if the backup failed/succeeded? Sample output: Database Name Status Log Date --------------- ---------- -------------- AdventureWorks2008...

TSQL Recomile Problems when using subqueries - Good Day All I have found a problem with a Simple TSQL script that is returning incorrect results due to a...

Convert DD MMM YYYY to yyyymmdd in string - Hi, I am struggling with changing the date format of [b]dd MMM YYYY[u][/u][/b] in the sample strings below [u]into [/u][b]yyymmdd[u][/u][/b] Sample Data...

Query to create log file for sql select query... - can you please tell me how to create a log file in sql server 2008

how to add a column to a table ? - How to edit table data in SQL Server Management Studio ? I right click on table > Edit top 200 data...

synch two tables - Guys, I have two tables one is users and the other is frap_users ,Both contains some common attributes which are required...

Need Help on Fastest Search Logic - Hi, I have two tables named "Table1" and "Table2". Table1 Details: id bigint, product_name nvarchar(1000),quantity int records count on Table1 : 25000( may increase in...

Prevent backups on C:\ drive? - Can anyone - off the top of their heads - think of a way of preventing backups being taken to the C:\...

data copy - I have a DEV database i.e DEVDB I also have a TEST database i.e TESTDB both are SQL SERVER 2008 I want to...

SSIS source file with time in the name - Hi everyone. I have been tasked with building some SSIS packages to load data from a csv into the database each...

listing of months for a particular year - Dear All, Hope you are doing fine. What am I trying to do is get a stock count for each item...

Rolling 3 month average cost help - Hi guys, I need some help calculating a 3 month avergae rolling cost. I have been trying to figure this out...

Send SQL Server Dashboard reports via database mail - Hi All, Is this is possible to enable a job, which sends us the dashboard reports of a server thru mail...

NUMA - In preparation for my MCITP exam tomorrow I have been reading through the books and just crossing the t's and...

SQL to list the headers in a table? - Does anyone know offhand the SQL to list all the headers in a specified table? Thank you!


SQL Server 2008 : T-SQL (SS2K8)

Using Dynamic SQL to build temp table...doesn't work? - I am trying to pass a column name as a parameter to build a #temp table combining data from two...

Query Help - Hello. I have one table that looks something like this.. 3022224 G 4980 65 3 3022224 U 4980 596 2 3022224 G 4980 67...

Compare delimited data - same data but in a different order - Hello All, I am currently working on comparing two versions of data for a comparison report. I have a delimited...

FILEPROPERTY 'SpaceUsed' what it returns - Can anyone explain the behavior of the query below. I was trying to write a query that can be run...

Usage of OR in WHERE clause - Apologies for a silly question, but I was taught in the past to avoid usage of OR in WHERE clause...

Error arthematic operations inside varchar @variable - Hi I'm getting conversion error in the messages while executing below query. Please help me understand this. [code="sql"]DECLARE @Linkedserver sysname = 'DataServerSeven'; DECLARE...

Update script - Hi, I a mtrying to create update staments including data for the table in this way but I am getting error...

Error converting data type nvarchar to numeric - Maybe I am a bit dense today since it is a Friday and I am looking towards the weekend.. But...

how to replace this cursor with set based solution? - I've got a cursor script that I want to replace with a set based solution because the cursor script takes...

Query Needed - Dear All Currently I am getting following result sets Registerno SubjectCode SubjectName Qno Marks 12402223 171906 Quality And Reliability Engineering 1 4 12402223 171906 Quality And Reliability...

diffrence between nolock with braces and without braces - Can someone explain me about the difference between nolock with braces and without braces nolock (nolock)


SQL Server 2008 : Security (SS2K8)

Determine *which* database an account failed to login to? - Is there a way to determine, when an account attempts to open a DB and fails, which DB it was...


SQL Server 2008 : SQL Server 2008 High Availability

Application Server Role - Hi, Can you tell me if the Application Server Role is required for SQL2008 in a cluster. The SQL install does not...


SQL Server 2008 : SQL Server 2008 Administration

Management Data Warehouse Purge - Does anyone know where the table core.source_info_internal gets the days_until_expiration value from. I have an MDW database which has grown to...

SWITCH statement: required indexes in target table? - hello, is there some information about which indexes have to be created on a table to which I want to switch...

Choosing replication type and preparing for it - Hi all, We have 15 servers (in different regions) and each need to work with subset of data and exchange modifications...

Calculating Memory usage - Hi, I wanted to calculate the Total SQL Server Memory usage using DMVs. From sql server 2008, I have below direct...

Monitoring a remote SQL Server - Can dbWarden be installed on a nonproduction SQL Server and be used to monitor a production server? I installed dbWarden...

DBCC CHECKTABLE not reporting errors - Hi, I've recently implemented a new DBCC INTEGRITY check process running a combination of CHECKDB (for the smaller databases) and CHECKTABLE...


SQL Server 2008 : SQL Server 2008 Performance Tuning

Need to set Max Memory? - One item you often see in "best practices" articles about SQL is the need to configure Maximum Memory at the...

Query Plans migrating from 2005 to 2008R2 - I'm migrating from a 2005 server to a 2008R2 Server on Windows 2012. I restore the backup to test on....


SQL Server 2005 : Administering

How can i find which tables are read/write intensive within sql server 2005 - How can i find which tables are read/write intensive within sql server 2005 Really would appreciate some help on this guys...

BLOCKING CAUSED BY REORGANIZE OF INDEX JOB IN SQL SERVER 2005 - hi all, need your help on this. i have a job which is reorganizing the index and with that i am having...

backup job failed - Hi , my full,dataintegrity,index optimization jobs failed with the below error Ö Executed as user: NT AUTHORITY\SYSTEM. Database '****' is already open and...

SQL SERVER 2005 STARTING PROBLEM - Hai Every One. I am using SQL Server-2005. When i am trying to start my SQL Server(MSSQLSERVER) it was not starting and...

Creating a Loop to Iterate Through a SQL Table and Change Default Database - Hello! I was wondering whether someone could help me with creating a loop to iterate through a table (sys.syslogins) and change...

What roles do I add to a new custom role, which will allow CRUD and execute on SP? - I want to create a new, custom role to my databases, to which I'll add users. This role will allow...

Time-out occurred while waiting for buffer latch type 3 for page - Getting this error message Time-out occurred while waiting for buffer latch type 3 for page (1:738192), database ID **. when trying to...

NUMA Node - Hi all, We have 16CPU servers. When i took a look at Errorlog log i have all this informational Message. Is...


SQL Server 2005 : Business Intelligence

branding a map report with my company logo - Using SQL server 2008 R2. I created an SSRS report that uses ESRI map to show customer distribution on the map...

Default Selection (Report) - Hello SQL Gurus I am currently working on a report that a default selection used to select customers. I use Visual...

SSRS 2008 R2. Print Reports In sequential order - Hi All, I have 2 reports with following layout [b][u]Sales Report[/u][/b] Agent1 State..........................sales amount-----------------Number of items.......................Average Sales VA................................$25000.....................................10.........................................$250....... CA................................$35000....................


SQL Server 2005 : Development

Invalid object name 'DDLTriggerTest..EventTableData'. - hi, when u tried to modify the procedure then it gives me error like this... [b]Procedure tr_ProcedureEvents, Line 6 Invalid object name...


SQL Server 2005 : SQL Server 2005 General Discussion

Need to shut down sql instances with out disturbing other instances... - As we have four instances in one physical server which is all are 2005 instances. In this one instance not...


SQL Server 2005 : SQL Server 2005 Integration Services

Removing noisi commas in CSV file (SSIS) - I am quite new on using SSIS dtsx on SQL server 2005. I have CSV files that have been cleansed...

same issue - i have the Same issue. Please provide solution thanks in advance.


SQL Server 2005 : T-SQL (SS2K5)

Need help with Charindex function.. - I am trying to capture an email address from the below message..whats the best possible way to do it .. DECLARE...

using case when condition in WHERE in SP - i have a SP with where condition as where id=@id and month=@month and Eid=@Eid wht i need is if @Eid=0 i...


Reporting Services : Reporting Services

SSRS report previewing error - Can you help figure out for the following error messages while previewing my SSRS report. What is to be done.? [rsParameterReference]...

SQL Book Questions - Couple of SQL realted Book Questions I am seeking a book on SSRS 2012 and am considering the recently published title...


Reporting Services : Reporting Services 2005 Development

3in x 2in size Label Printing report (not using number of column method) - I need to develop a report in SSRS 2005 that will facilitate printing of labels (3in x 2in size). I...

Change the parameters based on Main Parameter - Hi All, I've report that has 4 parameters, say ParA, ParB, ParC and ParD. ParA is the main parameter upon which other...


Reporting Services : Reporting Services 2008 Development

Parameter Get All Values - Hi I have created a parametered called 'Pay Via" From the dropdown box, the user can select: - Visa - MasterCard - PPA - Robs Amex -...

Using LIKE in drop down parameter - I have a report generated from a stored procedure. There is a code called "cip" with different lengths of code....


Programming : General

How to improve a aquery - Good morning, I am writing a query thaht bring data from 7 tables. In the first statge the data is stored in...


Programming : XML

Render XML report using XSL transform - Hi, I am upgrading reports from 2005 to 2008R2 and I noticed that two reports are failing on the new...


Data Warehousing : Integration Services

[Add Date from variable [2]] Error: An error occurred while attempting to perform a type cast. Getting error.. - Hello , I am getting below error when I am adding row into destination table using derived column. [Add PaymentDate_FirstOfMonth from variable...

SSIS should not be showing this error ? - I have an Execute SQL task which gives returns only one row with one column - a number. I set my...

How to test an ETL process ? - Is there a set of guidelines and tools which can be used for testing if your ETL process does its...

SSIS check if file was already loaded ? - I want to make a package to load some text files into a database table. Any suggestions on how I...

Question about dynamic OLE DB connections - Ok I have the following issue & scenario I want a ssis package that connects to a single configuration database server,on this...


Data Warehousing : Strategies and Ideas

Multiple Fact Table / Calculated Measure - Hi I have the following structure currently a fact With Order details in and multiple dimensions (customer, date, product etc) in...


Data Warehousing : Analysis Services

MDX to Get Customer Status in given date range - I have a fact table that stores customer statuses based on when they last chaged. e.g. Customer Status DateAttained Cust 1 Live...

How To Find OUt How Long It Took For A Cube To Process - Is there any way to find out how long a cube took to complete processing the last time the cube...

Update Dimension Member - I've been tasked with populating the description of all dimensions and measures in our analysis services cubes to then be...


SQLServerCentral.com : Anything that is NOT about SQL!

My Favourite Sql Server Book ?? - Sql Server 2008 internals By kalen delaney

Fantasy football 2013 - I renewed the league, you should be getting an email soon. At the moment, there are no open spots, but...

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


SQL Server 7,2000 : General

SQL 2000 xp_cmdshell bcp does not return data while xp_cmdshell DIR works fine - I have verified that the user has local admin privileges; execute on master and equote databases. The first Directory xp_cmdshell creates...


SQL Server 7,2000 : T-SQL

I need help Please :( - I have a table name Employee Logs table consist of EmpID, Empname,Logdatetime. I can generate the first in and last out by...

TempDB best practices - Doubt - Hello guys, I have two doubt: -- 1 script: SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio], cpu_count/hyperthread_ratio AS [Physical CPU Count],...

To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2013 Red Gate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com