Set the recovery model to “SIMPLE” programmatically

No Comments

Written by Luis Miguel – Originally published on September 18, 2010.

Here is a tiny stored procedure to set the recovery model to SIMPLE on every user database in your instance.

CREATE PROCEDURE [dbo].[Dba_reset_recovery_model]
  DECLARE @db_name VARCHAR(128) -- Database name
  DECLARE @alterdb VARCHAR(512) -- Dynamic SQL command
    -- Get the USER DATABASES that ARE ONLINE and their recovery model IS NOT SIMPLE
    SELECT name
    FROM   sys.databases
    WHERE  database_id > 4
           AND [state] = 0
           AND [recovery_model] <> 3
  OPEN db_cursor
  FETCH NEXT FROM db_cursor INTO @db_name
        SET @alterdb = ‘ALTER DATABASE ’ + @db_name + ‘ SET RECOVERY SIMPLE’
        EXEC (@alterdb)
        FETCH NEXT FROM db_cursor INTO @db_name
  CLOSE db_cursor
  DEALLOCATE db_cursor 

1 – This script is known to run well in SQL Server 2008, I have not tested it in any other version of SQL Server.
2 – Do not use this script in a Production system without prior extensive testing in a lab environment.

Overview of the prerequisites for the installation of the SQL Server 2008 sample databases

No Comments

Written by Luis Miguel – Originally published on September 1st, 2010

As of this writing (Sept. 2010), the installation file is being hosted by Codeplex, the name of the setup file is AdventureWorks2008_SR4.exe (SR means “sample refresh”) and the link to download it is

Prerequisites for the installation:
1 – Full Text Search must be installed
2 – The Full-text Filter Daemon Launcher service must be running
3 – Filestream must be enabled

Follow the instructions listed below to confirm that the prerequisites have been fulfilled.

To check that Full Text Search was installed:
1 – Look for the file “ConfigurationFile.ini” somewhere below the path “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log”. There should be one or more folders with names that look like timestamps, i.e. 20081122_105904, start looking in the oldest folder
2 -Once you locate the file ConfigruationFile.ini, search for a string like this “FEATURES=SQLENGINE,REPLICATION,FULLTEXT,AS,RS,BIDS,CONN,IS,BC,BOL,SSMS,ADV_SSMS”. As you can see, Full Text is one of the features installed on this server

To check if the Full-text Filter Daemon Launcher service running
1 – Open Start – Programs – Microsoft SQL Server 2008 – Configuration Tools – Configuration Manager
2 – Select SQL Server Services from the left pane
3 – Look for an the Full-text Filter Daemon Launcher entry and make sure it says Running under the State column heading, like the screenshot below

To verify if Filestream is enabled
1 – Open Configuration Manager as explained above
2 – Select SQL Server Services in the left pane
3 – Right click on “SQL Server (MSSQLSERVER)”, select Properties and click on the FILESTREAM tab.

After you confirm that the server fulfills all the prerequisites all you need to do is run the installation program and follow the wizard.

If everything goes well and depending on your selections from the wizard, once the installation program has finished you should see some or all of these databases in your server

SQL Server 2008 Sample Databases

The purpose if the post is to help you quickly find out if the server is ready to run the setup program that installs the SQL Server 2008 sample databases. For the sake of brevity I cannot get into the details of how to resolve any issues if one or more of the requirements are not ready.

*This post applies to SQL Server 2008 RTM (release to market), there is already a newer version of SQL Server called SQL Server 2008 R2 and the instructions to install the sample databases for this new version might be different.

Powered by Netfirms