Upgrade MS SQL 2014

** Myself

Pre-Step -Install MS-SQL -stop applicaton
run (from source path) cd T:\sw_database\MSSQL_2014\51049461\x86-x64\EnterpriseEdition\X64 setup.exe /Action=Upgrade /UpdateSource="T:\sw_database\Patch MSSQL 2014"
upgrade

MS SQL server Restore multiple device files

** Myself
## Query Tools
## Restore to QAS database  multiple file of B1.bak,B2,bak,B3.bak,B4.bak


RESTORE DATABASE QAS
FROM
DISK='F:\Backup\B1\B1.bak',
DISK='F:\Backup\B2\B2.bak',
DISK='F:\Backup\B3\B3.bak',
DISK='F:\Backup\B4\B4.bak'
WITH
MOVE 'PRDDATA1' TO 'E:\SAPDATA\QASDATA\QASDATA1.mdf',
MOVE 'PRDDATA2' TO 'E:\SAPDATA\QASDATA\QASDATA2.ndf',
MOVE 'PRDDATA3' TO 'G:\SAPDATA\QASDATA\QASDATA3.ndf',
MOVE 'PRDLOG1' TO 'G:\SAPDATA\QASDATA\QASLOG1.ldf'

MS SQL server Backup multiple device files

** Myself

Pros

-          Backup fasters

-          Each files small and can be separate drive letter (average db size to multiple file size)

Cons

-          Restore slow than single

Add Drive B1,B2,B3,and B4 to D:E:F:

Select & Add Drive B1,B2,B3,and B4 to Backup master DB

MS SQL Change server configuration parameter

** Myself

Change server configuration parameter

Display parametter

EXEC sp_configure

Display all parametter

EXEC sp_configure 'show advanced option', 1

RECONFIGURE WITH OVERRIDE

EXEC sp_configure

Change parametter run_value

EXEC sp_configure 'max server memory', '400';

EXEC sp_configure

MS SQL Relocate physical logfile

** Myself
Relocate physical logfile
*) Detach the database. Use SP_Detach_db procedure
*) Move the log file alone from C: drive to D: drive
*) Attach the db using SP_attach_db procedure as shown below
sp_attach_db 'TEST','C:\sql\test.mdf','D:\sql\test_log.ldf'

MS SQL Move transaction log file to new location

** Credit Web
Move SQL Server transaction log files to a different location via T-SQL and SSMS Bottom of Form
Problem
I know that moving the log file of a production SQL Server database to separate physical drive is a best practice to optimize the I/O performance.  Recently, we have added a physical drive to our SQL Server. Based on this change, I am planning on moving the SQL Server log file of my production database on this drive. I am curious about how it will optimize the performance.  What are some of the considerations I should take into account and how can I move the SQL Server database log file to a separate physical location?
Solution
All SQL Server databases have at least one primary database file and one transaction log file.