T-SQL Programming Part 5 – Using the CASE Function #microsoft, #articles, #microsoft


#

T-SQL Programming Part 5 – Using the CASE Function

Have you ever wanted to replace a column value with a different value based on the original column value? Learn how, with the T-SQL CASE function.

The CASE function is a very useful T-SQL function. With this function you can replace a column value with a different value based on the original column value. An example of where this function might come in handy is where you have a table that contains a column named SexCode, where 0 stands for female, 1 for male, etc. and you want to return the value “female” when the column value is 0, or “male” when the column value is 1, etc. This article will discuss using the CASE function in a T-SQL SELECT statement.

The CASE function allows you to evaluate a column value on a row against multiple criteria, where each criterion might return a different value. The first criterion that evaluates to true will be the value returned by the CASE function. Microsoft SQL Server Books Online documents two different formats for the CASE function. The “Simple Format” looks like this:

And the “Searched Format” looks like this:

Where the “input_expression” is any valid Microsoft SQL Server expression, the “when_expression” is the value in which the input_expression is compared, the “result_expression ” is the value that will be return for the CASE statement if the “when_expression” evaluates to true, ” . n ” represents that multiple WHEN conditions can exist, the “else_result_expression ” is the value that will be returned if no “when_expression” evaluates to true and in the “Searched Format” the “Boolean_expression” is any Boolean express that when it evaluates to true will return the “result_expression”. Let me go through a couple of examples of each format to help you better understand how to use the CASE function in a SELECT statement.

For the first example let me show you how you would use the CASE function to display a description, instead of a column value that contains a code. I am going to use my earlier example that I described at the top of this article where I discussed displaying “female” or “male” instead of the SexCode. Here is my example T-SQL Code:

Here is the output from this T-SQL code:

This example shows the syntax in action for a CASE function using the “Simple Format”. As you can see the CASE function evaluates the PatientSexCode to determine if it is a 0, 1, or 2. If it is a 0, then “female” is displayed in the output for the “Patient Sex” column. If the PatientSexCode is 1, then “male” is display, or if PatientSexCode is 2 then “unknown” is displayed. Now if the PatientSexCode is anything other than a 0, 1 or 2 then the “ELSE” condition of the CASE function will be used and “Invalid PatientSexCode” will be displayed for the “Patient Sex” column.

Now the same logic could be written using a “Searched Format” for the CASE function. Here is what the SELECT statement would look like for the “Searched Format”:

Note the slight differences between the “Simple” and “Searched” formats. In the “Simple” format I specified the column name for which row values will be compared against the “when_expressions” ,where as in the “Searched” format each WHEN condition contains a Boolean expression that compares the PatientSexCode column against a code value.

Now the CASE function can be considerably more complex than the basic examples I have shown. Suppose you want to display a value that is based on two different columns values in a row. Here is an example that determines if a Product in the Northwind database is of type Tins or Bottles, and is not a discontinued item.

The output for the above command on my server displays the following:

As you can see I’m using a “Searched Format” for this CASE function call. Also, each WHEN clause contains two different conditions. One condition to determine the type (tins, or bottles) and another condition to determine if the product has been discontinued. If the QuantityPerUnit contains the string “Tins” and the Discontinue column value is 0 then the “Type of Availability” is set to “Tins”. If the QuantityPerUnit contains the string “Bottles” and the Discontinue column value is 0 then the “Type of Availability” is set to “Bottles”. For all other conditions, the “Type or Availability” is set to “Not Tins , Not Bottles , or is Discontinued.

The WHEN clauses in the CASE function are evaluated in order. The first WHEN clause that evaluates to “True” determines the value that is returned from the CASE function. Basically, multiple WHEN clauses evaluate to “True”, only the THEN value for the first WHEN clause that evaluates to “True” is used as the return value for the CASE function. Here is an example where multiple WHEN clauses are “True.”

The output on my machine for this query looks like this:

If you look at the raw titles table data in the pubs database for the title “You Can Combat Computer Stress!” you will note that the price for this book is $2.99. This price makes both the “price 12.00” and “price 3.00” conditions “True”. Since the conditions are evaluated one at a time, and the “price 12.00” is evaluated prior to the “price 3.00,” the “Price Category” for the title “You Can Combat Computer Stress!” is set to “Cheap”.

The CASE function can appear in different places within the SELECT statement, it does not have to only be in the selection list within the SELECT statement. Here is an example where the CASE function is used in the WHERE clause.

The output for this query looks like this:

Here I only wanted to display books from the titles table in pubs database if the price category is ‘Average’. By placing my CASE function in the WHERE clause I was able to accomplish this.

Conclusion

As you can see the CASE function is an extremely valuable function. It allows you to take a data column and represent it differently depending on one or more conditions identified in the CASE function call. I hope that the next time you need to display or use different values for specific column data you will review the CASE function to see if it might meet your needs.

See All Articles by Columnist Gregory A. Larsen


SQL Server Backup Best Practices #database #backup #and #recovery, #sql #server


#

SQL Server Backup Best Practices

The absolute worst time to find out that your recovery plans don t work is right in the middle of a critical system restore. Follow these SQL Server backup best practices to ensure that you really can restore your system when (not if) it goes down.

Perform Full Backups Daily

A full database backup is the foundation for every DBA s data protection plan and in most cases should be performed daily. SQL Server supports online backups, allowing end users and SQL Server jobs to be active while the backup operation occurs. Even so, large databases can take a long time to back up. Strategies for reducing the backup window include backing up to disk and utilizing backup data compression.

Perform Frequent Transaction Log Backups

Next most important is to back up the transaction log, which contains all of the recent activity in the database and can be used to restore a database to a given point in time. Backing up the transaction log also truncates it, keeping it from becoming full. Like database backups, transaction log backups can occur while the system is active. Organizations with active databases might back up the transaction log every 10 minutes while those with less active databases might need to back up the transaction log only every half hour or every hour.

Regularly Back Up System Databases

Your backup strategy is incomplete without a plan to back up SQL Server system databases (master, model, msdb). These databases contain system configuration information as well as SQL Server job information that needs to be restored as part of a total system restore. Back up system databases daily for frequently changing instances, weekly for more stable installations.

Back Up the Host OS Daily

SQL Server runs on top of the OS and an event such as a hardware failure could require a complete system restore, beginning with the OS. Therefore, daily backups of the host OS are a good idea. At a minimum, back up the host system partition following any system updates or configuration changes.

Practice Recovery Operations

Changing business requirements can affect your plans, quickly making backup strategies obsolete. Test your strategies regularly in different scenarios, including both system and individual database restores, to ensure your backup plans really work when you need them.

YOUR SAVVY ASSISTANT
The Missing Link to IT Resource
Christan Humphries

For her birthday, I gave my sister a card embossed with golden print that reads The economy stinks. Be happy you got this card. However disappointing the birthday gift (and my attempt at a joke) most likely was, the shiny message on it is accurate. And in this economy, which has forced companies to nudge and sometimes shove employees out of positions, I ve noticed a change in attitude toward job hunting. Here in the United States (and even in countries with better economies), it seems that changing jobs is not only accepted, it s almost expected. And just like on Match.com, It s OK to look. In fact, our network of IT products includes a free resource that even makes it easy to look: IT Job Hound.

IT Job Hound is an online job-search engine that concentrates on the IT industry. Job seekers can find recently posted positions from top IT companies on the site or via email job alerts no registration required! Whether you want to evaluate your skills, secure a new position, or completely change your job title, check out IT Job Hound at www.itjobhound.com. (If you re looking for gift ideas, give my sister a call.)

Mike, I am being pressured by the IS Director to use CA Backup Exec (or the like) in lew of regular SQL Server backups. I tried to explain how Full, Differential and Tran log backups are used to restore to a point in time by identifying T-SQL transactions and log sequence numbers. He insists that there are third part tools available that are easier to use (not a good reason in my book!) and continuously take pictures of changes. What is you take on said tools and which, if any, would you recomend. tnx, G. Douglas Clavell Seven Feathes Hotel and Casino Canyonville OR.

frontierteg (not verified)

First of all, I’m assuming you are always backing up your SQL server to disk and the server people have already figured in the space requirements and have allocated that space to you. Remember, they are part of your backup plan. With that assumption, like the article says, you should backup changes every 10-30 minutes in case of hardware failure. How often you create a full backup is really up to the individual business to decide. The questions become, how long can you stand to be down? Restoring a full backup and a days worth of logs takes less time than a full and a weeks worth of logs. As to being able to restore to a certain point, as long as you are keeping the last 2 weeks of full and/or log backups on tape, you shouldn’t have a problem restoring to any point. So the questions become, how often do I want to transfer the SQL backups to tape? and how far in the past do I want to be able to restore to? Hopefully you are backup up to a seperate server in case the SQL server goes down. And hopefully you are taking the backup media offsite at least daily in case the data center burns down. Or you have a backup SAN offsite that you just copy the backups to once they are created, again, in case the data center burns. All in all, a great article explaining the use of full backups and transaction logs. Use some common sense to implement the right backup scheme for your environment using seperate servers or portable media.

Why a FULL backup every day? You realize you limit your ability to recover to a certain point of time with every FULL backup you perform. Suppose we need to go back 4 days ago because the corruption was noticed until then? Now how are you going restore? You already toasted your full backup a couple of days ago. I prefer a FULL once a week. And differentials throughout the production hours. After a week, my data has been clean enough to go through another weekly full backup. If a user then calls me and says on Thursday, I need the backup from Tuesday, I can easily say sure which one, morning, afternoon or evening. Doing fulls daily limits your ability to restore via multi-days. And if you attempt to SAVE your DAILY backups, and you have a large database, well you’ll have the server dept calling you. Do a full once a week. Do diffs between that time whatever the business needs decides. You’ll see your diff backups are much smaller. If you have an error, backup the tranlogs and go from there. If you need to restore you’ve got a lot of choices. Same thing with system dbs. And I’d be REALLY surprised if you’re MAKING changes DAILY on jobs, schedules, etc, creating new or altering your schema, or your one tired puppy! The article doesn’t even tell you why you back them up. And far be it from actually RESTORING the system databases. Me I set mine to once a month, or heck once a quarter, knowing if I had to, I’d just re-intall SQL and restore because messing around with system dbs are no fun and causes extra headaches when I can do a clean re-install. Not much of a BEST practice article. I was expecting a little more from Otey. disappointing. Andy

Mark (not verified)

The most important thing about a backup is it works when you need it. To work it needs: 1) to be a complete backup set 2) current 3) supports a restore process that’s tested, works, and has contingency in it. When we set a backup schedule we look at what we’re trying to protect. We’ll reimage the OS in a heartbeat. So we only backup the OS weekly. If the DB has a warm standby, it only gets a full backup weekly and incrementals on the daily basis (plus the log backups during the day). More importantly than setting the schedule is confirming the backups are working. 1) did the jobs complete when expected? If they are late, it’s a sign your equipment is failing, or your requirements are changing, either require action. 2) are the restores working? You need to measure both of those things. Additionally, I measure the actions taken on #1 and #2. If the rate of action is changing. why..are we managing better or worse, and then what action should that drive. There’s a lot more to best practices for DB backup than setting a schedule.

Seems to omit the use of differential backups.


T-SQL Programming Part 1 – Defining Variables, and logic #articles, #databases, #microsoft


#

T-SQL Programming Part 1 – Defining Variables, and IF. ELSE logic

Whether you are building a stored procedure or writing a small Query Analyzer script you will need to know the basics of T-SQL programming. This is the first of a series discusses defining variables, and using the IF. ELSE logic.

This is the first of a series of articles discussing various aspects of T-SQL programming. Whether you are building a stored procedure or writing a small Query Analyzer script you will need to know the basics of T-SQL programming. This first article will discuss defining variables, and using the IF. ELSE logic.

Local Variables

As with any programming language, T-SQL allows you to define and set variables. A variable holds a single piece of information, similar to a number or a character string. Variables can be used for a number of things. Here is a list of a few common variable uses:

  • To pass parameters to stored procedures, or function
  • To control the processing of a loop
  • To test for a true or false condition in an IF statement
  • To programmatically control conditions in a WHERE statement

More than one variable can be defined with a single DECLARE statement. To define multiple variables, with a single DECLARE statement, you separate each variable definition with a comma, like so:

Here is an example of how to use the SELECT statement to set the value of a local variable.

One of the uses of a variable is to programmatically control the records returned from a SELECT statement. You do this by using a variable in the WHERE clause. Here is an example that returns all the Customers records in the Northwind database where the Customers Country column is equal to ‘Germany’

IF. ELSE

T-SQL has the “IF” statement to help with allowing different code to be executed based on the results of a condition. The “IF” statement allows a T-SQL programmer to selectively execute a single line or block of code based upon a Boolean condition. There are two formats for the “IF” statement, both are shown below:

Format one: IF condition then code to be executed when condition true

Format two: IF condition then code to be executed when condition true ELSE else code to be executed when condition is false

In both of these formats, the condition is a Boolean expression or series of Boolean expressions that evaluate to true or false. If the condition evaluates to true, then the “then code” is executed. For format two, if the condition is false, then the “else code” is executed. If there is a false condition when using format one, then the next line following the IF statement is executed, since no else condition exists. The code to be executed can be a single TSQL statement or a block of code. If a block of code is used then it will need to be enclosed in a BEGIN and END statement.

Let’s review how “Format one” works. This first example will show how the IF statement would look to execute a single statement, if the condition is true. Here I will test whether a variable is set to a specific value. If the variable is set to a specific value, then I print out the appropriate message.

The above code prints out only the phrase “The number is 29”, because the first IF statement evaluates to true. Since the second IF is false the second print statement is not executed.

Now the condition statement can also contain a SELECT statement. The SELECT statement will need to return value or set of values that can be tested. If a SELECT statement is used the statement needs to be enclosed in parentheses.

Here I printed the message “Found A-D Authors” if the SELECT statement found any authors in the pubs.dbo.authors table that had a last name that started with an A, B, C, or D.

So far my two examples only showed how to execute a single T-SQL statement if the condition is true. T-SQL allows you to execute a block of code as well. A code block is created by using a “BEGIN” statement before the first line of code in the code block, and an “END” statement after that last line of code in the code block. Here is any example that executes a code block when the IF statement condition evaluates to true.

Above a series of “PRINT” statements will be executed if this IF statement is run in the context of the master database. If the context is some other database then the print statements are not executed.

Sometimes you want to not only execute some code when you have a true condition, but also want to execute a different set of T-SQL statements when you have a false condition. If this is your requirement then you will need to use the IF. ELSE construct, that I called format two above. With this format, if the condition is true then the statement or block of code following the IF clause is executed, but if the condition evaluates to false then the statement or block of code following the ELSE clause will be executed. Let’s go through a couple of examples.

For the first example let’s say you need to determine whether to update or add a record to the Customers table in the Northwind database. The decision is based on whether the customer exists in the Northwind.dbo.Customers table. Here is the T-SQL code to perform this existence test for two different CustomerId’s.

The first IF. ELSE logic checks to see it CustomerId ‘ALFKI’ exists. If it exists it prints the message “Need to update Customer Record”, if it doesn’t exist the “Need to add Customer Record” is displayed. This logic is repeated for CustomerId = ‘LARS’. When I run this code against my Northwind database I get the following output.

As you can see from the results CustomerId ‘ALFKI’ existed, because the first print statement following the first IF statement was executed. Where as in the second IF statement CustomerId ‘LARSE’ was not found because the ELSE portion of the IF. ELSE statement was executed.

If you have complicated logic that needs to be performed prior to determining what T-SQL statements to execute you can either use multiple conditions on a single IF statement, or nest your IF statements. Here is a script that determines if the scope of the query is in the ‘Northwind’ database and if the “Customers” table exists. I have written this query two different ways, one with multiple conditions on a single IF statement, and the other by having nested IF statements.

As you can see I tested to see if the query was being run from the Northwind database and whether the “Customers” table can be found in sysobjects. If this was true, I printed the message “Table Customers Exists”. In the first example I had multiple conditions in a single IF statement. Since I was not able to determine which parts of the conditions in the IF statement where false the ELSE portion printed the message “Not in Northwind database or Table Customer does not exist”. Now in the second example where I had a nested IF statement I was able to determine whether I was in the wrong database or the object “Customers” did not exist. This allowed me to have two separate print statements to reflect exactly what condition was getting a false value.

I hope that this article has helped you understand how to declare and use local variables, as well as IF. ELSE logic. Local variables are useful to hold the pieces of information related to your programming process. Where as the IF statement helps control the flow of your program so different sections of code can be executed depending on a particular set of conditions. As you can see nesting IF statements and/or having multiple conditions on an IF statement allows you to further refine your logic flow to meet your programming requirements. My next article in this T-SQL programming series will discuss how to build a programming loop.

See All Articles by Columnist Gregory A. Larsen


Mark s stream of verbiage: Streaming data from MySQL #sql #stream


#

How do we stream data from MySQL? Traditionally, developers have thought that they can simply use their API’s cursor-equivalent object (e.g. resultset) and move through the results.

Unfortunately, this does not do streaming in most cases. Normally most client libraries (which call mysql_store_result) will read the entire result into memory, and you’re just going through an already-in-memory data set. This will fail if it doesn’t fit in memory.

Enter mysql_use_result – if your library can use this instead of mysql_store_result, you can then skip through the records without needing to keep them all in ram at once.

However, there is some bad news – while going through a result set in mysql_use_result, you can’t do any OTHER queries on the connection. An attempt to do so generally violates MySQL’s protocol and fails. So if you need to do other queries while you’re processing the large data set, you should open another connection.

We’ve found that in Perl DBI, mysql_use_result performs significantly worse on small data sets (i.e. ones which fit in ram, say 100M – 1G) than mysql_store_result (which is used by default). It seems that a lot more requests need to be done into the libraries from Perl.

Enter HANDLER. This is basically an SQL-level object which behaves a bit like a server-side cursor would in some other databases. You can have multiple handlers open on the same connection, and can do other operations while a handler is open. The disadvantage: it cannot be used for arbitrary queries. Only table and index scans of a single table can be done – no joins or sorting. And the client needs to know a bit about the table to choose the best method – the optimiser doesn’t get involved.

However, in the cases where a lot of data are likely to be returned, we can either not require joins, or do joins on the client-side – if there are small “dimension tables” that can be held in-memory on the client.

None of this is great of course, it would be better if mysql actually supported server-side cursors. Unfortunately it doesn’t (except in stored procedures).


SQL Server Reporting Services 2016 – Part 1 – The Database Avenger


#

This first post of 3 takes a quick peek at SSRS 2016 using the Community Technical Preview (CTP) 3.2. I will be making a quick post installation tweak and then guiding you through the steps to build your first report. If you are experienced with SSRS you can probably just scan this post to see the differences in 2016. See the past post Installing SQL Server 2016 for details on the install I did prior to working on this post.

Post 2 will cover some of the new features and changes to the old style SSRS reports (referred to as paginated reports).

Post 3 will cover the new Mobile Reports and KPI features.

SSRS is essentially a website that you can upload reports to giving people in your organisation a central place to go to get their data.

To start configuring SSRS open Reporting Services Configuration Manager from the start menu. This utility lets you configure the web server that will serve up the SSRS portal.

When everything is installed on the same box, like I have done for this test server, the default settings should be fine. One thing I am changing is my TCP port. This test server already has a website on port 80 so I ll use 8080 for SSRS.

If you change the port number on the Web Service URL screen you will also need to configure that same port number on the Report Manager URL screen. The screenshot below shows that it still has the default port number.

To set the new port number click Advanced and then edit.

After confirming the port change you will hopefully see the output below with a column of nice green ticks.

Once set click the URL. This will open the old style portal on your newly installed SSRS.

Click Preview the new Reporting Services to see what all the fuss is about.

Straight away I noticed that the new portal doesn t have the long spin-up time on first load that the old portal had. Clicking the down arrow icon and selecting Report Builder takes you to this link to download the Report Builder installer. Report Builder is the application we will use to develop our test reports. Report developers will most likely use SQL Server Data Tools (SSDT) to manage multiple reports.


Download and click through the installer making sure to set the default URL as instructed. My install is in native mode so I needed /reportserver on the end of the URL.

Once installed launch Report Builder to start building your first report.

To anyone who has used SSRS and Report Builder in the past, the screenshot above will show that not much has changed here other than the style of the UI. Click Blank Report in the New Report section to open the Report Designer.

Before we can build a report we need some data. Right click Data Sources and select Add Data Source. Give the data source the name Master and select Use a connection embedded in my report . Enter your SQL Server s connection details or localhost as server name if installed on the same machine as SSRS. Select master as the database and confirm.

Now we have a connection to our SQL Server we can build a data set to display in the report. Right click DataSets and select Add Data Set. Name the data set Databases and select Use a data set embedded in my report . Select our data source named Master then copy and paste the query below into the Query text box to populate the data set.

This query will return some basic information about each of the databases on the server.

Confirming the data set settings will return you to the designer. Let s tidy things up a bit before publishing this report. Click the title text box and enter Database Statuses Click Insert, Table and then Table Wizard. Select the Databases data set. On the next screen select all four fields and drag them into the Values box on the bottom right of the screen. Click through to the finish and confirm. Finally stretch the columns in the new grid so that the headers fit.

Click Design to go back to the designer and save the report. Select a location on the SSRS Server to store the report. This will publish the report to the SSRS portal. Refresh your browser that you used earlier to view your new report on the portal.

This was a real high level first look at SSRS 2016. I will dive a little deeper in the next two posts.

Share this:


Recovering a database with a missing transaction log #microsoft #sql #server, #sql


#

Recovering a database with a missing transaction log

Question: We had a SAN problem over the weekend and the upshot is that one of our main databases was shut down with open transactions, but we ve lost the log file. The last working backup is two weeks old.

Is there any way to recover the database without having to resort to the old backup?

Answer: Yes, but not without consequences.

Usually when a database has open transactions and the server crashes, crash recovery will run on the affected database and roll back the open transactions. This prevents the partial effects of transactions being present in the database. If the transaction log is not available when SQL Server starts, the database will be in the SUSPECT state.

In this case, the only way to bring the database online (note that I m not saying way to make the database usable ) is to use the emergency mode repair functionality that was added in SQL Server 2005. This basically builds a new transaction log and then runs a DBCC CHECKDB using REPAIR_ALLOW_DATA_LOSS. Read more .

The problem you will have if you decide to go this route is that emergency mode repair cannot roll back any of the active transactions as it has no knowledge of what they were, because the transaction log was destroyed. This means that at best, the resulting database will be transactionally inconsistent i.e. the state of the data in the database is unknown.

For instance, there may have been a transaction that was updating some sales records in a table, and only half of them were updated before the crash and subsequent transaction log loss occurred. You ll find it very difficult to figure out what state the data is in, and how to make the database properly usable again by the application.

Emergency mode repair is supposed to be a real last resort when all other methods of recovering data have failed.

In your case, you ll have to figure out what is the lesser of two evils recovering the database into an inconsistent state, or restoring the two-week old backup. You may end up deciding to do both, and trying to piece together the data, but that will be very time consuming and problematic again, because you don t know what was happening in the database at the time that the crash occurred.

To prevent this situation in future, beef up your backup strategy to use much more frequent backups, and add a high-availability technology that maintains a real-time copy of your database such as database mirroring or SQL Server 2012 Availability Groups.

[Unrelated: this is the last post on this blog, as we re going to focus on our own blogs moving forward. It s been great having this additional outlet to the community and we ve enjoyed writing the weekly posts for the last two years. We hope you ve enjoyed reading and learning from them too!]


Security Considerations for a SQL Server Installation #sql #server #domain #controller


#

Security Considerations for a SQL Server Installation

Enhance Physical Security

Physical and logical isolation make up the foundation of SQL Server security. To enhance the physical security of the SQL Server installation, do the following tasks:

Place the server in a room accessible only to authorized persons.

Place computers that host a database in a physically protected location, ideally a locked computer room with monitored flood detection and fire detection or suppression systems.

Install databases in the secure zone of the corporate intranet and do not connect your SQL Servers directly to the Internet.

Back up all data regularly and secure the backups in an off-site location.

Use Firewalls

Firewalls are important to help secure the SQL Server installation. Firewalls will be most effective if you follow these guidelines:

Put a firewall between the server and the Internet. Enable your firewall. If your firewall is turned off, turn it on. If your firewall is turned on, do not turn it off.

Divide the network into security zones separated by firewalls. Block all traffic, and then selectively admit only what is required.

In a multi-tier environment, use multiple firewalls to create screened subnets.

When you are installing the server inside a Windows domain, configure interior firewalls to allow Windows Authentication.

If your application uses distributed transactions, you might have to configure the firewall to allow Microsoft Distributed Transaction Coordinator (MS DTC) traffic to flow between separate MS DTC instances. You will also have to configure the firewall to allow traffic to flow between the MS DTC and resource managers such as SQL Server.

For more information about the default Windows firewall settings, and a description of the TCP ports that affect the Database Engine, Analysis Services, Reporting Services, and Integration Services, see Configure the Windows Firewall to Allow SQL Server Access .

Isolate Services

Isolating services reduces the risk that one compromised service could be used to compromise others. To isolate services, consider the following guidelines:

Run separate SQL Server services under separate Windows accounts. Whenever possible, use separate, low-rights Windows or Local user accounts for each SQL Server service. For more information, see Configure Windows Service Accounts and Permissions .

Configure a Secure File System

Using the correct file system increases security. For SQL Server installations, you should do the following tasks:

Use the NTFS file system (NTFS). NTFS is the preferred file system for installations of SQL Server because it is more stable and recoverable than FAT file systems. NTFS also enables security options like file and directory access control lists (ACLs) and Encrypting File System (EFS) file encryption. During installation, SQL Server will set appropriate ACLs on registry keys and files if it detects NTFS. These permissions should not be changed. Future releases of SQL Server might not support installation on computers with FAT file systems.

If you use EFS, database files will be encrypted under the identity of the account running SQL Server. Only this account will be able to decrypt the files. If you must change the account that runs SQL Server, you should first decrypt the files under the old account and then re-encrypt them under the new account.

Use a redundant array of independent disks (RAID) for critical data files.

Disable NetBIOS and Server Message Block

Servers in the perimeter network should have all unnecessary protocols disabled, including NetBIOS and server message block (SMB).

NetBIOS uses the following ports:

UDP/137 (NetBIOS name service)

UDP/138 (NetBIOS datagram service)

TCP/139 (NetBIOS session service)

SMB uses the following ports:

Web servers and Domain Name System (DNS) servers do not require NetBIOS or SMB. On these servers, disable both protocols to reduce the threat of user enumeration.

Installing SQL Server on a domain controller

For security reasons, we recommend that you do not install SQL Server 2012 on a domain controller. SQL Server Setup will not block installation on a computer that is a domain controller, but the following limitations apply:

You cannot run SQL Server services on a domain controller under a local service account.

After SQL Server is installed on a computer, you cannot change the computer from a domain member to a domain controller. You must uninstall SQL Server before you change the host computer to a domain controller.

After SQL Server is installed on a computer, you cannot change the computer from a domain controller to a domain member. You must uninstall SQL Server before you change the host computer to a domain member.

SQL Server failover cluster instances are not supported where cluster nodes are domain controllers.

SQL Server Setup cannot create security groups or provision SQL Server service accounts on a read-only domain controller. In this scenario, Setup will fail.


SQL Server cluster design: One big cluster vs #sql #server #load #balancing


#

SQL Server cluster design: One big cluster vs. small clusters

workload to balance utilization and to schedule planned maintenance without downtime.

This cluster design uses software heartbeats to detect failed applications or servers. In the event of a server failure, it automatically transfers ownership of resources (such as disk drives and IP addresses) from a failed server to a live server. Note that there are methods to maintain higher availability of the heartbeat connections, such as in case of a general failure at a site.

MSCS does not require any special software on client computers, so the user experience during failover depends on the nature of the client side of the client-server application. Client reconnection is often transparent, because MSCS has restarted the applications, file shares and so on, at exactly the same IP address. Furthermore, the nodes of a cluster can reside in separate, distant sites for disaster recovery.

SQL Server on a cluster server

SQL Server 2000 can be configured on a cluster with up to four nodes, while SQL Server 2005 can be clustered on up to eight nodes. When a SQL Server instance is clustered, the disk resources, IP address and services are forming a cluster group to allow for failover. For more technical information, refer to the article How to cluster SQL Server 2005.

SQL Server 2000 allows installation of 16 instances on a single cluster. According to Books-On-Line (BOL), SQL Server 2005 supports up to 50 SQL Server instances on a single server or processor, but…, only 25 hard drive letters can be used, so these have to be planned accordingly if you need many instances.

Note that the failover period of a SQL Server instance is the amount of time it takes for the SQL Server service to start, which can vary from a few seconds to a few minutes. If you need higher availability, consider using other methods, such as log shipping and database mirroring. For more information about disaster recovery and HA for SQL Server, go to Disaster recovery features in SQL Server 2005 and Microsoft’s description of disaster recovery options in SQL Server.

One big SQL Server cluster vs. small clusters

Here are the advantages of having one big cluster, consisting of more nodes:

  • Higher Availability (more nodes to failover to)
  • More load balancing options for performance (more nodes)
  • Cheaper maintenance costs
  • Growth agility. Up to four or eight nodes, depending on SQL version
  • Improved manageability and simplified environment (less to manage)
  • Maintenance with less downtime (more options for failover)
  • Failover performance unaffected by the number of nodes in the cluster

    Here are the disadvantages of having one big cluster:

  • Limited number of clustered nodes (what if a ninth node is needed?)
  • Limited number of SQL instances on a cluster
  • No protection against failure — if disk array fails, no failover can take place
  • Can’t create failover clusters at the database level or database object level, such as a table, with failover clustering

    Virtualization and clustering

    Virtual machines can also participate in a cluster. Virtual and physical machines can be clustered together with no problem. SQL Server instances can reside on a virtual machine, but performance may be impacted, depending on the resource consumption by the instance. Before installing your SQL Server instance on a virtual machine, you should stress test to verify that it can hold the necessary load.

    In this flexible architecture, you can load balance SQL Server between a virtual machine and a physical box when the two are clustered together. For example, develop applications using a SQL Server instance on a virtual machine. Then fail over to a stronger physical box within the cluster when you need to stress test the development instance.

    Important links describing Windows and/or SQL Server clustering

  • SQL Server clustering resources (This article contains important links and information about clustering).

    A cluster server can be used for high availability, disaster recovery, scalability and load balancing in SQL Server. It’s often better to have one bigger cluster, consisting of more nodes, than to have smaller clusters with fewer nodes. A big cluster allows a more flexible environment where instances can move from one node to the other for load balancing and maintenance.

    ABOUT THE AUTHOR:

    Michelle Gutzait works as a senior database consultant for ITERGY International Inc.. an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the last 10 years, she has worked exclusively with SQL Server. Her skills include SQL Server infrastructure design, database design, performance tuning, security, high availability, VLDBs, replication, T-SQL/packages coding, and more.
    Copyright 2007 TechTarget


  • SQL Server 2012 s Hardware and Software Requirements #sql #hardware #requirements, #microsoft


    #

    SQL Server 2012 s Hardware and Software Requirements

    SQL Server 2012 is designed to run on a wide range of computer systems from laptop and desktop systems to supercomputer class systems so its minimum hardware requirements are surprising low. The minimum processing requirement is a 1.0GHz CPU for a 32-bit x86 implementation and a 1.4GHz CPU for a 64-bit x64 implementation. Microsoft s recommended minimum processor speed is 2.0GHz.

    The minimum memory requirements for SQL Server 2012 are also quite low. The low-end SQL Server 2012 Express edition requires a minimum of 512MB of RAM, whereas the other editions require a minimum of 1GB. Microsoft s recommended minimum RAM for SQL Server is 4GB.

    These days, it s hard to buy even a desktop system with anything lower than a 1GHz processor and 1GB of RAM, so these hardware requirements shouldn t be a problem for most businesses. Of course, most production implementations will require more processing power and greater amounts of memory.

    Each SQL Server 2012 edition has different OS requirements. In addition, the 32-bit x86 versions and the 64-bit x64 versions of the SQL Server 2012 editions have somewhat different OS requirements. The following table lists all the supported Windows OSs for the principal editions of SQL Server 2012.

    Supported Windows OSs for the Principal Editions of SQL Server 2012

    SQL Server Edition

    Windows OSs That Support
    32-Bit SQL Server

    Windows OSs That Support
    64-Bit SQL Server

    SQL Server Enterprise

    Windows Server 2012 64-bit Datacenter, Standard, Essentials, and Foundation editions

    Windows Server 2008 R2 SP1 64-bit Datacenter, Enterprise, Standard, and Web editions

    Windows Server 2008 SP2 64-bit Datacenter, Enterprise, Standard, and Web editions

    Windows Server 2008 SP2 32-bit Datacenter, Enterprise, Standard, and Web editions

    Windows Server 2012 64-bit Datacenter, Standard, Essentials, and Foundation editions

    Windows Server 2008 R2 SP1 64-bit Datacenter, Enterprise, Standard, and Web editions

    Windows Server 2008 SP2 64-bit Datacenter, Enterprise, Standard, and Web editions

    SQL Server Business Intelligence

    Windows Server 2012 64-bit Datacenter, Standard, Essentials, and Foundation editions

    Windows Server 2008 R2 SP1 64-bit Datacenter, Enterprise, Standard, and Web editions

    Windows Server 2008 SP2 64-bit Datacenter, Enterprise, Standard, and Web editions

    Windows Server 2008 SP2 32-bit Datacenter, Enterprise, Standard, and Web editions

    Windows Server 2012 64-bit Datacenter, Standard, Essentials, and Foundation editions

    Windows Server 2008 R2 SP1 64-bit Datacenter, Enterprise, Standard, and Web editions

    Windows Server 2008 SP2 64-bit Datacenter, Enterprise, Standard, and Web editions

    SQL Server Standard

    Windows Server 2012 64-bit Datacenter, Standard, Essentials, and Foundation editions

    Windows Server 2008 R2 SP1 64-bit Datacenter, Enterprise, Standard, Foundation, and Web editions

    Windows 8 32-bit and 64-bit

    Windows 8 Pro 32-bit and 64-bit

    Windows 7 SP1 64-bit Ultimate, Enterprise, and Professional editions

    Windows 7 SP1 32-bit Ultimate, Enterprise, and Professional editions

    Windows Server 2008 SP2 64-bit Datacenter, Enterprise, Standard, Foundation, and Web editions

    Windows Server 2008 SP2 32-bit Datacenter, Enterprise, Standard, and Web editions

    Windows Vista SP2 64-bit Ultimate, Enterprise, and Business editions

    Windows Vista SP2 32-bit Ultimate, Enterprise, and Business editions

    Windows Server 2012 64-bit Datacenter, Standard, Essentials, and Foundation editions

    Windows Server 2008 R2 SP1 64-bit Datacenter, Enterprise, Standard, Foundation, and Web editions

    Windows 8 64-bit

    Windows 8 Pro 64-bit

    Windows 7 SP1 64-bit Ultimate, Enterprise, and Professional editions

    Windows Server 2008 SP2 64-bit Datacenter, Enterprise, Standard, Foundation, and Web editions

    Windows Vista SP2 64-bit Ultimate, Enterprise, and Business editions

    For a more complete discussion of SQL Server 2012, see Migrating to SQL Server 2012 .


    Security Assessment, VAPT, ECSA Training in Bangalore, Chennai, Mumbai, Pune, Delhi, Gurgaon,


    #

    A penetration test is done to evaluate the security of a computer system or network by simulating an attack by a malicious user / hacker. The process involves active exploitation of security vulnerabilities that may be present due to poor or improper system configuration, known and / or unknown hardware or software flaws, or operational weaknesses in process or design.

    This analysis is carried out from the position of a potential attacker, to determine feasibility of an attack and the resulting business impact of a successful exploit. Usually this is presented with recommendations for mitigation or a technical solution.

    About this workshop

    This workshop gives an in-depth perspective of penetration testing approach and methodology that covers all modern infrastructure, operating systems and application environments.

    This workshop is designed to teach security professionals the tools and techniques required to perform comprehensive information security assessment.

    Participants will learn how to design, secure and test networks to protect their organization from the threats hackers and crackers pose. This workshop will help participants to effectively identify and mitigate risks to the security of their organization s infrastructure.

    This 40 hour highly interactive workshop will help participants have hands on understanding and experience in Security Assessment.

    A proper understanding of Security Assessment is an important requirement to analyze the integrity of the IT infrastructure.

    Expertise in security assessment is an absolute requirement for a career in information security management and could be followed by management level certifications like CISA, CISSP, CISM, CRISC and ISO 27001.

    There are many reasons to understand Security Assessment:

    • Prepare yourself to handle penetration testing assignments with more clarity
    • Understand how to conduct Vulnerability Assessment
    • Expand your present knowledge of identifying threats and vulnerabilities
    • Bring security expertise to your current occupation
    • Become more marketable in a highly competitive environment

    Therefore this workshop will prepare you to handle VA / PT assignments and give you a better understanding of various security concepts and practices that will be of valuable use to you and your organization.

    This workshop will significantly benefit professionals responsible for security assessment of the network / IT infrastructure.

    • IS / IT Specialist / Analyst / Manager
    • IS / IT Auditor / Consultant
    • IT Operations Manager
    • Security Specialist / Analyst
    • Security Manager / Architect
    • Security Consultant / Professional
    • Security Officer / Engineer
    • Security Administrator
    • Security Auditor
    • Network Specialist / Analyst
    • Network Manager / Architect
    • Network Consultant / Professional
    • Network Administrator
    • Senior Systems Engineer
    • Systems Analyst
    • Systems Administrator

    Anyone aspiring for a career in Security Assessment would benefit from this workshop. The workshop is restricted to participants who have knowledge of ethical hacking countermeasures.

    The entire workshop is a combination of theory and hands-on sessions conducted in a dedicated ethical hacking lab environment.

    • The Need for Security Analysis
    • Advanced Googling
    • TCP/IP Packet Analysis
    • Advanced Sniffing Techniques
    • Vulnerability Analysis with Nessus
    • Advanced Wireless Testing
    • Designing a DMZ
    • Snort Analysis
    • Log Analysis
    • Advanced Exploits and Tools
    • Penetration Testing Methodologies
    • Customers and Legal Agreements
    • Rules of Engagement
    • Penetration Testing Planning and Scheduling
    • Pre Penetration Testing Checklist
    • Information Gathering
    • Vulnerability Analysis
    • External Penetration Testing
    • Internal Network Penetration Testing
    • Routers and Switches Penetration Testing
    • Firewall Penetration Testing
    • IDS Penetration Testing
    • Wireless Network Penetration Testing
    • Denial of Service Penetration Testing
    • Password Cracking Penetration Testing
    • Social Engineering Penetration Testing
    • Stolen Laptop, PDAs and Cell phones Penetration Testing
    • Application Penetration Testing
    • Physical Security Penetration Testing
    • Database Penetration testing
    • VoIP Penetration Testing
    • VPN Penetration Testing
    • War Dialing
    • Virus and Trojan Detection
    • Log Management Penetration Testing
    • File Integrity Checking
    • Blue Tooth and Hand held Device Penetration Testing
    • Telecommunication and Broadband Communication Penetration Testing
    • Email Security Penetration Testing
    • Security Patches Penetration Testing
    • Data Leakage Penetration Testing
    • Penetration Testing Deliverables and Conclusion
    • Penetration Testing Report and Documentation Writing
    • Penetration Testing Report Analysis
    • Post Testing Actions
    • Ethics of a Penetration Tester
    • Standards and Compliance

    T-SQL Programming Part 5 – Using the CASE Function #microsoft, #articles, #microsoft


    #

    T-SQL Programming Part 5 – Using the CASE Function

    Have you ever wanted to replace a column value with a different value based on the original column value? Learn how, with the T-SQL CASE function.

    The CASE function is a very useful T-SQL function. With this function you can replace a column value with a different value based on the original column value. An example of where this function might come in handy is where you have a table that contains a column named SexCode, where 0 stands for female, 1 for male, etc. and you want to return the value “female” when the column value is 0, or “male” when the column value is 1, etc. This article will discuss using the CASE function in a T-SQL SELECT statement.

    The CASE function allows you to evaluate a column value on a row against multiple criteria, where each criterion might return a different value. The first criterion that evaluates to true will be the value returned by the CASE function. Microsoft SQL Server Books Online documents two different formats for the CASE function. The “Simple Format” looks like this:

    And the “Searched Format” looks like this:

    Where the “input_expression” is any valid Microsoft SQL Server expression, the “when_expression” is the value in which the input_expression is compared, the “result_expression ” is the value that will be return for the CASE statement if the “when_expression” evaluates to true, ” . n ” represents that multiple WHEN conditions can exist, the “else_result_expression ” is the value that will be returned if no “when_expression” evaluates to true and in the “Searched Format” the “Boolean_expression” is any Boolean express that when it evaluates to true will return the “result_expression”. Let me go through a couple of examples of each format to help you better understand how to use the CASE function in a SELECT statement.

    For the first example let me show you how you would use the CASE function to display a description, instead of a column value that contains a code. I am going to use my earlier example that I described at the top of this article where I discussed displaying “female” or “male” instead of the SexCode. Here is my example T-SQL Code:

    Here is the output from this T-SQL code:

    This example shows the syntax in action for a CASE function using the “Simple Format”. As you can see the CASE function evaluates the PatientSexCode to determine if it is a 0, 1, or 2. If it is a 0, then “female” is displayed in the output for the “Patient Sex” column. If the PatientSexCode is 1, then “male” is display, or if PatientSexCode is 2 then “unknown” is displayed. Now if the PatientSexCode is anything other than a 0, 1 or 2 then the “ELSE” condition of the CASE function will be used and “Invalid PatientSexCode” will be displayed for the “Patient Sex” column.

    Now the same logic could be written using a “Searched Format” for the CASE function. Here is what the SELECT statement would look like for the “Searched Format”:

    Note the slight differences between the “Simple” and “Searched” formats. In the “Simple” format I specified the column name for which row values will be compared against the “when_expressions” ,where as in the “Searched” format each WHEN condition contains a Boolean expression that compares the PatientSexCode column against a code value.

    Now the CASE function can be considerably more complex than the basic examples I have shown. Suppose you want to display a value that is based on two different columns values in a row. Here is an example that determines if a Product in the Northwind database is of type Tins or Bottles, and is not a discontinued item.

    The output for the above command on my server displays the following:

    As you can see I’m using a “Searched Format” for this CASE function call. Also, each WHEN clause contains two different conditions. One condition to determine the type (tins, or bottles) and another condition to determine if the product has been discontinued. If the QuantityPerUnit contains the string “Tins” and the Discontinue column value is 0 then the “Type of Availability” is set to “Tins”. If the QuantityPerUnit contains the string “Bottles” and the Discontinue column value is 0 then the “Type of Availability” is set to “Bottles”. For all other conditions, the “Type or Availability” is set to “Not Tins , Not Bottles , or is Discontinued.

    The WHEN clauses in the CASE function are evaluated in order. The first WHEN clause that evaluates to “True” determines the value that is returned from the CASE function. Basically, multiple WHEN clauses evaluate to “True”, only the THEN value for the first WHEN clause that evaluates to “True” is used as the return value for the CASE function. Here is an example where multiple WHEN clauses are “True.”

    The output on my machine for this query looks like this:

    If you look at the raw titles table data in the pubs database for the title “You Can Combat Computer Stress!” you will note that the price for this book is $2.99. This price makes both the “price 12.00” and “price 3.00” conditions “True”. Since the conditions are evaluated one at a time, and the “price 12.00” is evaluated prior to the “price 3.00,” the “Price Category” for the title “You Can Combat Computer Stress!” is set to “Cheap”.

    The CASE function can appear in different places within the SELECT statement, it does not have to only be in the selection list within the SELECT statement. Here is an example where the CASE function is used in the WHERE clause.

    The output for this query looks like this:

    Here I only wanted to display books from the titles table in pubs database if the price category is ‘Average’. By placing my CASE function in the WHERE clause I was able to accomplish this.

    Conclusion

    As you can see the CASE function is an extremely valuable function. It allows you to take a data column and represent it differently depending on one or more conditions identified in the CASE function call. I hope that the next time you need to display or use different values for specific column data you will review the CASE function to see if it might meet your needs.

    See All Articles by Columnist Gregory A. Larsen


    Steps to Rebuild Master Database In SQL Server #backup #database #sql #server


    #

    Rebuild Master Database in SQL Server Without Backup

    It has been observed that many SQL Server administrators, in fact majority of them, backup their user databases regularly, but turn a blind eye to the system databases. While most of them do not understand the value of these databases, some remain in their comfort zone owing to the years of no failure. Whatever be the reason, we will help you to be acquainted with the methods to restore the system databases, should the admins find themselves in a crucial situation.

    Reasons Behind Master Database Rebuilding

    There are a number of reasons due to which, the master database needs to be rebuild. Some of them are:

    • At times, user delete some crucial information like linked servers, logins, SQL Server configuration, and other user objects.
    • The master database is not coming online and the admin does not have a backup of the master database.
    • It can also happen that the master database has been corrupted due to various hardware and software failure and can no longer be used.
    • The administrator might want to make a clone of the server or might want to restore the master database to a new instance.

    How to Rebuild Master Database in SQL Server?

    The following procedure will help you to rebuild the master system database. The entire process, which we will discuss in this write up, will be divided into three sections for the convenience of the users.

    1. Pre-Rebuild Process

    Since, only the master database is corrupt, therefore, in order to ensure the authenticity of other system database files, it gets necessary to take their backup. The files that need to be backed up are MSDB Data, MSDB Log, model model Log In order to do so, follow the below mentioned steps:

    • Open SQL Server Configuration Manager.
    • From the left pane, select SQL Server Services option. This will list all the services that are currently running.
    • Right click on the service and select Stop option for stopping all the services one by one.
    • Exit SQL Server Configuration Manager.
    • Browse to the location where all the system files of a particular SQL Server instance are stored.

    C:\Program Files\Microsoft SQL Server\[INSTANCE NAME]\MSSQL\DATA

    Note: Keep in mind this location, as this is where the master database files will be rebuild automatically.

    • Cut all the healthy system files, save them in a new folder, and delete corrupted master mastlog, tempdb, templog files.

    2. Rebuilding Process

    The steps that are followed for rebuilding are:

    • In the command prompt, change the directory location of the server instance to the location where the SQL Server 2016 installation media.
    • Open Command Prompt as Administrator.
    • Since my installation media is stored in the F: Drive, change the directory location by entering f: and press Enter.
    • The next step is to run the following command:

      Note: In case your account name contains a blank space, keep in mind to enclose the account name within quotes. Moreover, if you are specifying multiple accounts, separate them with a space.
    • When all the system databases are rebuilt, it returns no message in the command prompt. In order to verify whether the process has been completed successfully, examine the summary.txt file. the location of this file is:
      C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Logs

    3. Post-Rebuild Process

    • The first step after rebuilding is to restart all the services, which were stopped earlier.
    • If you have backups of model and MSDB databases, restore them.
    • In case the backup is not present, simply replace the rebuilt MSDB and model files with the files that were backed up in the I. section. (This should be done after stopping the services).

    In this blog, we have described the full procedure for rebuilding the master database in the scenario when the backup is not available.We believe that with its help you will be able to rebuild the master database successfully.


    SQL Server Rounding Functions – Round, Ceiling and Floor #round #sql #server


    #

    SQL Server Rounding Functions – Round, Ceiling and Floor

    By: Jeremy Kadlec | Read Comments (17) | Related Tips: 1 | 2 | 3 | 4 | More > Functions – System
    Problem

    I saw your recent tip on Calculating Mathematical Values in SQL Server and have some related issues as I try to round values in my application. My users and me have a difference of opinion on some of the calculations in our reporting applications. All of the code is in T-SQL, but I think the reporting issues are related to data types and rounding down or rounding up rules. Do you have any insight into these issues? I would like to see some examples with a variety of coding options.

    Solution

    Rounding can become misunderstood if the underlying data types and rounding functions are not understood. Depending on the data type (integer, float, decimal, etc.) the rounded value can be different. In addition, depending on the SQL Server rounding function (ROUND(), CEILING(), FLOOR()) used in the calculation the values can differ as well. As such, it is important to find out the user rounding requirements then translate those requirements into the appropriate T-SQL command.

    From a definition perspective, let’s start here:

    • ROUND – Rounds a positive or negative value to a specific length and accepts three values:
      • Value to round
        • Positive or negative number
        • This data type can be an int (tiny, small, big), decimal, numeric, money or smallmoney
      • Precision when rounding
        • Positive number rounds on the right side of the decimal point
        • Negative number rounds on the left side of the decimal point
      • Truncation of the value to round occurs when this value is not 0 or not included
    • CEILING – Evaluates the value on the right side of the decimal and returns the smallest integer greater than, or equal to, the specified numeric expression and accepts one value:
      • Value to round
    • FLOOR – Evaluates the value on the right side of the decimal and returns the largest integer less than or equal to the specified numeric expression and accepts one value:
      • Value to round

    Let’s walk through each function with a few different data types to see the results.

    SQL Server ROUND, CEILING and FLOOR Examples for Integer Data Types

    Example 1a – In this first example let’s just look at rounding a positive integer for the precision value of 1 yields all three rounding functions returning the same value. In this example we are using a variable with the functions and check out the result commented out on the right of the function.

    Example 1b – Since the CEILING AND FLOOR functions do not have any optional values, let’s test some options with the ROUND function. In this example, let’s see the impacts of a negative number as the precision as well as the specifying additional positions that exceed the value to round. Check out these results with the result commented out on the right of the function.

    Example 1c – Let’s expand the digits in this example with the ROUND function and see the impacts with the result commented out on the right of the function.

    Example 1d – Let’s round a negative integer and see the impacts with the result commented out on the right of the function.

    SQL Server ROUND, CEILING and FLOOR Examples for Decimal, Numeric and Float Data Types

    Example 2a – With a decimal data type and the ROUND function with various length parameters (i.e. 1, 2 or 3) yields different final values in our example. The 5 in the second digit to the right of the decimal point is significant when the length parameter is 1 when rounding the value. In addition, with the decimal data type the CEILING and FLOOR functions take the decimal places into consideration for differing values as well.

    Example 2b – Here is a quick example of using the numeric data type with the ROUND function. This follows much of the same behavior as the decimal data type.

    Example 2c – In the final example, with a float data type you can see the same type of behavior as was the case with the decimal and numeric examples above with the ROUND, CEILING and FLOOR functions.

    Next Steps

    Last Update: 2017-01-31

    Technically, there aren’t an “insufficient number of digits” from example 1b. When rounding to the nearest 100 (or 1,000), 6 is just closer to zero. Same thing in 1c; 444 is closer to zero than to 1,000 or 10,000.

    Good tip and explanation. This is pretty logical overall, but sometimes you really need to stop and think it through. These examples are a great help with that.

    Monday, August 12, 2013 – 5:00:56 PM – Scott Coleman

    In answer to ClaudioRound’s question “why this rounding does not work” (for 172.765).

    Subtracting 128 from this value drops the two leftmost bits, so it gains two more fractional bits resulting in 101100.11000011110101110000101000111101011100001010010. (The mantissa is always 53 bits long in a float.) This is about 44.76500000000000057, so even though it has the same fractional digits the value of “ROUND(44.765, 2)” is 44.77.

    Another fun fact is that “ROUND(CAST(172.7650000000000160090000000000000099 AS FLOAT), 2)” returns 172.76, but if you add a trailing 0 then “ROUND(CAST(172.76500000000001600900000000000000990 AS FLOAT), 2)” returns 172.77. Don’t ask me why.

    The moral of the story is that if you really care about exact fractional values then don’t use FLOAT or REAL. Even casting it to DECIMAL before ROUNDING may help.

    Wednesday, June 12, 2013 – 1:56:53 PM – Dave


    How can I make MS SQL Server available for connections? Stack Overflow


    #

    I’m trying to connect to MS SQL Server (running on my machine) from a Java program. I’m getting the following long winded exception:

    Exception in thread “main” com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: “Connection refused: connect. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.”.

    When I check “Properties” and click “View Connection Properties” in the Object Explorer of MS SQL, I find that the “Server is Unavailable.” This seems possibly related to the exception message.

    How can I make the server available?

    I am using SQL Server 2008, and I have now enabled TCP/IP, and restarted my instance. I am still told that “Server is unavailable.”

    Any other ideas?

    I ran into this problem as well. The MSKB article applies to SQL server 2005.

    As the “SQL Server Surface Area Configuration” tool has been dropped in lieu of “Facets” – this wasn’t obvious to me.

    I resolved this by setting the TCPAll port and enabling the relevant IP.

    Steps

    Open the Sql Server Configuration Manager (Start -> Programs -> Microsoft SQL Server 2008 -> Configuration Tools)

    Expand SQL Server Network Configuration -> [Your Server Instance]

    Double click TCP/IP

    Ensure Enabled is Yes

    Scroll to the bottom and set the TCP Port under IPAll, (1433 by default)

    Find the IP address you want to connect to and set Enabled and Active to Yes

    Before messing about with connections, first check that the SQL Server Service is actually running.

    You can do this by either using the SQL Server Configuration Manager (located in the configuration tools folder) or in the standard services console in the Windows control panel.

    Once you have checked the service is up and running, you need to ensure that SQL Server has been configured to allow remote connections.

    See below for an explanation on how to do this:

    Open up Sql Server Configuration Manager. ( Start | Programs | Whatever version of sql server | Configuration Tools)

    Browse down to ‘SQL Server Services’ and restart your instance. However you could do this in Management Studio by right clicking on the instance and selecting restart.

    If your restart fails then, check out Computer Management | Event Viewer | Application and look for sql server events. it will record successful and error messages here.

    answered Jul 6 ’09 at 12:49

    Here are my screenshots. If you can’t read the words, then download the image or copy and paste the image into Paint.

    “Additional Properties” Tab

    Inside Microsoft SQL Server Management Studio:

    answered Jan 13 ’15 at 20:06

    First off, check that the sql server service is running. If you’re using SQL 2005 or 2008, check Configuration manager (2008) or Surface are configuration tool (2005) to make sure the TCP/IP protocol is enabled and TCP/IP connections are allowed. With SSE(express) these are off by default, which would cause your problem. Also just in case you’re running multiple instances, you may need SQL browser service running. If this is the case, you should be able to connect object explorer by using (local) as the server address, since this will use a local/shared memory connection.

    answered Jul 6 ’09 at 12:52

    From 2005 and up the SQL server browser service has te be running.
    That one fooled me many times.

    answered Sep 28 ’09 at 11:22

    Your Answer

    2017 Stack Exchange, Inc


    Managing SQL Server XML Result Sets with XML Notepad 2007 #managing #sql


    #

    Managing SQL Server XML Result Sets with XML Notepad 2007

    By: Jay Dave | Read Comments | Related Tips: More > XML

    Problem
    With SQL Server 2005’s native support of XML, XML seems to be popping up more and more in applications and as output from a variety of SQL Server administrative commands. In particular organizations, working with XML has become the norm for particular needs and applications. Unfortunately, creating, managing and editing XML data has still been a challenge with the native SQL Server tool set. Although a number of the native SQL Server commands (Cross Walk – SQL Server Query Plans ) can have data returned in an XML format, working with the data from that point forward can be a major challenge, especially with large result sets. As such, how should I go about working with XML result sets directly from SQL Server 2005?

    Solution
    One approach is to download a free tool from Microsoft called the XML Notepad 2007. What you can do with this tool is capture the needed information from SQL Server in XML format and then be able to work with the data in the XML Notepad 2007 tool. This tool provides the following functionality:

    • View XML as text, tree or spreadsheet-like grid
    • Cut/copy/paste maintains the syntax color
    • Drag and drop editing supported
    • Blocks of XML can be expanded and collapsed
    • Infinite undo/redo, even for major operations like re-indenting the file or making XML canonical
    • Supports XML documents over 100 megabytes
    • Nudge buttons for moving nodes up and down in the tree view
    • Preview window for XML-style sheet processing instructions
    • XML Differencing tool supports single or multiple documents
    • Project window to group related files

    So let’s walk through some examples to show how this tool can be helpful when managing XML result sets from a SQL Server perspective. These examples should show how using the XML Notepad 2007 saves time and allows you to read the SET SHOWPLAN XML and SET STATISTICS XML result sets in a more readable format than with SQL Server 2005 Management Studio.

    Example 1 – SET SHOWPLAN_XML

    As a first example, let’s capture the query plan when running SHOWPLAN_XML in SQL Server 2005 Management Studio using the AdventureWorks database.

    SET SHOWPLAN_XML ON
    GO
    SELECT *
    FROM Person.Contact
    GO

    Once you execute this query you will see the following results shown below:

    Once you click the link from the result set window in SQL Server 2005 Management Studio, it will display the XML as shown below. This data is invaluable, but is there a better way to view this data? Here is where Microsoft’s XML Notepad 2007 comes in handy.

    The XML PLAN can be saved in my test directory as “Test XML Showplan2.xml”. Now it is possible to open this file using Microsoft’s XML Notepad 2007 as shown below. As you can see, the results are in a more readable format than above in SQL Server 2005 Management Studio. In addition, each of the nodes can be expanded or collapse on the left side to see only the parts of the query plan in order to focus on the issue.

    Example 2 – SET STATISTICS XML

    As a second example, let’s capture the actual plan by using SET STATISTICS XML ON. The SET STATISTICS XML ON example shown below provides more information in the query plan such as the degree of parallelism, compile time, etc.

    SET STATISTICS XML ON
    GO
    SELECT *
    FROM Person.Contact PC
    INNER JOIN Person.Address PA
    ON PC.ContactID = PA.AddressID
    GO

    Once you click the link, you can save the XML file to a local directory just like in the previous example. After opening the file in Microsoft’s XML Notepad 2007 you will have something like this:

    • As XML is used more in applications, traditional management tools are going to be a little cumbersome, so relying on a separate tool like XML Notepad 2007 may be a good alternative when you need to review, edit and create XML documents. The traditional management tools may change to meet those needs, but until then using XML Notepad 2007 may be your best alternative.
    • This tip has only scratched the surface of the XML Notepad 2007 capabilities. Check out all that the tool has to offer and consider it a viable utility in your tool belt as you work with XML more and more from an administrative and development perspective.
    • To download the XML Notepad 2007, please visit this URL:

      • http://www.microsoft.com/downloads/details.aspx?familyid=72d6aa49-787d-4118-ba5f-4f30fe913628 displaylang=en
    • Check out the XML tips on MSSQLTips.com for more information on managing XML with SQL Server 2005:

      • Cross Walk – SQL Server Query Plans
      • BCP XML Format Files with SQL Server 2005
      • Cross Walk – Returning XML Result Sets with SQL Server
      • Capturing Deadlock Information in XML Format
      • Capturing Graphical Query Plans with Profiler
    • Special thanks to Jay Dave of the MSSQLTips.com community for contributing this tip.

    Last Update: 2007-06-22


    Transact-SQL Reference (Transact-SQL) #ms #sql #server


    #

    Transact-SQL Reference (Transact-SQL)

    Transact-SQL is central to using SQL Server. All applications that communicate with an instance of SQL Server do so by sending Transact-SQL statements to the server, regardless of the user interface of the application.

    The following is a list of the kinds of applications that can generate Transact-SQL:

    • General office productivity applications.
    • Applications that use a graphical user interface (GUI) to let users select the tables and columns from which they want to see data.
    • Applications that use general language sentences to determine what data a user wants to see.
    • Line of business applications that store their data in SQL Server databases. These applications can include both applications written by vendors and applications written in-house.
    • Transact-SQL scripts that are run by using utilities such as sqlcmd .
    • Applications created by using development systems such as Microsoft Visual C++, Microsoft Visual Basic, or Microsoft Visual J++ that use database APIs such as ADO, OLE DB, and ODBC.
    • Web pages that extract data from SQL Server databases.
    • Distributed database systems from which data from SQL Server is replicated to various databases, or distributed queries are executed.
    • Data warehouses in which data is extracted from online transaction processing (OLTP) systems and summarized for decision-support analysis.

    To view the Transact-SQL reference topics

    To view the list of topics in the Transact-SQL reference section of SQL Server Books Online, follow these steps:

    1. On the SQL Server Books Online toolbar, click the Sync with Table Of Contents button.
    2. On the Contents tab, expand Transact-SQL Reference.

    When viewing this content in the MSDN Library, use the Table of Contents to browse for topics or search for commands by name. If you do not see the Table of Contents, to show it, click the page and press t .

    For a list of Transact-SQL functions by category, see Functions (Transact-SQL) .

    For a short tutorial about how to write Transact-SQL, see Writing Transact-SQL Statements Tutorial .


    5 SQL Server 2014 Security Enhancements #sql #server #2014, #database #security


    #

    5 SQL Server 2014 Security Enhancements

    SQL Server 2014 continues the Microsoft commitment to excellence in security. According to the National Institute of Standards and Technology (NIST) public security board, SQL Server reportedly has the lowest number of security vulnerabilities across major database vendors. In addition, SQL Server has been deemed the most secure database by the Information Technology Intelligence Consulting (see, SQL Server Delivers Industry-Leading Security , 2010 and Stratus ftServer and SQL Server Deliver Continuous Availability , 2012).

    SQL Server 2014 offers Common Criteria (CC) Certification at the High Assurance Level, as well as enhanced separation of duty for greater security. Additional new features include Transparent Data Encryption for better data protection and Encryption Key Management, allowing the storage of keys on a separate server.

    1. Transparent Data Encryption (TDE)

    In SQL Server 2014, TDE functionality takes the non-encrypted backup data and encrypts the data before writing it to disk. In order to preserve a major compression factor of SQL Server backup compression in conjunction with the newly introduced encryption, the compression is performed on the backup data first, before the encryption is applied to the compressed data. With this order of actions, a major compression factor can be preserved while the backup as it rests on the backup vehicle is encrypted.

    2. Encryption Key Management

    As discussed above, to secure your backup data in SQL Server 2014, you can now select to encrypt when you create a backup. The encryption options include an encryption algorithm and a certificate or asymmetric key to be used for the encryption. Only asymmetric keys residing in the Extended Key Management is supported.

    Multiple encryption algorithms up to AES 256-bit are supported giving you a variety of algorithms to choose from. Adding backup encryption to a TDE encrypted database, gives additional protection for your data.

    You can control the process using PowerShell. SQL Server Management Objects (SMO), or SQL Server Management Studio (SSMS), or T-SQL. Encryption options can also be used when configuring SQL Server Managed Backup to Windows Azure, therefore providing additional security for your off-site data.

    It is very important that the certificate or key used for encrypting must be backed up to a different location than the backup that the certificate or key is used to encrypt. Without this certificate or key the backup file cannot be restored. For more information, see: Backup and Restore Enhancements in SQL Server 2014 CTP2 .

    New Server-Level Permissions

    SQL Server 2014 also has 3 new server-level permissions.

    3. CONNECT ANY DATABASE Permission

    Grant CONNECT ANY DATABASE to a login that must connect to all databases that currently exist and to any new databases that might be created in future. This permission does not grant any permission in any database beyond connect. You may combine CONNECT ANY DATABASE permission with SELECT ALL USER SECURABLES or VIEW SERVER STATE to allow an auditing process to view all data or all database states on the instance of SQL Server.

    4. IMPERSONATE ANY LOGIN Permission

    When granted, IMPERSONATE ANY LOGIN allows a middle-tier process to impersonate the account of clients connecting to it, as it connects to databases. When denied, a high privileged login can be blocked from impersonating other logins. For example, a login with CONTROL SERVER permission can be blocked from impersonating other logins.

    5. SELECT ALL USER SECURABLES Permission

    When SELECT ALL USER SECURABLES permission is granted, a login such as an auditor can view data in all databases that the user can connect to. For additional information, see What s New (Database Engine)


    BackupAssist – System State backup and restore #backupassist, #backup #assist, #backup, #back


    #

    System State backup and restore

    What’s included in a System State backup?

    The exact system components that make up your machine’s System State depend on the operating system installed and how it is has been configured. A System State backup generally includes a copy of any installed device drivers and related files, most of the Windows directory, the Windows Registry, the Active Directory configuration (where applicable) and system files under Windows File Protection.

    System State backups for Vista and Server 2008 are usually between 7GB and 15 GB and for XP and Server 2003, they are generally much smaller, being between 200MB and 300 MB. Many files in the Windows directory have multiple hard links. If you are backing up the System State using the File Replication Engine or the Rsync Engine and have Single Instance Store enabled, only a single copy of each multiply linked file will be stored on your backup destination. This data does not need to be copied on subsequent backups, which reduces backup times and saves storage space on your destination.

    Why is a System State backup useful?

    From a System State backup you can restore your Windows system settings in the event of a system failure or corruption. A System State backup is therefore particularly important for disaster recovery purpose as it eliminates you having to reconfigure Windows back to its original state before the system failure occurred.

    We recommended that you always have a recent backup of your System State and that you should perform System State backups on a regular basis, even daily, to increase your level of protection. We also recommended that you perform System State backups before and after any major change is made to your server.

    How to back up the System State

    With BackupAssist v6 you can schedule local* ‘System State only’ backups across all modern Windows operating systems, or even back up the System State as part of a larger backup including files and applications, using File Replication, Zip, or Windows Imaging. A System State backup includes important Windows systems settings, such as the Registry, and is crucial for system recovery.

    *BackupAssist cannot be used to back up the System State of a remote machine; you must install BackupAssist on each server of which you require a System State backup.

    Complete the following steps to configure a BackupAssist job to back up the local machine’s System State:

    1. Launch BackupAssist and either edit an existing File Replication, Zip or Imaging job by selecting Edit from the top menu and choosing the appropriate job, or create a new File Replication, Zip or Imaging job by going to File New backup job .
    2. If you are creating a new job check the Backup local system state option during the Files and Folders step of the Job Creation Wizard.
    3. If you are editing an existing job select Files and folders from the left menu, click the Local system selections tab, and then enable the Backup local system state option .

    How to restore the System State

    Complete the following steps to restore the System Stattefrom a BackupAssist backup using the BackupAssist Restore Console:

    • Note. you cannot restore the System State from an Image backup using the BackupAssist Restore Console. You must use the built-in Windows tool, wbadmin. Visit wbadmin.info for instructions.
    1. In BackupAssist, Click Restore in the top navigation bar and choose the BackupAssist Restore Console .
    2. Click Load all known backups to load all backup catalogues located in the BackupAssist settings folder, or use the Browse option to locate the backup set from which you want to restore.
    3. Choose the job that corresponds to the backup from which you want to restore the System State
    4. Use the calendar to select the date of the backup from which you wish to restore.
      • Note. dates for which backups are available are marked in bold on the calendar.
    5. Use the middle pane to expand the loaded backup set and select the System State to restore.
      • Note. files located on the Windows system drive (C: drive in the example above) may be available for restore, even though only the System State was selected for backup. These files are associated with the System State. If you choose to restore individual files from this list and not the System State, the System State will not be restored, and the files themselves may not restore correctly. If you choose to restore these files together with the System State, the System State option will override selections where there is an overlap. If you want to perform a full restore and are unsure which files are included in the System State we recommend selecting both the System State and all other files listed.
    6. Once you have made your selections click the Restore to button on the bottom right of the window.
    7. The restore confirmation screen will then load:
      You can choose to restore the System State either to its original location or to an alternate location of your choosing. If you select an alternate location click [. ] to set an alternate restore path.
    8. Once you have selected where to restore the System State to, click OK to perform the restore.
      • Note. if you are restoring the System State to a machine that hosts Directory Services, you will be prompted to reboot into the Directory Services Restore Mode.
      • Note. During a System State restore the necessary files are copied to a temporary directory and then moved to their correct location after a reboot of the machine. Before the restore starts, the Restore Console will take a VSS snapshot of the volumes to be restored. This allows the user to manually roll back to a pre-restore state, if necessary, using Windows’ previous versions feature.


    SQL Server Monitor #sql #server #monitor, #appmanager, #microsoft


    #

    Microsoft SQL Server

    By monitoring SQL from the AppManager console, you can ensure your SQL database infrastructure is performing and available for end users. This module’s extensive alerting capabilities inform you immediately when incidents arise so you can take action. By responding proactively, you reduce downtime and ensure compliance with Service Level Agreements to better meet the needs of customers and end users.

    AppManager for Microsoft SQL Server

    Features benefits

    Analyzes SQL Server resource usage down to the SQL statement level and stores performance data directly in a SQL database.

    Provides proactive event management with robust fault management, detecting potential problems that could impact the availability of your SQL Servers.

    Allows for easy automation, letting you set up automatic event-driven notifications and corrective actions.

    Lowers support costs by enabling you to view the health and status of distributed SQL Servers from a central location.

    What’s monitored

    AppManager for Microsoft SQL Server includes out-of-the-box Knowledge Scripts that track simple or complex events and collect data for real-time or historical performance analysis. Examples of Knowledge Scripts include:

    Accessibility. Immediately alerts if the SQL Server or any specified database within the server is not accessible.

    BlockedProcesses. Detects when processes are blocked.

    EventLog. Detects when the SQL Server has written critical error messages to the Windows event log. Support is also available for monitoring SQL Server’s own log file.

    TopCPUUsers. Analyzes the CPU being consumed by SQL Server users and correlates CPU usage and SQL statements being executed by users.

    TopLockUsers. Identifies locking activity by user and identifies the SQL statements causing locks.

    TopMemoryUsers. Tracks memory usage by SQL Server users and correlates memory utilization to SQL statement activity.

    DBGrowthRate. Tracks the rate at which databases are growing.

    DBSpace. Tracks trends and raises alerts on the available space your SQL Server databases.

    LogSpace. Detects whether you are running low on SQL Server log space and provides an option to automatically truncate the transaction log file.

    RunSQL. Lets you invoke any SQL statement or stored procedure as a Knowledge Script or corrective action.

    NearMaxContent. Tells you whether available connections are running low.

    NearMaxLocks. Identifies whether SQL Server is almost out of locks.

    ServerThroughput. Measures key I/O statistics, such as transactions per second.

    ProcessingTime. Tracks the amount of time required to process a user-defined SQL statement.

    UserConnections. Tracks who connects into the SQL Server and how many connections each person is using.

    CacheHitRatio. Keeps watch of the SQL Server buffer cache, alerting you if you need to increase the memory allocated to SQL Server.

    Let’s Talk

    Welcome , Want to talk to someone? Call our Sales team or request a call and we’ll get right back to you.

    For support information, please visit Technical Support .

    • Request a Call

    SQL server databases stuck in restoring state – Database Administrators Stack Exchange


    #

    This is likely caused by the restore script adding the WITH NO RECOVERY parameter to make the database ready for a transaction log apply after the restore. The database is now waiting for the latest transaction log file.

    1. Apply the latest transaction log (Using RESTORE LOG. WITH RECOVERY ). or
    2. Restore the database again, but this time using WITH RECOVERY. or
    3. Force the database out of restoring mode by executing: RESTORE DATABASE YourDb WITH RECOVERY

    Before you do this, please make sure you understand the implications of these options. You may cause data loss if you are not careful.

    See this for details:

    When those don t work, see dba.stackexchange.com/questions/11175/ — again, LOTS OF WARNINGS OF DOOM if you don t know what you re doing and understand the full risk of data loss. But if you re in a bind on a non-production server, you ve got good backups to do a fresh restore from, and you can afford to restart SQL on said server, it works. NateJ Feb 26 ’16 at 1:18

    I just had this situation, and the cure was quite surprising:

    Apparently the NetBackup restore that broke left it in a weird state. No other solution worked (though I hadn’t yet tried restarting the SQL Server service)

    I’d be careful with the database, though, as theoretically once a restore starts, then fails, you could have corrupted data. I’m just going to restore the database again anyway, so it doesn’t matter to me.


    Understanding SQL Server Backup Types #backup #database #sql #server


    #

    Understanding SQL Server Backup Types

    Applies to: SQL Server 2000, SQL Server 2005

    Database backups are at the core of any SQL Server disaster recovery planning for any production system. Backups may be used to provide a means of recovery to a point-in-time when the database was last operational. Microsoft® SQL Server™ provides several types of backups that may be combined to formulate a customized disaster recovery plan depending on the nature of the data and the recovery requirements. It is highly recommended that all SQL Server databases be backed up periodically.

    SQL Server backup media

    A database may be backed up to disk or to tape. The examples in this article assume a disk backup directly into a disk file (as opposed to a disk backup device). Any database can be backed up to a random disk file at any time. The file may either be initialized (using WITH INIT) or appended with the new backup.

    Types of backups in SQL Server

    SQL Server provides several different kinds of backups including Complete. Differential. Transaction Log. andFile(s) and Filegroup(s) backup. A combination of these backups may be used to formulate a robust disaster recovery strategy. The following paragraphs explain each SQL Server backup type

    Complete database backup

    A complete database backup creates a stand-alone image of the entire database. A complete database backup is self-dependent and may be restored to either the same or a new database on the same or a different server. This provides plenty of flexibility at the time when this backup has to be restored. A complete backup may be restored without the need for any other kind of backup. It may also be performed for databases in any recovery model. Restoring a complete database backup typically would be considered a starting point for a disaster recovery situation where the entire database is lost or damaged. It is recommended that a complete database backup be performed at regular intervals for all production databases. It is also recommended that a complete backup should be performed for system databases if there are any changes performed to the SQL Server operating environment such as creating or removing databases, configuring security, creating and modifying DTS/SSIS packages or scheduled jobs, adding and removing linked servers, etc.

    BACKUP DATABASE Northwind
    TO DISK = c:backupsnorthwind.bak
    WITH INIT

    Restore syntax (Same database)

    RESTORE DATABASE Northwind
    FROM DISK = c:backupsnorthwind.bak

    Restore syntax (New database and/or server)

    RESTORE DATABASE Northwind_new
    FROM DISK = c:backupsnorthwind.bak
    WITH MOVE northwind TO c:new_locationNorthwind_new.mdf
    MOVE northwind_log TO c:new_locationNorthwind_new_log.ldf

    Differential database backup

    A differential backup backs up only modified extents since the last complete backup. An extent is a group of 8 data pages each consisting of 8 KB (64 KB in total). By definition, differential backups are cumulative. The most recent differential backup contains all changes from all previous differential backups performed since the most recent complete database backup. Differential backups may be considered as an alternative for databases that are large and are modified infrequently. These would include data warehouse type of databases. Differential backups have several limitations including the following:

    • They do not provide point-in-time restore capabilities
    • They may only be restored after a complete database backup is restored
    • They may not be performed on the master database

    BACKUP DATABASE Northwind
    TO DISK = c:backupsnorthwind_diff.bak
    WITH INIT, DIFFERENTIAL

    Restore syntax (Same database Note that a complete database backup is restored first using WITH NORECOVERY)

    RESTORE DATABASE Northwind
    FROM DISK = c:backupsnorthwind.bkp
    WITH NORECOVERY

    RESTORE DATABASE Northwind
    FROM DISK = c:northwind_diff.bkp
    WITH RECOVERY

    Transaction log backup

    An SQL Server database consists of two components: data file(s) and transaction log file(s). A transaction log captures the modifications made to the database. A simple transaction may place several records in the transaction log. Each of these records is known as a log record and is assigned a unique identification number known as the log sequence number (LSN). Log records that belong to the same transaction are linked together through the LSN. If SQL Server service shuts down unexpectedly, upon restart the recovery process examines the entries in the transaction log and if there are transactions that have not been rolled forward completely, the recovery process rolls back the changes performed as part of these incomplete transactions. This operation is extremely important as it forms the basis of transactional recovery. Entries in the transaction log are also used if transactional replication is configured for the specific database.

    A transaction log backup backs up all transactions since either the previous transaction log backup, or the complete database backup if there have been no transaction log backups performed for the database in the past. This backup may then be used to apply the backed-up changes, in case disaster recovery is required. Transaction log backups may only be applied to a database in an unrecovered state. A database may be in an unrecovered state if it is being restored from a set of backups as part of a disaster recovery procedure, or if it is configured as a standby database on a warm backup server. A transaction log backup also truncates the inactive portion of the transaction log, unless the database is configured as a Publisher in transactional replication and there are transactions pending propagation to Subscribers.

    Each transaction log backup contains a First and Last log sequence number (LSN). Consecutive transaction log backups should have sequential LSNs for the boundary log records. These LSN values may be examined using the RESTORE HEADERONLY command. If LastLSN from the previously restored transaction log backup does not match the FirstLSN from the backup that is currently being restored, the restore operation fails with the following error: This backup set cannot be restored because the database has not been rolled forward far enough. You must first restore all earlier logs before restoring this log . If the above message is generated while restoring a particular transaction log backup, which is part of a set of transaction log backups that are to be restored, any attempts to restore further transaction log backups will fail with this message. There could be several reasons for consecutive transaction log backups being out of sequence. Some of the most common reasons noted from support experience have been:

    • The database recovery model has been changed to Simple and back to either Full or Bulk-Logged. Switching the recovery mode to Simple causes the transaction log to be truncated
    • Another transaction log backup was performed between the previous successfully restored backup and the one generating this message
    • The transaction log was manually truncated between the two backups
    • The database was in Bulk-Logged recovery model and non-logged operations were performed
    • Transaction log backups are not allowed for databases in Simple Recovery model. While in Simple Recovery model, a database’s transaction log is truncated every time a CHECKPOINT is invoked for the database

    Transaction log backups provide the possibility of performing a point-in-time restore or point-of-failure restore. You can also perform a restore to a named transaction with transaction log backups.

    BACKUP LOG Northwind
    TO DISK = c:backupsnorthwind_log_1.bak
    WITH INIT

    Restore syntax (Same database Note that a complete database backup is restored first using WITH NORECOVERY, then a sequence of transaction log backups)

    RESTORE DATABASE Northwind
    FROM DISK = c:backupsnorthwind.bkp
    WITH NORECOVERY

    RESTORE LOG Northwind
    FROM DISK = c:northwind_log_1.bkp
    WITH RECOVERY

    File(s) and Filegroup(s) backup

    Use BACKUP to back up database files and filegroups instead of the full database when time constraints make a full database backup impractical. To back up a file instead of the full database, put procedures in place to ensure that all files in the database are backed up regularly. Also, separate transaction log backups must be performed. After restoring a file backup, apply the transaction log to roll the file contents forward to make it consistent with the rest of the database.