Restoring SQL Server 2005 Suspect Database

I had a SQL Server 2005 database in suspect mode,so i couldn't work on transaction until i repaired,while i am searching on the internet to resolve this problem i found an new Database status called Emergency introduced in SQL Server 2005.
This mode can change the database from Suspect mode to Emergency mode, so that you can retrieve the data in read only mode.
Please follow the following steps to change the database status from suspect to emergency than to it's normal state:

EXEC sp_resetstatus 'DBname'

ALTER DATABASE DBname SET EMERGENCY

DBCC checkdb('DBname')

ALTER DATABASE DBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('DBname', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE DBname SET MULTI_USER

I hope that will help you to get your database working

FTP Put file using t-sql

I have to use a stored procedure which will send/Receive text file
from/to a server by using FTP.

So i googled on the net and i found a very interesting stored procedure written by Nigel Rivett
that will upload a file from the database.
But before using this stored procedure on SQL Server 2005,XP_CMDSHELL must be enabled,
because it's disabled by default on SQL Server 2005 for security reasons.
There are two ways to enable this option: Surface Area Configuration tool or sp_configure.
Enabling this option via Surface Area Configuration tool is rather straight forward.
Here is the instructions to enable xp_cmdshell in SQL Server 2005 via Surface Area Configuration tool:

1. Click the Start button.

2. Select All Programs.

3. Navigate to the Microsoft SQL Server 2005 folder.

4. On the flyout, mouseover Configuration Tools.

5. Select SQL Server Surface Area Configuration.

6. The SQL Server 2005 Surface Area Configuration window will appear.

7. At the bottom of the window, select Surface Area Configuration for Features.

8. In the left pane, under Database Engine, select xp_cmdshell.

9. Check the Enable xp_cmdshell checkbox.

10. Click OK.

11. Close the SQL Server 2005 Surface Area Configuration window.

Here is the script to enable xp_cmdshell in SQL Server 2005 via sp_configure:

EXEC master.dbo.sp_configure 'show advanced options', 1

RECONFIGURE

EXEC master.dbo.sp_configure 'xp_cmdshell', 1

RECONFIGURE


-- FTP Put file using t-sql.
-- Author Nigel Rivett

This needs a work directory to create a FTP command file to execute.
In a multi-user system include the spid in the filename to make it unique for the connection.


if exists (select * from sysobjects where id = object_id(N'[dbo].[s_ftp_PutFile]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_ftp_PutFile]
GO


Create procedure s_ftp_putfile
@FTPServer varchar(128) ,
@FTPUser varchar(128) ,
@FTPPWD varchar(128) ,
@FTPPath varchar(128) ,
@FTPFileName varchar(128) ,
@SourcePath varchar(128) ,
@SourceFile varchar(128) ,
@workdir varchar(128)
as


declare @cmd varchar(1000)
declare @workfilename varchar(128)

/* usage:
exec s_ftp_putfile
@FTPServer = 'ftpserver' ,
@FTPUser = 'user' ,
@FTPPWD = 'password' ,
@FTPPath = 'path/' ,
@FTPFileName = 'test.txt' ,
@SourcePath = 'c:\' ,
@SourceFile = 'test.txt' ,
@workdir = 'c:\temp\'
*/

select @workfilename = 'ftpcmd.txt'

-- deal with special characters for echo commands
select @FTPServer = replace(replace(replace(@FTPServer, '', '^'),'<','^<'),'>','^>')
select @FTPUser = replace(replace(replace(@FTPUser, '', '^'),'<','^<'),'>','^>')
select @FTPPWD = replace(replace(replace(@FTPPWD, '', '^'),'<','^<'),'>','^>')
select @FTPPath = replace(replace(replace(@FTPPath, '', '^'),'<','^<'),'>','^>')

select @cmd = 'echo ' + 'open ' + @FTPServer
+ ' > ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPUser
+ '>> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + @FTPPWD
+ '>> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'put ' + @SourcePath + @SourceFile + ' ' + @FTPPath + @FTPFileName
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd
select @cmd = 'echo ' + 'quit'
+ ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @cmd

select @cmd = 'ftp -s:' + @workdir + @workfilename

create table #a (id int identity(1,1), s varchar(1000))
insert #a
exec master..xp_cmdshell @cmd

select id, ouputtmp = s from #a

go

Converting the database mirroring from High Availability to High Performance

In the previous article i showed you how to configure database mirroring using the high availability configuration through a SQLCMD master script.
What if we want to change the configuration from high availability mode to high performance mode,we will need to clean the configuration and reconfigure it again?
The answer is no,we can convert the configuration.
All what we need to do is to set both the witness and the safety off

-- ConvertToHighPerf.sql

/*========================================================
File: ConvertToHighPerf.sql

Summary: Change the High Availability configuration
over to High Performance.
Turn off the witness, turn off safety!

Date: August 2007
==========================================================*/

:SETVAR PrincipalServer MSHEHADEHVPC\SQLDev01
:SETVAR MirrorServer MSHEHADEHVPC\SQLDev02
:SETVAR Database2Mirror AdventureWorksDW
go

:ON ERROR EXIT
go

:CONNECT $(PrincipalServer)

ALTER DATABASE $(Database2Mirror)
SET WITNESS OFF
go

ALTER DATABASE $(Database2Mirror)
SET SAFETY OFF
go

SELECT DATABASEPROPERTYEX(N'$(Database2Mirror)', N'Status')
-- Returns ONLINE
SELECT db_name(sd.[database_id]) AS [Database Name],
sd.mirroring_guid,
sd.mirroring_state,
sd.mirroring_state_desc,
sd.mirroring_partner_name,
sd.mirroring_witness_name,
sd.mirroring_witness_state,
sd.mirroring_witness_state_desc,
sd.mirroring_role,
sd.mirroring_role_desc,
sd.mirroring_role_sequence,
sd.mirroring_safety_level,
sd.mirroring_safety_level_desc,
sd.mirroring_safety_sequence,
sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')
go

:CONNECT $(MirrorServer)
SELECT DATABASEPROPERTYEX(N'$(Database2Mirror)', N'Status') -- Returns RESTORING
SELECT db_name(sd.[database_id]) AS [Database Name],
sd.mirroring_guid,
sd.mirroring_state,
sd.mirroring_state_desc,
sd.mirroring_partner_name,
sd.mirroring_witness_name,
sd.mirroring_witness_state,
sd.mirroring_witness_state_desc,
sd.mirroring_role,
sd.mirroring_role_desc,
sd.mirroring_role_sequence,
sd.mirroring_safety_level,
sd.mirroring_safety_level_desc,
sd.mirroring_safety_sequence,
sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')
go

Configuring and Implementing Database Mirroring using SQLCMD

This article configures a database (uses AdventureWorksDW) for database mirroring – using the high availability configuration through a SQLCMD master script. SQLCMD replaces OSQL in SQL Server 2005 for better command-line automation by offering many new features including parameterization and ability to connect to different servers – all within one script. The true power can be seen by the fact that you can create complex multi-server scripts all within one master script. This script contains the steps necessary to setup mirroring through Transact-SQL. You will execute this script through the SQLCMD mode supported in SQL Server Management Studio. Please be sure to ONLY review the commands and their syntax, DO NOT EXECUTE any of the steps until the end – without the parameters set properly, this script will not execute correctly. This configuration is the High Availability configuration.

The below script (Database2Mirror.sql) is a SQLCMD script and by default, SQLCMD mode is not enabled. Enable SQLCMD command mode by choosing SQLCMD Mode from the Query menu (make sure to remember the icon shown in the drop-down menu since you could choose to use this instead). You can also toggle this mode off and on by using the SQLCMD mode icon in the toolbar. If you’re not sure of what an icon is, remember to use tool tips by hovering over an icon. Once you’ve enabled SQLCMD Mode, notice how your script changes. All SQLCMD lines are highlighted in gray.

-- Database2Mirror.sql

/*=============================================
File: Database2Mirror.sql

Summary: Setup the database for Mirroring - High Availability.

Date: August 2007

===============================================*/

:SETVAR PrincipalServer MSHEHADEHVPC\SQLDev01
:SETVAR MirrorServer MSHEHADEHVPC\SQLDev02
:SETVAR WitnessServer MSHEHADEHVPC\SQLExpress
:SETVAR Database2Mirror AdventureWorksDW
go

:ON ERROR EXIT
go

:CONNECT $(PrincipalServer)

-- Mirroring ONLY supports the FULL Recovery Model
ALTER DATABASE $(Database2Mirror)
SET RECOVERY FULL
go

USE $(Database2Mirror)
go

CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5091)
FOR DATABASE_MIRRORING (ROLE=ALL)
GO

:CONNECT $(MirrorServer)

CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5092)
FOR DATABASE_MIRRORING (ROLE=ALL)
GO

:CONNECT $(WitnessServer)

CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5090)
FOR DATABASE_MIRRORING (ROLE=WITNESS)
GO

:CONNECT $(PrincipalServer)

BACKUP DATABASE $(Database2Mirror)
TO DISK = 'C:\MSHEHADEHVPC\Mirroring\$(Database2Mirror).bak'
WITH INIT
GO

:CONNECT $(MirrorServer)

DECLARE @InstanceName sql_variant,
@InstanceDir sql_variant,
@SQLDataRoot nvarchar(512),
@ExecStr nvarchar(max)

SELECT @InstanceName = ISNULL(SERVERPROPERTY('InstanceName'), 'MSSQLServer')

EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL',
@InstanceName, @InstanceDir OUTPUT

SELECT @ExecStr = 'EXECUTE master.dbo.xp_regread '
+ '''HKEY_LOCAL_MACHINE'', '
+ '''SOFTWARE\Microsoft\Microsoft SQL Server\'
+ convert(varchar, @InstanceDir)
+ '\Setup'', ''SQLDataRoot'', @SQLDataRoot OUTPUT'

EXEC master.dbo.sp_executesql @ExecStr
, N'@SQLDataRoot nvarchar(512) OUTPUT'
, @SQLDataRoot OUTPUT

IF @SQLDataRoot IS NULL
BEGIN
RAISERROR ('Did not find the correct SQL Data Root Directory. Cannot proceed. Databases backed up but not yet restored.', 16, -1)
END

CREATE TABLE #BackupFileList
( LogicalName sysname NULL
, PhysicalName sysname NULL
, [Type] char(1)
, FileGroupName sysname NULL
, Size bigint
, MaxSize bigint
, FileId smallint
, CreateLSN numeric(25,0)
, DropLSN numeric(25,0)
, UniqueId uniqueidentifier
, ReadOnlyLSN numeric(25,0)
, ReadWriteLSN numeric(25,0)
, BackupSizeInBytes bigint
, SourceBlockSize bigint
, FileGroupId smallint
, LogGroupGUID uniqueidentifier
, DifferentialBaseLSN numeric(25,0)
, DifferentialBaseGUID uniqueidentifier
, IsReadOnly bit
, IsPresent bit
)

INSERT #BackupFileList
EXEC('LOAD FILELISTONLY FROM DISK = ''C:\MSHEHADEHVPC\Mirroring\$(Database2Mirror).bak''')

UPDATE #BackupFileList
SET PhysicalName
= @SQLDataRoot
+ N'\Data\'
+ REVERSE(SUBSTRING(REVERSE(PhysicalName)
, 1, PATINDEX('%\%', REVERSE(PhysicalName)) -1))

DECLARE @LogicalName sysname
, @PhysicalName sysname

DECLARE FileListCursor CURSOR FAST_FORWARD FOR
SELECT LogicalName, PhysicalName
FROM #BackupFileList

OPEN FileListCursor

FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName

SELECT @ExecStr = N'RESTORE DATABASE $(Database2Mirror)' +
N' FROM DISK = ''C:\MSHEHADEHVPC\Mirroring\$(Database2Mirror).bak''' +
N' WITH MOVE ''' + @LogicalName + N''' TO ''' + @PhysicalName + N''''

FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName

WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @ExecStr = @ExecStr + N', MOVE ''' + @LogicalName
+ ''' TO ''' + @PhysicalName + ''''
FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName
END

-- NORECOVERY is required for Database Mirroring, replace is not.
-- Replace is used here solely to allow repetitive use of this script.
SELECT @ExecStr = @ExecStr + N' , NORECOVERY, REPLACE'

-- Useful for testing
-- Only return the string and then comment out the EXEC line below.
-- SELECT @ExecStr

EXEC (@ExecStr)

DEALLOCATE FileListCursor
GO

:CONNECT $(PrincipalServer)
SELECT DATABASEPROPERTYEX(N'$(Database2Mirror)', N'Status') -- Returns ONLINE
SELECT db_name(sd.[database_id]) AS [Database Name],
sd.mirroring_guid,
sd.mirroring_state,
sd.mirroring_state_desc,
sd.mirroring_partner_name,
sd.mirroring_witness_name,
sd.mirroring_witness_state,
sd.mirroring_witness_state_desc,
sd.mirroring_role,
sd.mirroring_role_desc,
sd.mirroring_role_sequence,
sd.mirroring_safety_level,
sd.mirroring_safety_level_desc,
sd.mirroring_safety_sequence,
sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')
go

:CONNECT $(MirrorServer)
SELECT DATABASEPROPERTYEX(N'$(Database2Mirror)', N'Status') -- Returns RESTORING
SELECT db_name(sd.[database_id]) AS [Database Name],
sd.mirroring_guid,
sd.mirroring_state,
sd.mirroring_state_desc,
sd.mirroring_partner_name,
sd.mirroring_witness_name,
sd.mirroring_witness_state,
sd.mirroring_witness_state_desc,
sd.mirroring_role,
sd.mirroring_role_desc,
sd.mirroring_role_sequence,
sd.mirroring_safety_level,
sd.mirroring_safety_level_desc,
sd.mirroring_safety_sequence,
sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')
go

:CONNECT $(MirrorServer)

ALTER DATABASE $(Database2Mirror)
SET PARTNER = 'TCP://MSHEHADEHVPC:5091'
-- SET PARTNER = 'TCP://Server.fully.qualified.dns.name:5091'

GO

:CONNECT $(PrincipalServer)

ALTER DATABASE $(Database2Mirror)
SET PARTNER = 'TCP://MSHEHADEHVPC:5092'
-- SET PARTNER = 'TCP://Server.fully.qualified.dns.name:5092'

GO

ALTER DATABASE $(Database2Mirror)
SET WITNESS = 'TCP://MSHEHADEHVPC:5090'
-- SET WITNESS = 'TCP://Server.fully.qualified.dns.name:5090'

GO

SELECT db_name(sd.[database_id]) AS [Database Name],
sd.mirroring_guid,
sd.mirroring_state,
sd.mirroring_state_desc,
sd.mirroring_partner_name,
sd.mirroring_witness_name,
sd.mirroring_witness_state,
sd.mirroring_witness_state_desc,
sd.mirroring_role,
sd.mirroring_role_desc,
sd.mirroring_role_sequence,
sd.mirroring_safety_level,
sd.mirroring_safety_level_desc,
sd.mirroring_safety_sequence,
sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')

--------------------------------------------------------------------
-- CLEAN UP SECTION
--------------------------------------------------------------------
-- This section will clean the database mirroring configuration
---- Begin cleanup...

-- If you want to clean the database mirroring configuration
-- just uncomment the following section

--:SETVAR PrincipalServer MSHEHADEHVPC\SQLDev01
--:SETVAR MirrorServer MSHEHADEHVPC\SQLDev02
--:SETVAR WitnessServer MSHEHADEHVPC\SQLExpress
--:SETVAR Database2Mirror AdventureWorksDW
--go
--
--:CONNECT $(PrincipalServer)
--
--USE master
--GO
--
--SELECT * FROM sys.database_mirroring_endpoints
--SELECT * FROM sys.endpoints
--SELECT * FROM sys.tcp_endpoints
--GO
--
--ALTER DATABASE $(Database2Mirror)
-- SET PARTNER OFF
--GO
--
--DROP ENDPOINT Mirroring
--go
--
--DROP DATABASE $(Database2Mirror)
--go
--
--:CONNECT $(MirrorServer)
--
--DROP ENDPOINT Mirroring
--GO
--
--ALTER DATABASE $(Database2Mirror)
-- SET PARTNER OFF
--GO
--DROP DATABASE $(Database2Mirror)
--go
--
--:CONNECT $(WitnessServer)
--
--DROP ENDPOINT Mirroring
--go
--
------ End cleanup...