sql server misc

Run the Software in administration(or may not hasright to connect)

 

SQLServer Management Studio is a GUI tool included with SQL Server

 

 

Database backup

Database(rightclick) -> task -> backup

Backup full: backupboth data and log

Backup destination:use a new file  **.bak to get a smallerbackup, otherwise all backup will in origin one big file

 

Database restore

Database(rightclick) -> restore database -> device -> choose the file

Option: restore withnorecovery

(ifcreate a table, will not write over the origin table, doesn't need backuporigin table, choose this one)

There may be errorin Windows UI, so press enter to run the restore

Database(rightclick) -> refresh

(tosee the restoring state of the database)

(It takes more timeto restore than backup)

 

Check sql job running state

Sql job history

SQLserver Agent -> jobs -> choose the job (right click) view history

Mostyellow alert is cause the current machine is not master

Otheryellow alert often cause by the sql server error such as connection or stringand so on (search the error code in MSDN or google)

Job activity monitor

(Seethe brief view of the current job running state)

 

Write the sql

Select top 1000, thetop is in the bottom

( add order by descto let latest at top)

 

Give the variablevalue

Method1: set a =

Method2: select a ==

 

Delete from<tabel name>

to clean the table ,it's ok to escape the * between delete from

 

Delete job

Select job (rightclick) -> delete

(feel free to justdirect delete the job)

 

New query

Must enure the queryis connnect to the right server

If the new query isto create a new job, must first ensure the context is your DB, or it will takeeffect to master DB

Write'use msndashboardetl' at thetop of the new query means use the MSNDashboardETL DB

 

Modify stored procedure

If you execute afterthe modify, then the script will automatically saved

 

 

Modfiy servername

 (after you change your pcname, sql server name doesn't change automatically,

You should mannually change)

 

Get server name

SELECT@@SERVERNAME

 

Update server name

Eg:

oldname :WIN-NCS9ECDN8GU

newname:WIWU_WORKSTATIO

Runthe query

sp_dropserver@server ='WIN-NCS9ECDN8GU',@droplogins ='droplogins'

sp_addserver@server='WIWU_WORKSTATIO',@local='LOCAL'

Restartsql server service

 

Update the table

Updatetable set domain = value

 

 

Change email notification

Change the job failure receiver and write log

Sqlserver agent -> jobs -> select one job -> property -> notification

Change the job failure receiver list

Sqlserver agent -> operators

Change the job failure sender list

Sqlserver  -> management -> databasemail

 

Run cmd query script

Query-> sqlcmdmode  on

Orsome cmd in query such as :setvar can't be execute

 

Open the xp_configure

(during manually install ED ETL, run create sql jobscript will cause the warning, then the data source isn’t create)

EXEC

sp_configure'show advanced options', 1

GO

-- Toupdate the currently configured value for advanced options.

RECONFIGURE

GO

-- Toenable the feature.

EXEC

sp_configure'xp_cmdshell', 1

GO

-- Toupdate the currently configured value for this feature.

RECONFIGURE

GO

 

 

Sql management studio connection error

Sqlserver configuration manager -> sql server services -> open the service

                 ->  protocols -> tcp/ip enabled

If onlylocal machine can remote ,  selectdatabase -> property -> allow remote connection

 

 

 

举个例子来解释RECOVERY和NORECOVERY有什么区别

比如1是全备份，2是差异备份，3，4，5是日志备份，如果现在要恢复到5的时间，首先要恢复1，然后2，然后3，4，5从1--4期间都要指定为norecovery，为什么呢？可以设想一下如果是recovery的话，此时数据库已经可以应用，比如如果在2--3之前用了recovery，则进行2以后数据库就可以应用，此时如果有人进行数据库操作，那么3就不能恢复了。再有在2--3之间中间段的事物将会回滚，此时如果再一次恢复后面序列备份就会出错。

 

 

 

Delete dbase

Databasemirroring must be removed before you drop SQL Server database – Error 3743

Run the followingscript to stop mirroring

 

ALTERDATABASE [Database_Name] SET PARTNER OFF;

GO

 

 

Connection string

DataSource =Server = Address =Addr = NetworkAddress  

InitialCatalog =Database

 

Eg:

DataSource=myServerAddress;Failover Partner=myMirrorServerAddress;Initial

Catalog=myDataBase;IntegratedSecurity=True;

 

Server=myServerAddress;Database=myDataBase;UserId=myUsername;

Password=myPassword;

 

 

Account

Sqlserver is the windows-service, since you close the sql connection managementstudio, the function is still on.

 

 

 

Databases state

Recovering

Was there any sudden shut down of the SQL Server? Just wait till itcomes back online again.

 

Ifmirroring set failure, then the slave will become this state from restoringstate

 

Recovery pending

Meaning:means that for some reason SQLcannot run restart recovery on the database.

 

Cause:

Usually this is because the log is missing or corrupt.

Myexperience  failover, new principledoesn't update, then failover again back, origin principle become this state

 

Solution:

Chek firs mdf and ldf is exist or not..

if exist run below coommand on master DB

ALTER DATABASE DATBASE_NAME SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE DATBASE_NAMESET ONLINE WITHROLLBACK IMMEDIATE

 

WhenI try to online the database, it prompt

The database 'RelevanceOptimizationLog' cannot be opened because itis version 661. This server supports version 655 and earlier. A downgrade pathis not supported.

Originprinciple server 10.0.5500,  originmirror server 10.50.4000

 

 

 

Sql Sentences

,convert(xml,content)

 

  delete fromtablename   //clear the table

 

Comment /* */

 

SUSER_SNAME( [ server_user_sid ] )  //Returns the loginname

 

Comment --

 

Get sql server version

Server -> property -> general -> product

SELECT @@VERSION

 

 

Upgrade from sql server 2008 to 2008 r2

Opensql server installation center

他们都和 productsweb上下载的内容一样是么？

应该是一样的

\\phx那个目录里的不一样

是 ipak version

我应该选择哪一个文件作为安装文件？ \\phx.gbl\Services\MSNPLAT\gold\SQL2008R2SP2\里面还有多层安装目录

我是在sql serverinstallation center 里面选择upgrade然后选择安装文件， 应该选择哪一个？

选择哪一个安装文件？  ipakhelper是安装文件么？

原始sql安装文件

在 sqlbits里

\\phx.gbl\Services\MSNPLAT\gold\SQL2008R2SP2\SQLBITS\SQL\Datacenter

这个应该是 r2的安装包

\\phx.gbl\Services\MSNPLAT\gold\SQL2008R2SP2\SQLBITS\SQL\SP2

sp2升级包

安装包是setup.exe 还是需要进入里面的x64选择setup100.exe？

你也可以运行\\phx.gbl\Services\MSNPLAT\gold\SQL2008R2SP2

下的 sqlipak

用 dos

cmd

pushd \\phx.gbl\Services\MSNPLAT\gold\SQL2008R2SP2

sqlipak/?

 

Failover

Aftermanually failover, the origin principle sql server log  will prompt login in failure error

But  if the principle is accessible, the failoverpartner will not prompt login in failure error

 

Sql server configuration manager

 

 

Security -> logins

These are the accounts that can connect to sql server, but may bethey only have read right, (you can set is in property to be dbowner or others)

Account 'Sa' is the account only in sql server,  it's unrelated with your windows server2012

Account 'nt authority/service account',  is an windows inner account,  is just the 'Network service' account inwindows-service, this account can't be used to log,  only use to get network resources.

 

Monitor active connections

SELECT * FROM sys.dm_exec_connections

exec sp_who

exec sp_who2

 

Start sql server agent

 can't open sql server agent, promptagent XPs is disabled

sp_configure'show advanced options',1

go

reconfigurewith override

go

sp_configure'Agent XPs',1

go

reconfigurewith override

go

sp_configure'show advanced options',0

go

reconfigurewith override

go

 

 

 

Check mirroring principle and mirroring

DECLARE@MirroringRole int;

SET@MirroringRole = (SELECT mirroring_role

    FROM sys.database_mirroring

    WHERE DB_NAME(database_id) =N'RelevanceOptimizationLog');   -- yourdatabase name here

 

select@MirroringRole

 

IF@MirroringRole = 2 -- Mirror

    -- connect to the failover partner server,using your database

select @MirroringRole

ELSE IF@MirroringRole = 1 -- Principal

    -- connect to this server

select @MirroringRole

 

 

select *from sys.database_mirroring where mirroring_role =1 for principal database

and

select *from sys.database_mirroring where mirroring_role =2 for mirrored database

 

Check master and slave

 

DECLARE@ReturnValue int

EXEC@ReturnValue= dbo.IsMasterMachine

IF@ReturnValue = 0

RAISERROR('Current machine is not master, following steps will be skiped',16,1)

 

t-sql

Onsuccess go the next failure

Onfailure set quit job reporting success

 

 

DECLARE@MirroringRole int;

SET@MirroringRole = (SELECT mirroring_role

    FROM sys.database_mirroring

    WHERE DB_NAME(database_id) =N'RelevanceOptimizationLog');   -- yourdatabase name here

 

IF@MirroringRole = 2 -- Mirror

    -- connect to the failover partner server,using your database

RAISERROR('Current machine is not principle, following steps will be skiped',16,1)

 

 

 

 

Configure mirroring Step

Youshould remote to primary, second machine, to ensure the service is runningunder 'Network service' account,

Thenyou should check primary, second machine 's control panel -> administratortool -> windows firewall advanced security -> both primary , secondaryinbound and outbound has enable the 5022 port( new rule -> select port ->enable)

Thenyou should ensure there exist  security-> logins  'nt authority/ networkservice' account in your primary, secondary machine db sql connectionmanagement, and each of them has the dbowner and sysadmin of the your mirroringdatabase.

(youshould keep the db in recovery mode, the dbowner and sysadmin can be set)

Youshould add primary domain /primary machine name to secondary  security -> logins

 

 securable -> add the server name  and add a point called mirroring (only afteryou has mirrored server , will display Mirroring endpoint)  you should modified mirroring endpoint grant,all it default is none, if you open users property again, the mirroringendpoint will disappear

 

Setthe primary db property -> option -> full recovery mode  (only then, you can backup transaction log)

Backupdb, backup transaction log

Restoredb ( select no recovery option or you can't restore transaction log), restoretransaction log ( restore -> transaciton log, also select norecovery mode)

 

 

 

 

 

Manually failover after the origin principle serversuddenly down

If the principal server does down, the option to 'select themirroring tab and click on failover' will not be available on the mirror server

Connect to the mirror server.

Issue the following statement:

ALTER DATABASE <database_name> SET PARTNERFORCE_SERVICE_ALLOW_DATA_LOSS

where <database_name> is the mirrored database.

The mirror server immediately transitions to principal server and show in recovery state,

 

Ifyou bring back the previous prinple, it will show 'mirror, suspended/restoring'

 

Once your previous principal Serevr comes back online, mirroringwill be suspended between the two. You have to manually re-enable mirroring,where you'll get a warning about possible data loss due to the way the previousmirror was brought back online.    

 

resume a database mirroring session

 

Method1

Newprinciple (which in recovery model -> properties -> mirroring ->resume)

 

Method2

Connect to either partner.

Issue the following Transact-SQL statement:

ALTER DATABASE <database_name> SET PARTNER RESUME

 

Notsucceed with method1, and new mirror doesn’t has property only has general

Usemethod2 running one new mirror success

 

 time

 within 30 seconds,

 

 

unable to start service sqlserveragent  unknown failure(objectExplorer)

Checkif has nt service/sqlserveragent login

 

If disable  Agent XP , open it

Thenyou can open see  sql jobs,  logs in sql serveragent

 

Butstill meet prompt unable to start service sqlserveragent

 

Sqlserver agent log file,  sqlagent->property->general -> error log find the log file location  thecontent is the same with  sql serveragent -> error log

 

Insql server configuration manager, there is no sql server agent service

 

Open windows service, we could find the sql server agent service,but if we want start it, prompt 'windows could notstart the sql server agent service  can'tfind the file specified'

 

Sql server agent running job

Everytime you run the job, it prompt you start from which step, then it has startand run two step, these are different with the detailed steps you set the thejob,  so if check master failure, itstill should be two succeed

 

Ifyou transact-sql script has select from other table, you should has GO to execthe above command,

Orit will not exec,  if only declare andset , there is no need to use go,  everycommand between two go will be send as an query

 

 

Manually created the sql serve job

Add use table todefine the right table

Modify  wisaccount to 'NT authority\network service'to modify the creator of the job

So, howdoes the manually install auto do these thing?

 

 

 

 

 

SQLSERVER – The server network address “TCP://SQLServer:5022″ can not be reached or does not exist.Check the network address name and that the ports for the local and remoteendpoints are operational. (Microsoft SQL Server, Error: 1418)

Check ifyou can telnet to your ports using command TELNET ServerName Ports like “telnetSQLServerName 5023

Method 1

单击，开始---〉运行---〉telnet 192.168.1.1

Method 2

cd\windows\system32

 

If no command, youshould install telnet client in control panel -> program windows feature

 

Sql server instanceEndpoint  correspond to one port of theserver

 

Check endpoint,  select * fromsys.database_mirroring_endpoints

 

DROPENDPOINT Mirroring(endpoints name);   todrop the endpoint

 

If you configuremirroring again, a new endpoint will be created

 

NT AUTHORITY\NETWORKSERVICE

 

 

Allow sql server remote connection

Instance ->propety -> connections-> allow remote connection

 

 

SSIS sql job

To configure the sqljob (configuration, datasource), you should remote to that machine, or youwon't have the right to access the file

 

 

The identifier that starts with....is too...

Singlequote should be used when passing String to a query or Stroed Procedure inTransact SQL

 

 

 

 

 

1.DELETE

　・DML语言

　・可以回退

　・可以有条件的删除

 

     DELETE FROM表名

　  WHERE条件

 

2.TRUNCATETABLE

　・DDL语言

　・无法回退

　・默认所有的表内容都删除

　・删除速度比delete快。

 

   TRUNCATE TABLE表名

 

 

 

n, SSMS, execute bellow steps.

1. Click Tools.
2. Select Options
3. Select Designer

Just uncheck the Option highlightedin the Screenshot. Now you can use the Alter script to change the DDL.

 

 

将数字转换成字符串，如果是整数类型的话，可以使用str()函数直接来转换，不过用str函数不能转带有浮点数。

declare @scorefloat;

set @score=1.6;

selectstr(@score);

输出结果是2。这明显是不对的。

 

From<http://blog.163.com/zjlovety@126/blog/static/22418624201012482922707/>

 

 

所以更建议不要使用str函数，而使用convert和cast函数

这两个函数的语法

convert(目标类型，要转换的数字);

cast(要转换的数字 as目标类型);

如：

declare @scorefloat;

set @score=1.6;

selectconvert(varchar(5),@score);

declare @scorefloat;

set @score=1.6;

selectcast(@score as varchar(5));

 

From<http://blog.163.com/zjlovety@126/blog/static/22418624201012482922707/>

 

 

Temptable in sql sentence

CREATETABLE #temptable(clicks int, pageviews int )

 

 

Select result insert into one table

INSERT INTO dbo.Users

(

   Username

    ,FirstName

    ,LastName

    ,IsSuperUser

    ,AffiliateId

    ,Email

    ,DisplayName

    ,UpdatePassword

)

SELECT  

   Username

    ,FirstName

    ,LastName

    ,IsSuperUser

    ,AffiliateId

    ,Email

    ,DisplayName = DisplayName + ' SuperUser'

    ,UpdatePassword

FROM dbo.NewUsersnu

WHERE nu.IsSuperUser = 1

 

 

 

 

DECLARE @InputStr int
       ,@Size     int
SELECT @InputStr=123
      ,@Size=10

PRINT REPLICATE('0',@Size-LEN(RTRIM(CONVERT(varchar(8000),@InputStr)))) + CONVERT(varchar(8000),@InputStr)

 

 

 

Find the location of sql server agent log file

EXECmsdb.dbo.sp_get_sqlagent_properties

 

EXECmsdb.dbo.sp_set_sqlagent_properties @errorlog_file=N'D:\Srvapps\Microsoft SQLServer\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT'

 

But the job history error log is in db

select *from msdb.dbo.sysjobhistory

order byrun_date desc

 

10mins

520runtime difference

Run time scale , if 9:23:00, runtime will be 92300

 

job history查询都timeout可能是sql server资源占用太高

 

select *from msdb.dbo.sysjobhistory

wherestep_name != 'CheckMasterStatus'

andrun_date = '20131023'

order byrun_time

 

 

 

 

 

Check mirroring principle and mirroring

1. Granting sysadmin to NT AUTHORITY\NETWORK SERVICE, so it can check master db of mirroring stat

   2.  step should use master db,  (ifuse your db, mirror will automatically connect to the principle

 

 

Copy the table

Source table doesn't exist

select *into <destination table> from <source table>

 

Source table already exist

INSERTINTO dbo.CumulativeModuleCTR SELECT * FROM dbo.CumulativeModuleCTR_bak

 

 

truncatetable dbo.CumulativeModuleCTR

truncatetable dbo.CumulativeModuleCTR

 

declare@Ser nvarchar(1000)

declare@Query nvarchar(1000)

 

set @Ser= 'Server=CPKMSNVM000001;Trusted_Connection=yes;Database=MSNDashboard'

 

set@Query = 'Insert dbo.CumulativeModuleCTR

Select *

FROMOPENROWSET(''SQLNCLI'','''+ @Ser +''',

''select* from dbo.CumulativeModuleCTR''

        ) AS a;'

 

EXEC(@Query)

 

set@Query = '

SETIDENTITY_INSERT dbo.DimPageStructure ON

Insertinto dbo.DimPageStructure

(

[AreaID]

      ,[AreaName]

      ,[FullPath]

      ,[ParentAreaID]

      ,[PageID]

      ,[LevelNumber]

      ,[LeafLevel]

      ,[TabGroupID]

      ,[TabID]

      ,[ModuleName]

      ,[VirtualModuleName]

      ,[VirtualAreaID]

      ,[Order]

      ,[IsExcluded]

      ,[Active]

)

Select[AreaID]

      ,[AreaName]

      ,[FullPath]

      ,[ParentAreaID]

      ,[PageID]

      ,[LevelNumber]

      ,[LeafLevel]

      ,[TabGroupID]

      ,[TabID]

      ,[ModuleName]

      ,[VirtualModuleName]

      ,[VirtualAreaID]

      ,[Order]

      ,[IsExcluded]

      ,[Active]

FROMOPENROWSET(''SQLNCLI'','''+ @Ser +''',

''select* from dbo.DimPageStructure''

        ) AS a;

SETIDENTITY_INSERT dbo.DimPageStructure OFF

'

 

EXEC(@Query)

 

 

 

 

 

SELECT

      [PageID]

      ,[UpdatedDateTime]

  FROM[MSNDashboard].[dbo].[CumulativeModuleCTR]

  group by UpdatedDateTime, PageID

All the areaid hasonly last update record

 

 

 

Error prompt:An explicit value for the identity columnin table can only be specified when a column list is used and IDENTITY_INSERTis ON SQL Server

SET IDENTITY_INSERTtableA ON

You have to make acolumn list for your INSERT statement:

 

INSERT Into tableA([id], [c2], [c3], [c4], [c5] )

SELECT [id], [c2],[c3], [c4], [c5] FROM tableB

not like"INSERT Into tableA SELECT ........"

 

SET IDENTITY_INSERTtableA OFF

 

 

 

About sql server logins and database logins

Every database has logins,

If you one db error, backup it,

You will find the logins only in specific database,but the total logins will be none,

But it still work

 

 

 

 

 

 

truncatetable dbo.DimPageStructure

 

declare@Ser nvarchar(1000)

declare@Query nvarchar(1000)

 

set @Ser= 'Server=CPKMSNVM000001;Trusted_Connection=yes;Database=MSNDashboard'

 

set@Query = '

SETIDENTITY_INSERT dbo.DimPageStructure ON

Insertinto dbo.DimPageStructure

(

[AreaID]

      ,[AreaName]

      ,[FullPath]

      ,[ParentAreaID]

      ,[PageID]

      ,[LevelNumber]

      ,[LeafLevel]

      ,[TabGroupID]

      ,[TabID]

      ,[ModuleName]

      ,[VirtualModuleName]

      ,[VirtualAreaID]

      ,[Order]

      ,[IsExcluded]

      ,[Active]

)

Select[AreaID]

      ,[AreaName]

      ,[FullPath]

      ,[ParentAreaID]

      ,[PageID]

      ,[LevelNumber]

      ,[LeafLevel]

      ,[TabGroupID]

      ,[TabID]

      ,[ModuleName]

      ,[VirtualModuleName]

      ,[VirtualAreaID]

      ,[Order]

      ,[IsExcluded]

      ,[Active]

FROMOPENROWSET(''SQLNCLI'','''+ @Ser +''',

''select* from dbo.DimPageStructure''

        ) AS a;

SETIDENTITY_INSERT dbo.DimPageStructure OFF

'

 

EXEC(@Query)

 

 

  selectcount(*) FROM

Return rows