How to determine the version and edition of SQL Server and its components

How to determine the version and edition of SQL Server and its components? this page will indicate you about the version information from MS website:

https://support.microsoft.com/kb/321185/en-us

SQL Server 2012 version information

The following table lists the major releases of SQL Server 2012.
Release	Product Version
SQL Server 2012 Service Pack 1	11.00.3000.00
SQL Server 2012 RTM	        11.00.2100.60

SQL Server 2008 R2 version information

The following table lists the major releases of SQL Server 2008 R2.
Release	Product version
SQL Server 2008 R2 Service Pack 2	10.50.4000.0
SQL Server 2008 R2 Service Pack 1	10.50.2500.0
SQL Server 2008 R2 RTM	                10.50.1600.1

SQL Server 2008 version information

The following table lists the major releases of SQL Server 2008.
Release	Product version
SQL Server 2008 Service Pack 3	10.00.5500.00
SQL Server 2008 Service Pack 2	10.00.4000.00
SQL Server 2008 Service Pack 1	10.00.2531.00
SQL Server 2008 RTM	        10.00.1600.22

SQL Server 2005 version information

The following table lists the major releases of SQL Server 2005.
Release	Product version
SQL Server 2005 Service Pack 4	9.00.5000.00
SQL Server 2005 Service Pack 3	9.00.4035
SQL Server 2005 Service Pack 2	9.00.3042
SQL Server 2005 Service Pack 1	9.00.2047
SQL Server 2005 RTM	        9.00.1399

–EOF–

Shrink Trunsaction Logfile for SQL Server 2008R2

check the recovery mode of database

use master
SELECT NAME, recovery_model_desc FROM sys.databases;

change the recovery mode to simple

use master
alter database EDM_DEV set recovery simple;

locate the file name and the size of LOG file you want to shrink

use EDM_DEV
select name,size from sys.database_files;

the log is named “EDM_DEV_log”,shrink it to 1G

dbcc shrinkfile (N'EDM_DEV_log',1000)

check out the result of size of logfile,see if the size of logfile decreased

change the EDM database to full recovery mode

use matser
alter database EDM_DEV set recovery full;
SELECT NAME, recovery_model_desc FROM sys.databases;

cleanTrunsactionLogfile
–EOF–

windows server 2008 partition extend online

help the customer to extend the NTFS partition online to satisfy the disk consumtion,just mark it.

C:UsersAdministrator>diskpart

Microsoft DiskPart 版本 6.1.7601
Copyright (C) 1999-2008 Microsoft Corporation.
在计算机上: EDM-DEV-DB

DISKPART> list volume

  卷 ###      LTR  标签         FS     类型        大小     状态       信息
 ----------  ---  -----------  -----  ----------  -------  ---------  --------
  卷     0     X                       DVD-ROM         0 B  无介质

  卷     1         系统保留    NTFS   磁盘分区         100 MB  正常     系统
  卷     2     C               NTFS   磁盘分区          29 GB  正常     启动
  卷     3     D   新加卷      NTFS   磁盘分区         109 GB  正常
页面文件

DISKPART> list volume

  卷 ###      LTR  标签         FS     类型        大小     状态       信息
  ----------  ---  -----------  -----  ----------  -------  ---------  --------
  卷     0     X                       DVD-ROM         0 B  无介质

  卷     1         系统保留     NTFS   磁盘分区         100 MB  正常    系统
  卷     2     C                NTFS   磁盘分区         29 GB  正常    启动
  卷     3     D   新加卷       NTFS   磁盘分区         109 GB  正常  页面文件

DISKPART> list

Microsoft DiskPart 版本 6.1.7601

DISK        - 显示磁盘列表。例如,LIST DISK。
PARTITION   - 显示所选磁盘上的分区列表。
              例如,LIST PARTITION。
VOLUME      - 显示卷列表。例如,LIST VOLUME。
VDISK       - 显示虚拟磁盘列表。

DISKPART> list volume

  卷 ###      LTR  标签         FS     类型        大小     状态       信息
  ----------  ---  -----------  -----  ----------  -------  ---------  --------
  卷     0     X                       DVD-ROM         0 B  无介质
  卷     1         系统保留     NTFS   磁盘分区         100 MB  正常         系统
  卷     2     C               NTFS   磁盘分区          29 GB  正常         启动
  卷     3     D   新加卷       NTFS   磁盘分区         109 GB  正常         页面文件

DISKPART> list disk

  磁盘 ###  状态           大小     可用     Dyn  Gpt
  --------  -------------  -------  -------  ---  ---
  磁盘 0    联机              450 GB   310 GB

DISKPART> select disk 0

磁盘 0 现在是所选磁盘。

DISKPART> list volume

  卷 ###      LTR  标签         FS     类型        大小     状态       信息
  ----------  ---  -----------  -----  ----------  -------  ---------  --------
  卷     0     X                       DVD-ROM         0 B  无介质
  卷     1         系统保留     NTFS   磁盘分区         100 MB  正常         系统
  卷     2     C                NTFS   磁盘分区          29 GB  正常         启动
  卷     3     D   新加卷       NTFS   磁盘分区         109 GB  正常         页面文件

DISKPART> list part

  分区 ###       类型              大小     偏移量
  -------------  ----------------  -------  -------
  分区      1    主要                 100 MB  1024 KB
  分区      2    主要                  29 GB   101 MB
  分区      3    主要                 109 GB    29 GB

DISKPART> select volume 3

卷 3 是所选卷。

DISKPART> extend 317440

为此命令指定的参数无效。
有关此命令类型的详细信息,请使用 HELP EXTEND 命令

DISKPART> extend size=317440

DiskPart 成功地扩展了卷。

DISKPART>

DISKPART> list part

  分区 ###       类型              大小     偏移量
  -------------  ----------------  -------  -------
  分区      1    主要                 100 MB  1024 KB
  分区      2    主要                  29 GB   101 MB
* 分区      3    主要                 419 GB    29 GB

–EOF–

sqlserver 2008r2 backup to the UNC share directory

I was trying to backup the sqlserver 2008 R2 to the share directory in the LAN,but lots of error trap me in.
I always got the error message as below:
backup database testdb to “\192.168.56.101backuptestdb.bak”

BackupDiskFile::CreateMedia: backup device ‘z:backupedm.bak’ can not be created。operation system error 3(can not find the path)

I think there must be some difference between the local disk and the UNC mapping disk.

the priviledge in the UNC share disk is ok,I can exclude it’s problem.
while it seem that it is because the account to start the SQL Server instance,that’s the root cause.
AS SQL Server services were startup by “NT AUTHORITYSYSTEM” which has no right to visit the remote resources in the LAN.If you use the domain user or any local os account who has the priviledge to visit remote resources,the problem will be overcome.
Finally,I change the startup account to administrator,this problem was solved.

Besides,it’s not recommended that you use the local administrator account to boot the SQL Server service,because when the OS admin change the password of administrator account,the SQL Server will can not be startup as the password was incorrect..

sqlserver2008R2_bakcup_to_UNC

–EOF–