Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

Monthly Archives: October 2010

I am often called upon to restore a copy of a live database to a development server. Taking a one-off backup in the normal manner would cause problems, as the sequence of LSNs (Log Sequence Numbers) would be disrupted on the production server. This means that subsequent log or differential backups would follow the one-off backup, rather than the file produced from the regular scheduled backup plan. Thankfully there is a solution….

Issuing a backup with the COPY_ONLY option stops the log sequence chain from being broken. Use the option as follows:-

BACKUP DATABASE [LiveDB]
TO DISK = 'E:\Backups\TestRestoreFile.bak'
WITH INIT,  NAME = 'Full Database Backup', COPY_ONLY,  STATS = 10

Unfortunately, it is not possible to select a COPY_ONLY backup through the Management Studio GUI. The backup command has to be issued through T-SQL. It is also not possible to restore through the interface. As you can see from the following screenshot, you can select the backup file, but the backup set will not appear in the list.

RestoreTestDB
To perform the restore, issue the following command, specifying the target location(s) for data and log files. Include REPLACE to overwrite any existing test database. I would also recommend you rename the files so that they now match the name of the test database, rather than the live database.

RESTORE DATABASE [TestDB]
FROM  DISK = 'E:\Backups\TestRestoreFile.bak'
WITH  FILE = 1,
MOVE 'LiveDB_data' TO 'E:\Data\TestDB.mdf',
MOVE 'LiveDB_log' TO 'E:\Logs\TestDB_log.ldf',
NOUNLOAD,  REPLACE,  STATS = 1
GO

If you now issue log backups on the production server, the LSNs will be in the correct sequence and the COPY_ONLY backup will effectively have been ignored.

Tags: , , , , , , ,

For this demo, you will require SQL Server with the AdventureWorks database and a copy of Windows Powershell. We intend to build up a number of UPDATE statements based on data within AdventureWorks.HumanResources.Employee. We want to give all 157 production technicians an extra ten vacation hours, but need an easy way of generating the many lines of SQL we require. First of all, we need to run the following query to return the employeeID of all employees who meet this criteria:-

USE AdventureWorks
SELECT EmployeeID
FROM HumanResources.Employee
WHERE Title LIKE 'Production Technician%'

With a fresh installation of the AdventureWorks database, this should return 157 results. Create a file on the root of C: called myfile.txt and copy and paste in the results using notepad or other text editor. You should have one EmployeeID per line.

Now we can build up our UPDATE queries by using Windows Powershell. Open Powershell and enter the following:-

get-content c:\myfile.txt | Foreach {"UPDATE HumanResources.Employee SET VacationHours = VacationHours + 10 WHERE EmployeeID = '"+$_+"';"} | out-file c:\output.txt

If you now check the newly created file (ouput.txt) on C: drive, you should have 157 UPDATE statements which you can copy and paste into SQL Server. Executing will give the production assistants their extra hours.

output.txt

Now to explain the powershell code…..

get-content c:\myfile.txt

simply reads the contents of our file. If you solely run this part of the command in Powershell, the contents of the file will be displayed on screen.

The main part of our command is the foreach loop which builds our SQL statements by adding our text before and after the contents of each row. The contents of each row is held in “$_

Foreach {"UPDATE HumanResources.Employee SET VacationHours = VacationHours + 10 WHERE EmployeeID = '"+$_+"';"}

The final part of our code simply writes the changes out to our output.txt file

out-file c:\output.txt

There are much more elegant ways of writing our SQL statement. We could of course, have written a single UPDATE statement and included,

WHERE Title LIKE 'Production Technician%'

…. but that would not have been as much fun and you would not have just learnt this handy Powershell tip!!

Tags: , , , , , , ,

Following on from my previous post on sp_help, the sp_helpdb stored procedure provides useful information at the database level.

Running sp_helpdb without specifying a database provides an overview of all databases within the instance.

sp_helpdb_resultsThe query returns the following information:-

name
database size
owner
database id
creation date
database status
compatibility level

By specifying a database,

sp_helpdb 'AdventureWorks' 

additional information is provided on that databases data files and log files:-

sp_helpdb

Tags: , , , , ,

sp_help is an invaluable inbuilt SQL Server procedure that returns information on a number of different objects. Here are some examples of its use:-

Executing

sp_help

without specifying a context returns a list of all objects within the current database. The name, owner and object type are returned. Additionally, any user defined data types within the database are listed:-

sp_help

Executing

sp_help tablename

returns information relating to the table. The first results set provides information relating to the table itself. The second results set provides information on the columns, such as data type and data length. Beneath that are information on identity columns, GUIDs, filegroup location, indexes, constraints and foreign key references:-

sp_help_table

Try running sp_help against other objects (indexes, columns, filegroups, constraints, procedures, functions) to see what useful information you can gather.

Tags: , , , ,

xp_cmdshell allows Windows CMD shell commands to be executed from within SQL Server. This allows a DBA to perform file and OS-level tasks. All the usual CMD tasks can be issued from within a T-SQL query window or from within a SQL Server Agent job step.

xp_cmdshell is disabled by default so needs to be enabled through the Surface Area Configuration utility. Once enabled, CMD commands can be entered and executed as follows:-

EXEC xp_cmdshell 'enter command here'

So, to rename a file, you can run:-

EXEC xp_cmdshell 'ren "C:\my_file.xml" "renamed.xml"'

A large number of commands can be issued. For a comprehensive list, see here.

Permissions can be problematic when running CMD commands from SQL Server. The user that executes xp_cmdshell must have the correct permissions to perform the required action. This means that the user configured to run the SQL Agent service will require relevant permissions if xp_cmdshell is run from a SQL Agent job. Alternatively, a proxy account with sufficient privileges can be configured.

If you do receive operating system errors relating to permissions, then check which Windows account is running the command by executing:-

EXEC xp_cmdshell 'whoami.exe'

Once the user account is known, steps can then be taken to resolve permissions based issues by granting elevated privileges.

Tags: , , , , ,

As you are probably aware, updating data in a table can be achieved with a relatively simple SQL statement. In the following example, the data we want to change is included in the statement itself:-

UPDATE employees
SET last_name = 'Smith'
WHERE employee_id = 6392

We feed in ‘Smith’ as the new data to be entered in the last_name column, and we specify ‘6329’ as the record to be updated. This works well, but is usually only useful for tasks where only a few records need to be updated, or an entire column needs to be updated with the same data (achieved by excluding the WHERE clause).

Much more useful is the ability to UPDATE data based on the contents of another table or data source. This allows us to update large amounts of data and ensures its integrity. There are two approaches for performing updates based on data from another table.

METHOD 1

The example below updates the primary_address in the employees table to that of the home_address in the addresses table. The WHERE clause ensures that the rows in the employees table are updated with the correct data from the addresses table by joining on the employee_id column, which is present in both tables.

UPDATE employees
SET e.primary_address = a.home_address
FROM employees e, addresses a
WHERE e.employee_id = a.employee_id

With this method, it is possible to update additional columns and to expand the WHERE clause to limit the records which are altered. In the following, we add an additional two columns to be updated and a sub-query to limit the update to those staff who have commenced employment within the last year:-

UPDATE employees
SET e.primary_address = a.home_address,
e.secondary_address = a.next_of_kin_address,
e.primary_telephone = a.home_telephone
FROM employees e, addresses a
WHERE e.employee_id = a.employee_id
AND e.employee_id IN
(SELECT employee_id
FROM employment_dates
WHERE [start_date] > DATEADD(YEAR, -1, GETDATE()))


METHOD 2

This approach uses a sub-query to select the home address from the addresses table. Don’t forget to include the WHERE EXISTS clause, followed by the repetition of the sub-query, otherwise you will receive errors – this is a common mistake!

UPDATE employees
SET e.primary_address =
(SELECT a.home_address
FROM addresses a
WHERE e.employee_id = a.employee_id)
WHERE EXISTS (SELECT a.home_address
FROM addresses a
WHERE e.employee_id = a.employee_id)

As you can see from this statement above, the second method requires more code and in my opinion is much more difficult to understand. Therefore, I tend to use the first method in most circumstances. An additional limitation is that it is not easy to update multiple columns. Oracle (and a number of other database management systems) allows multiple columns to be updated by specifying the columns within brackets:-

UPDATE employees
SET (e.primary_address, e.secondary_address, e.primary_telephone) =
(SELECT a.home_address, a.next_of_kin_address, a.home_telephone
FROM addresses a
WHERE e.employee_id = a.employee_id)
WHERE EXISTS (SELECT a.home_address, a.next_of_kin_address, a.home_telephone
FROM addresses a
WHERE e.employee_id = a.employee_id)

However, the above does not work with SQL Server (at least not in 2005)!

To achieve this with SQL Server, you would need to write the query for each column you want to update.

UPDATE employees
SET e.primary_address =
(SELECT a.home_address
FROM addresses a
WHERE e.employee_id = a.employee_id)
WHERE EXISTS (SELECT a.home_address
FROM addresses a
WHERE e.employee_id = a.employee_id),
e.secondary_address =
(SELECT a.next_of_kin_address
FROM addresses a
WHERE e.employee_id = a.employee_id)
WHERE EXISTS (SELECT a.next_of_kin_address
FROM addresses a
WHERE e.employee_id = a.employee_id),
e.primary_telephone =
(SELECT a.home_telephone
FROM addresses a
WHERE e.employee_id = a.employee_id)
WHERE EXISTS (SELECT a.home_telephone
FROM addresses a
WHERE e.employee_id = a.employee_id)

NOT NICE! And this does not even include the clause to limit the updates to recently employed staff.

Tags: , , , ,

Being able to replace characters in a string of text can be useful in solving a variety of problems. Thankfully SQL Server provides a REPLACE command which allows for sections of a string to be replaced with another set of characters.

Here is a simple example to demonstrate how REPLACE works:-

 SELECT REPLACE('My name is Fred', 'Fred', 'Frank')

Replace looks at the text string and replaces any instances of ‘Fred’ with ‘Frank’. Therefore, the output of the query will read:-

My name is Frank

When used in conjunction with an UPDATE command, we can use REPLACE to alter characters within a column. In this example we are changing the monthly salary of all staff from British pounds to US dollars. We are not converting the salaries, but simply changing the ‘£’ to a ‘$’, so in fact everyone is receiving a pay cut!

UPDATE salary_table
SET monthly_salary = REPLACE(monthly_salary, '£', '$')

Instead of applying the change to all members of staff, what we should do is use a WHERE clause to ensure the REPLACE only affects those staff who should be getting paid in dollars:-

UPDATE salary_table
SET monthly_salary = REPLACE(monthly_Salary, '£', '$')
WHERE nationality = 'American'

If we check the salary_table now, then we will find that records such as,

Bob Jones            American             £2,500

will now read as follows,

Bob Jones            American             $2,500

Tags: , , , , ,

%d bloggers like this: