Sep 17
adminRecovery Model, T-SQL
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]
AS
DECLARE @db_name VARCHAR(128) -- Database name
DECLARE @alterdb VARCHAR(512) -- Dynamic SQL command
DECLARE db_cursor CURSOR FOR
-- 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
WHILE @@FETCH_STATUS = 0
BEGIN
SET @alterdb = ‘ALTER DATABASE ’ + @db_name + ‘ SET RECOVERY SIMPLE’
EXEC (@alterdb)
FETCH NEXT FROM db_cursor INTO @db_name
END
CLOSE db_cursor
DEALLOCATE db_cursor
Warnings:
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.
Sep 01
adminSample Databases
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 http://msftdbprodsamples.codeplex.com/releases/view/37109
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
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.
RSS
