Thursday, May 20, 2010

Finding Stored Procedures, User Defined Functions, and Views to Change

In some cases, we need to make changes to our table structure. One of the challenges that might arise when making changes to table structure is that we need to make sure to review the database objects (stored procedures, views, and user defined functions) that might be affected by the table structure changes that we made. If there is a good documentation in place, we might be able to find the affected database objects pretty easily (this is a good case for having a good design/technical documentation). But since that's not always the case, we need to have a way to find the list of potentially affected database objects.

SQL Management Studio


On our SQL Server Management Studio, we can identify the database objects (stored procedures, views, constraints and user defined functions) associated with a particular table pretty easily. To do that, open SQL Server Management Studio, and on the Object Explorer window, right click on the table that you are going to modify. On the context menu, select View Dependencies. It should open a window that list all the database objects that depend on that particular table.
SSMS View Dependencies

Stored Procedures/ Functions


There are also stored procedure/functions that you can use to find the object dependencies. If you are still using SQL Server 2005, you can use sp_depends stored procedure. It is very easy to use. For example let say that you want to find the list of database objects that depend on HumanResources.Employee table in AdventureWorks database. You can use the following:

USE [AdventureWorks];   
EXEC sp_depends 'HumanResources.Employee';

If you are using SQL Server 2008, you can use the sys.dm_sql_referencing_entities.

Alternatives


For most part, using SQL Management Studio or sp_depends or sys.dm_sql_referencing_entities should be sufficient. However, in situation where we are using dynamic SQL in our stored procedure, we will need a different approach. One such approach would be to do a text search on the database object definition.

Back to the example of finding the list of database objects that depends on HumanResources.Employee table in AdventureWorks database, we can try to use the following script:

USE [AdventureWorks];   
SELECT    B.Name AS ObjectName, B.Type_Desc AS ObjectType
FROM    sys.sql_modules A
    INNER JOIN sys.objects B ON A.Object_ID = B.Object_ID
WHERE    REPLACE(REPLACE(A.[Definition],'[', ''), ']', '') LIKE '%HumanResources.Employee%';

The above script has its own limitation. Since it uses wildcard text search, it might return more database objects than it really needs. In our example, it might also returns database objects that contains HumanResources.EmployeeAddress, HumanResources.EmployeeDepartmentHistory, etc.

Another problem that might arise when trying to find object dependencies is that if we use the View Dependencies on SQL Management Studio Object Explorer, or sp_depend, we can only see the object dependencies limited to that one particular database. It could happen that you have a tool database, and within that tool database you have a stored procedure that uses the table that you want to modify. So we need to have a way to search across databases. One way to do that, we can use the following script:

DECLARE @ObjectDependencies TABLE (ObjectName VARCHAR(255), ObjectType VARCHAR(255), DatabaseName VARCHAR(255)) 

INSERT INTO @ObjectDependencies
EXEC sp_msforeachdb 'SELECT B.Name AS ObjectName, B.Type_Desc AS ObjectType, ''?'' AS [Database] FROM ?.sys.sql_modules A INNER JOIN ?.sys.objects B on A.Object_ID = B.Object_ID WHERE REPLACE(REPLACE(A.[Definition],''['', ''''), '']'', '''') LIKE ''%HumanResources.Employee%'''

SELECT    ObjectName, ObjectType, DatabaseName
FROM    @ObjectDependencies

Third Party Tools


There are also some third-party tool that you can use to search for text on database objects (as a way to find object dependencies). One of the tool that I have been using is SQL Digger (http://www.sqldigger.com). It was pretty neat tool that can be use to search text on database object.

Saturday, May 15, 2010

SharePoint Designer

If you are working with SharePoint Services or Microsoft Office SharePoint Server (MOSS), chances are that you have been working with SharePoint Designer.

SharePoint Designer can be use to push new pages or contents to SharePoint sites. In addition, we can also obtained SharePoint usage stats from SharePoint Designer. It can also be use to edit HTML, ASP.NET, CSS, XSL, etc. It is not as powerful as Visual Studio, but it should do a pretty decent job. It has a pretty decent What-You-See-Is-What-You-Get (WYSIWYG) editor. In term of its look and feel, SharePoint Designer is pretty similar to its predecessor, Microsoft FrontPage. The best part is that SharePoint Designer has been made available by Microsoft for free. Below are the links to Microsoft site to download SharePoint designer:


SharePoint Designer 2007
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=baa3ad86-bfc1-4bd4-9812-d9e710d44f42


SharePoint Designer 2010 (32-Bit)
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=d88a1505-849b-4587-b854-a7054ee28d66


SharePoint Designer 2010 (64-Bit)
http://www.microsoft.com/downloads/details.aspx?familyid=566D3F55-77A5-4298-BB9C-F55F096B125D&displaylang=en

Some notes:

  • SharePoint Designer 2010 RTM (Release to Market) has just come out last month. It is no longer beta version.
  • Unlike SharePoint Designer 2007, which only have the 32-bit version, SharePoint Designer 2010 has 32-bit and 64-bit versions.
  • According to the SharePoint Designer team blog site, we can have a side by side installation of SharePoint Designer 2007 and SharePoint Designer 2010.
  • Also to my understanding, SharePoint Designer 2010 can not be use for MOSS 2007. So you still need to use SharePoint Designer 2007 to manage MOSS 2007.

If you are interested to learn more about SharePoint Designer, and also get development news in regards to SharePoint Designer, you might want to check out the Microsoft's SharePoint Designer blog. Microsoft's SharePoint Designer blog can be found under the following URL: http://blogs.msdn.com/sharepointdesigner/.

Saturday, May 8, 2010

Last Sunday of the Month

Copy_Only option was introduced as one of the backup option starting with SQL Server 2005. It is a pretty useful backup option that I have been using it for a while now. When you back up your database or log with Copy_Only option, it will back up the database or log, and more importantly it will not disturb the backup sequence/routine that you might already have.

How is this useful?

Let say that you have established a routine in which you do a full backup on your database every once a week on Monday morning at 7:00 AM. You also have set up a differential backup to be done once a day at 8:00 PM. Then come Wednesday morning, your application vendor/developer manager is asking for the latest backup of your database to help to troubleshoot an application problem.

What to do?

There are several things that you can do. First you can immediately take a differential backup, and send the Monday’s full database backup and the newly created differential backup to your application vendor/developer manager.

Alternatively, you can also immediately take a full database backup, and send that full database backup to your application vendor/developer manager. However, one thing that you need to do is you need to make sure that you keep that full database backup. It is because the subsequent differential backups will depend on that full database backup that you’ve just take. (I guess a little bit of refresher is in order here. Differential backup contains changes that happen since the last full backup.) It’s just one more thing to worry about.

This is where the Copy_Only option can come in handy. In this scenario, you can use the Copy_Only option when taking the full database backup. The advantage of this is that this will not affect the subsequent differential backups. Those differential backups will still be based on the Monday’s full backup that you have. Let say that on Friday morning, you encountered a data corruption on that database. And you want to restore up to the Thursday differential backup. You can just restore the database from the Monday’s full backup and then restore the Thursday differential backup, and you should be golden.

Demonstration (Without Copy_Only option)

For this demonstration I am using AdventureWorks database. Let say that I have the following backup routine:

  • Full Backup #1
  • Differential Backup #1
  • Differential Backup #2
  • Full Backup #2
  • Differential Backup #3

I can use the following script:

USE [master];

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Full_Backup_1.bak'
WITH NAME = 'Full Backup #1';

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\ Backup\AW\Diff_Backup_1.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #1'

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\ Backup\AW\Diff_Backup_2.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #2'

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\ Backup\AW\Full_Backup_2.bak'
WITH NAME = 'Full Backup #2';

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\ Backup\AW\Diff_Backup_3.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #3'

Now, if we try to restore the database from Full Backup #1 and then restore the Differential Backup #3, using the following script:

USE [master];

RESTORE DATABASE AW_Restore
FROM DISK = 'C:\Backup\AW\Full_Backup_1.bak'
WITH NORECOVERY,
MOVE 'AdventureWorks_Data' TO 'C:\Data\AW\AdventureWorks_Data.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Data\AW\AdventureWorks_Log.ldf';

RESTORE DATABASE AW_Restore
FROM DISK = 'C:\Backup\AW\Diff_Backup_3.bak'
WITH RECOVERY;

As expected, we will get the following error:

This differential backup cannot be restored because the database has not been restored to the correct earlier state.

Now, let’s look at why this is happening. Try running the following query:

SELECT name, type, is_copy_only, first_lsn, differential_base_lsn
FROM msdb..backupset
WHERE database_name = 'AdventureWorks'
ORDER BY backup_start_date ASC

The result is as follow (you might get a different set of result):

name

type

is_copy_only

first_lsn

differential_base_lsn

Full Backup #1

D

0

260000000007500037

NULL

Differential Backup #1

I

0

260000000009700034

260000000007500037

Differential Backup #2

I

0

260000000011200001

260000000007500037

Full Backup #2

D

0

260000000011600037

NULL

Differential Backup #3

I

0

260000000013700034

260000000011600037

 

You will notice that the Full Backup #1 has first_lsn of 260000000007500037. (LSN is Log Sequence Number). And if you noticed on the differential_base_lsn, the Differential Backup #1 and Differential Backup #2 are also having the same LSN of 260000000007500037. This would indicate that the Differential Backup #1 and Differential Backup #2 are based on Full Backup #1. You will need Full Backup #1 in order to restore from Differential Backup #1 or Differential Backup #2.

Notice also that once you take the Full Backup #2, the subsequent differential back up (Differential Backup #3), has a different base LSN than the Differential Backup #1 or Differential Backup #2. In order to restore from Differential Backup #3, you need Full Backup #2.

Demonstration (With Copy_Only Option)

Let say that I have the following backup routine:

  • Full Backup #1
  • Differential Backup #1
  • Differential Backup #2
  • Full Backup #2 (With Copy_Only Option)
  • Differential Backup #3

I can use the following script:

USE [master];

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Full_Backup_1.bak'
WITH NAME = 'Full Backup #1';

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Diff_Backup_1.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #1'

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Diff_Backup_2.bak'
WITH DIFFERENTIAL, NAME = 'Differential Backup #2'

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Full_Backup_2.bak'
WITH NAME = 'Full Backup #2 (With Copy_Only)', COPY_ONLY;

BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backup\AW\Diff_Backup_3.bak
WITH DIFFERENTIAL, NAME = 'Differential Backup #3'

Now, if we try to restore the database from Full Backup #1 and then restore the Differential Backup #3, using the following script:

USE [master];

RESTORE DATABASE AW_Restore
FROM DISK = 'C:\Backup\AW\Full_Backup_1.bak'
>WITH NORECOVERY,
MOVE 'AdventureWorks_Data' TO 'C:\Data\AW\AdventureWorks_Data.mdf',
MOVE 'AdventureWorks_Log' TO 'C:\Data\AW\AdventureWorks_Log.ldf';

RESTORE DATABASE AW_Restore
FROM DISK = 'C:\Backup\AW\Diff_Backup_3.bak'
WITH RECOVERY;

It went successfully. So what happen? Let’s try running the following query:

SELECT name, type, is_copy_only, first_lsn, differential_base_lsn
FROM msdb..backupset
WHERE database_name = 'AdventureWorks'
ORDER BY backup_start_date ASC

Here’s the result:

name

type

is_copy_only

first_lsn

differential_base_lsn

Full Backup #1

D

0

260000000015600037

NULL

Differential Backup #1

I

0

260000000017800034

260000000015600037

Differential Backup #2

I

0

260000000019300001

260000000015600037

Full Backup #2 (With Copy_Only)

D

1

260000000019500001

NULL

Differential Backup #3

I

0

260000000019700001

260000000015600037

As you can see, unlike a regular full database backup, when we use the Copy_Only option, the subsequent differential (Differential Backup #3) is still based on the Full Backup #1. Looking at the differential_base_lsn column, you can see that.

Take Away

You can use Copy_Only option with your backup for that “one off” situation, where you need to take a backup without disturbing your routine backup strategy.

Some Notes

  • You can use Copy_Only option when you are doing full database backup or transaction log backup. You can not do it with the differential backup. If you put the Copy_Only option when you do the differential backup, SQL Server will ignore the Copy_Only option.
  • You can not use the full backup that you take with the Copy_Only option as the base to restore subsequent differential backup.
  • However, you can use the full backup that you take with the Copy_Only option as the base to restore subsequent log backup(s).