Misc SQL Server Stuff (Hash, backup, fulltext)

Declare @password Varbinary(5000)
Declare @passwordseed DateTime
Set @passwordseed=GetDate()
Set @password=HashBytes('Sha1', cast(@passwordseed as NVarchar) +'P@ssw0rd1')
Select @passwordSeed as seed, @password as [password]

use Automart
Alter table Customer.RegisteredCustomer
add HashedPassword varbinary(5000)

Update Customer.RegisteredCustomer
Set hashedPassword=hashbytes('sha1', CustomerPassword)
where RegisteredCustomerID=1

Select * from Customer.RegisteredCustomer

Declare @myPassword nvarchar(20)
set @myPassword='amypass'
Declare @hPassword varbinary(5000)
Set @hPassword=hashbytes('sha1',@myPassword)
if exists
 (Select hashedPassword from Customer.RegisteredCustomer
 where hashedPassword=@hPassword)
Begin
print 'Successful Login'
End
Else
Begin
Print 'Login Failed'
End

Declare @myPassword nvarchar(20)
set @myPassword='jpass'

Declare @hPassword varbinary(5000)
Set @hPassword=hashbytes('sha1',N'jpass')
if exists
 (Select hashedPassword from Customer.RegisteredCustomer
 where hashedPassword=@hPassword)
Begin
print 'Successful Login'
End
Else
Begin
Print 'Login Failed'
End

--backups

-- change recovery model 
ALTER DATABASE Automart SET RECOVERY SIMPLE; 
--ALTER DATABASE Automart SET RECOVERY BulkLogged; 
ALTER DATABASE Automart SET RECOVERY full; 

-- create a full backup of Automart 
BACKUP DATABASE Automart 
TO DISK = 'C:\Backups\Automart.bak' 
with init; 

use Automart
Alter table Customer.RegisteredCustomer
add HashedPassword varbinary(500)

-- create a differential backup of Automart appending to the last full backup 
BACKUP DATABASE Automart 
TO DISK = 'C:\Backups\Automart.bak' 
with differential; 

-- create a backup of the log 
use master; 
BACKUP LOG Automart 
TO disk = 'C:\Backups\AutomartLog.bak' 
WITH NORECOVERY, NO_TRUNCATE; 

create table TestTable 
( 
ID int identity(1,1) primary key, 
MyTimestamp datetime 
); 

insert into TestTable values 
( 
GETDATE() 
); 

use Automart; 
select * from TestTable; 

-- restore from the full backup 
use master; 
RESTORE DATABASE Automart 
FROM disk = 'C:\Backups\Automart.bak' 
with norecovery, file = 1; 

-- restore from the differential backup on file 2 
RESTORE DATABASE Automart 
FROM disk = 'C:\Backups\Automart.bak' 
with norecovery, file = 2; 

-- restore from the differential backup on file 3 
RESTORE DATABASE Automart 
FROM disk = 'C:\Backups\Automart.bak' 
with norecovery, file = 3; 

-- restore from the log 
use master; 
RESTORE LOG Automart 
FROM disk = 'C:\Backups\AutomartLog.bak' 
WITH NORECOVERY; 

restore database Automart; 

--Full Text Catalog
use Master

--add a filegroup
Alter Database Automart
Add Filegroup FullTextCatalog

use Automart
--add a table with some text
Create Table TextTest
(
   TestId int identity (1,1) primary key,
   TestNotes Nvarchar(255)
)

--insert text
Insert into TextTest(TestNotes)
Values('For test to be successful we must have a lot of text'),
('The test was not successful. sad face'),
('there is more than one test that can try a man'),
('Success is a relative term'),
('It is a rare man that is always successful'),
('The root of satisfaction is sad'),
('men want success')

Select * From TextTest

--create full text catalog
Create FullText Catalog TestDescription
on Filegroup FullTextCatalog

--Create a full text index
Create FullText index on textTest(TestNotes)
Key Index PK__TextTest__8CC331603A9A1988
on TestDescription
With Change_tracking auto

--run queries on the full text catalog

--find all instances that have the word "sad"
Select TestID, TestNotes 
From TextTest
Where FreeText(TestNotes, 'sad')

--do the same with successful
Select TestID, TestNotes 
From TextTest
Where FreeText(TestNotes, 'successful')

Select TestID, TestNotes 
From TextTest
Where Contains(TestNotes, '"success"')

--look for any words containing the letters "success"
--the * is a wildcard
Select TestID, TestNotes 
From TextTest
Where Contains(TestNotes, '"success*"')

--looks for all grammatical forms of a word
Select TestID, TestNotes 
From TextTest
Where Contains(TestNotes, ' Formsof (Inflectional, man)')

--finds words near another word
Select TestID, TestNotes 
From TextTest
Where Contains(TestNotes, ' not near successful')

Select TestID, TestNotes 
From TextTest
Where Contains(TestNotes, ' sad near successful')


Select ServiceName, ServiceDescription.query
('declare namespace s="http://www.automart.com/servicedescription";
//s:servicedescription/s:labor') 
From Customer.AutoService
Where ServiceName='Tune Up'


Select * From Customer.AutoService