SQL Geeks Indonesia

Database Trouble Shooting, how to recover suspect database in SQL

When the database status is suspected, it might be from trouble of Infra structure or hardware that impacted to SQL Server database performance.

Here are some step by step to recover database that have been suspended by the system

1.       Look in the SQL Server Logs in Enterprise Manager(EM), There should be multiple logs starting with Current and then followed by 6 or more archived logs.

2.       Review all recent Error Log(s). There WILL be an indication here as to why the database has been marked suspect.

3.       When the problem has been fixed and you're either sure that the data is going to be ok, or you have no backup anyway, so you've nothing to lose, then change the database status to normal and restart SQL Server.

4.       To change the database status we will us the following store procedure: sp_resetstatus.
The steps are as follows:

5.       Change database status to allow update

6.       After the procedure is run, immediately disable updates to the system tables:

1.     Issue the following command to put the database into emergency mode:

   a.    USE master
        GO
        sp_configure 'allow updates', 1
        GO
        RECONFIGURE WITH OVERRIDE
        GO
 
   b.    UPDATE master..sysdatabases
        SET status = 32768
        WHERE name = 'DatabaseName'

2.     Stop and Restart ALL SQL Server Services.

We are now ready to pull whatever data we can out of the tables in the corrupt database. Remember, some tables may be corrupt, thus you may have to play with various T-SQL statements to get the data out. First try DTS

Share this post: | | | |

SQL Server Monthly Meeting - Feb 10

Share this post: | | | |

Database Unit testing with SQL Server 2005

As you seen on my previous articles in my blog,  Unit testing is very importan in software development. But people still not aware about this because developer commonly only focus on how to develop the application and deprioritize the quality of code it self. Unit testing will make sure system behaviour and response from every parameter input that given by user will compliant with the function spesification.

To help developer doing unit testing in stabilizing phase, Visual Studio team system for database already have capabilty to create and develop module for unit testing.

The framework in Microsoft Visual Studio 2005 Team Edition for Database Professionals (DB Pro) for database unit testing, however, offers some important value-add features above and beyond those just mentioned. The first of these is automatic generation of SQL script stubs of unit tests for stored procedures, functions, and triggers. This feature saves you the hassle of developing the boilerplate code that you see earlier in this paper.

Secondly, DB Pro provides a set of built-in test conditions to help you verify your test results. These test conditions perform the most common validation that you would want to verify, including the rows returned, scalar values, and execution time. You can easily configure these test conditions through the user interface.

And, most importantly, DB Pro offers a way for you to set the database state by using the data-generation feature to populate the database with test data before running your database tests. The beauty of the data-generation functionality is that you can repeatedly generate the same test data based on a seed value, making it repeatable and thus very applicable for unit testing.

We will explore each of these features in this paper and how best to leverage them in implementing your process for database unit testing.

Authoring Your First Database Unit Test

To get you started, the following simple walkthrough shows how you can create a database unit test for the CustOrderHist stored procedure in the Northwind database.

1. Ensure that Team Edition for Database Professionals is installed on your computer.
2. Open Microsoft Visual Studio.
3. On the Test menu, click New Test.
4. In the Add New Test dialog box, click Database Unit Test, and pick the type of test project that you want to create (either C# or Visual Basic).

   

   Figure 1. Add New Test dialog box

5. Specify a name for the new test project.

   

   Figure 2. New Test Project dialog box

6. Specify the database connection against which to run the test, and click OK.

   

   Figure 3. Database test configuration

   The Database Unit Test Designer appears.

   

   Figure 4. Database Unit Test Designer (Click on the picture for a larger image)

7. Add a test method by clicking the plus sign (+) on the top of the designer.

   

   Figure 5. Adding new database unit test

8. Name the new test, and click OK.

   

   Figure 6. Naming new database unit test

9. Add the following T-SQL to the main editor window in the designer:

   DECLARE @CustomerId nchar(5)
   SELECT @CustomerId = 'EASTC'
   EXEC dbo.CustOrderHist @CustomerId
   

10. Click the inconclusive test condition in the Test Conditions panel, in the bottom half of the designer. Click the red "x" button to delete the test condition.

    

    Figure 7. Test Conditions panel

11. Add a row-count test condition by clicking Row Count in the Test Conditions list and clicking the + button.

    

    Figure 8. Adding row-count test condition

12. In the Properties window, set the number of expected rows to 19.

    

    Figure 9. Configuring test-condition properties

13. On the Test menu, point to Windows, and click Test View.

    

    Figure 10. Running test from Test View

14. Right-click the test, and click Run Selection.
15. Review the results in the Test Results window.

    

    Figure 11. Viewing test results

And your test passed!

You have just successfully created your first database unit test. Let's now drill into the details of the various phases of database unit testing.

source : http://msdn.microsoft.com/en-us/library/bb381703(VS.80).aspx#dtbunttsttedp_topic1

Share this post: | | | |

Database Unit Testing, is it sound familiar for you?

have you heard about Database Unit Testing?

Unit testing is a well-understood concept in application development, but the database community has not yet embraced the advantages and strategies of this approach. Therefore, I'd like to start by exploring the fundamental tenets of the unit-testing methodology. Unit testing provides a structured and automated way of testing individual components of a system. Unit tests are most often authored by the developer of the component that is being tested. Each unit test tests a specific module of the code in an isolated fashion to ensure that the component behaves as expected.

How does this all relate to database development? The direct analog of application unit tests in the database world are tests of a database's programmability objects. These objects include, for example, a database's stored procedures, functions, and triggers.

What might a unit test for a stored procedure look like? Let's say that you are trying to test the CustOrderHist stored procedure in the Northwind database. The stored procedure should return the order history for a given customer ID. To test this behavior, you can imagine writing a SQL script that executed the stored procedure and then verified whether the expected number of rows was returned. Such a script might resemble the following:

DECLARE @CustomerId nchar(5)
SELECT @CustomerId = 'EASTC'
EXEC dbo.CustOrderHist @CustomerId

IF (@@ROWCOUNT <> 19)
RAISERROR('Actual Rowcount not equal to expected 19',11,1)

As a methodology, unit testing has many advantages over manual, ad-hoc testing and debugging. By developing database unit tests, you can create a collection of tests and run them during development to ensure that your features work as you expect. Because each unit test focuses specifically on an individual method, you can more easily determine the source of a failure for a failing unit test. Therefore, database unit tests help you determine the sources of bugs in your code.

Such a collection of tests is very useful for regression testing. As you implement new features, you can rerun existing tests to ensure that existing functionality has not been broken. Such a regression test suite facilitates database changes, because you can now make changes knowing the implications of those changes.

Unit tests, in addition, serve as documentation for users of the methods under test. Developers can quickly review unit tests to determine exactly how particular components should be consumed.

Database unit testing is not limited merely to testing the database's programmability objects. You might want to author the four classes of tests that this section describes.

Feature Tests

The first and likely most prevalent class of database unit test is a feature test. In my mind, feature tests test the core features—or APIs, if you will—of your database from the database consumer's perspective. Testing a database's programmability objects is the mainline scenario here. So, testing all the stored procedures, functions, and triggers inside your database constitute feature tests in my mind. To test a stored procedure, you would execute the stored procedure and verify that either the expected results were returned or the appropriate behavior occurred. However, you can test more than just these types of objects. You can imagine wanting to ensure that a view, for example, return the appropriate calculation from a computed column. As you can see, the possibilities in this realm are large.

Schema Tests

One of the most critical aspects of a database is its schema, and testing to ensure that it behaves as expected is another important class of database unit tests. Here, you will often want to ensure that a view returns the expected set of columns of the appropriate data type in the appropriate order. You might want to ensure that your database does, in fact, contain the 1,000 tables that you expect.

Security Tests

In today's day and age, the security of the data that is stored within the database is critical. Thus, another important class of database unit tests are those that test the database security. Here, you will want to ensure that particular users exist in your database and that they are assigned the appropriate permissions. You will often want to create negative tests that attempt to retrieve data from restricted tables or views and ensure that the access is appropriately denied.

Stock-Data Tests

Many databases contain stock data, or seed data. This data changes infrequently and is often used as lookup data for applications or end users. ZIP codes and their associated cities and states are great examples of this kind of data. Therefore, it is useful to create tests to ensure that your stock data does, in fact, exist in your database.

 source : http://msdn.microsoft.com/en-us/library/bb381703(VS.80).aspx#dtbunttsttedp_topic1

Share this post: | | | |