Wednesday, April 10, 2013

How to rebuild system databases in SQL Server 2008

setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=instance_name /SQLSYSADMINACCOUNTS= accounts [/SAPWD=password] [/SQLCOLLATION=collation_name]

Thursday, April 4, 2013

How to create FileTable in SQL Server 2012


1.  Create Windows file share and grant R/W permission to SQL Service account




2.  Enable FileStream for Instance: Open SQL Configuration Manager. Right click on SQL Server service. Go to FILESTREAM tab and enable by providing windows file share created in step 1







3.  Create FileStream enabled database

CREATE DATABASE FileStreamDB
ON
PRIMARY ( NAME = FileStreamDB_data,
    FILENAME = 'D:\MSSQL\Data\FileStreamDB.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = FileStreamDB_FS_Data,
    FILENAME = 'D:\MSSQL\Data\FileStream')
LOG ON  ( NAME = FileStreamDB_log,
    FILENAME = 'E:\TransactionLogs\FileStreamDB.ldf')
GO
4.  Alter DB to provide filestream directory name

USE [master]
GO
ALTER DATABASE [FileStreamDB] SET FILESTREAM( DIRECTORY_NAME = N'FileStream' ) WITH NO_WAIT
GO

5.  Create filetable


USE FileStreamDB
GO
IF OBJECT_ID('dbo.testFileTable', 'U') IS NOT NULL
  DROP TABLE dbo.testFileTable
GO
CREATE TABLE dbo.testFileTable AS FILETABLE
  WITH
  (
    FILETABLE_DIRECTORY = 'FileTable',
    FILETABLE_COLLATE_FILENAME = database_default
  )
GO

6.  Verify filestream directory by right clicking on the table and selecting Explore FileTable directory


7.  We can copy files to the directory or load files into filetable we can use either bulk insert

INSERT INTO [dbo].testFileTable
([name],[file_stream])
SELECT
'Yogesh.JPG', * FROM OPENROWSET(BULK N'D:\MSSQL\Data\FileStream\Yogesh.JPG', SINGLE_BLOB) AS FileData
GO


8.  Same was you can delete files by manually deleting from folder or running DELETE statement

9.  Verify data

USE FILESTREAMDB
GO
SELECT [name]
,[file_type]
,CAST([file_stream] AS VARCHAR) FileContent
,[cached_file_size]
,[creation_time]
,[last_write_time]
,[last_access_time]
FROM [dbo].testFileTable
GO