SQL Server configuration manager the tool that comes as an integral part of SQL Server 2012 installaiton helps us enable filestream feature
All Programs->Microsoft SQL Server 2012->Configuration Tools->SQL Server Configuration Manager->Database (say SQL EXPRESS)
Right click and choose properties. Click on Filestream tab. Enable filestream, enable I/O access on windows share and remote clients if needed. Click Apply then ok
This will enable filestream access across the SQL Express (for our example) database.
This is similar to executing the following T-SQL query at SQL Server Management Studio ->File-> New Query
exec sp_configure filestream_access_level,2
By default filestream feature is not enabled at database level upon installation or upgrade of SQL Server 2012. filestream is needed if we wish to make use of interesting SQL Server 2012 features like Filetables.
Here is the simple step to enable filestream feature at database level using T-SQL
exec sp_configure filestream_Access_level,2
The sp_configure package will modify the values and enable filestream access. The output will show that filestream_access_level parameter value has been changed from original 0 to latest 2 value
It is evident that we can simply attach AdventureWorks2012.mdf datafile to a database and make it accessible. However, logfile is not naturally added as a part of this porcess.
Adding logfile to AdventureWorks2012 database is simple and straight forward by using alter databas edatabasename add log file command
alter database AdventureWorks2012
add log file ( name=Adventureworks2012log1,
Filetables the latest feature in SQL Server 2012 makes use of filestream feature at database level. Before creating filetables it is mandatory to know if the filestream is enabled at database level.
Here is the simple T-SQL that helps us determine the same
select * from sys.database_files where type_desc=’FILESTREAM’)
PRINT ‘Filestream Configured For Datbase’
print ‘Filestream Not configured for database’
If it is not enabled, the t-SQL cna be run in SSMS new query window to enable filestream
execute sp_configure filestream_access_level,2
How will you shrink database in sql server environment?
Shrink database is often an option to reclaim the unused space from the database. This de-fragmentation will help us shrink the database to its originally created maximum size
We can perform the shrink operation using SQL Server management studio and using T-SQL statement
The dbcc command is used to perform the shrink operation of a database
Say, to shrink a database by name demo issue the following command
dbcc shrinkdatabase(demo,30); – This will shrink data and log files in demo database and will let 30% of space free in these database
How will you perform PERFORM SQL SERVER BACKUP ONTO NETWORK SHARE?
SQL Server offers maintenance plans that automate the full, differential, transactional log backups by creating jobs and automating using schedule. As such there comes a situation to backup the SQL Server databases onto network share directly
Formally, this is not supported by microsoft. However, this is possible and can be done under guidance of sql server expert with approval from microsoft support personnel.
Here are the simple steps
1) Enable the xp_cmdshell. This will change the value from 0 to 1 and the settings are made permanent using reconfigure command
RECONFIGURE WITH OVERRIDE;
2) Set up the network share as backup destination
exec xp_cmdshell ‘net use X: networkshare-name’;
VErify the existence of drive as follows:
exec xp_cmdshell ‘Dir X:’
3) In computer look for X: and make sure this is accessible via unc path
4) Create maintenance plans, specify x: as backup destination and test backups