Veeam B&R restore Physical SQL cluster server to ESXi for UAT environment

This lab will use local disk for Quorum not share disk, and just restore 1 cluster hosts let SQL online

Remark: if we want to auto start cluster, we need to restore AD to UAT environment or using a script to start without quorum.

Export disk to VMDK

Our lab don’t need Q: and F:

We can found restored to our ESXi UAT environment

Add A new VM using existing disk

Delete default “Hard disk 1”, and add existing disk just restored.

Our phyical server using EFI bios

Power On windows and we can see the cluster can’t startup

Type below command to startup without Quorum disk

net.exe stop clussvc
net.exe start clussvc /forcequorum

Delete Cluster disk.

If we don’t delete it, we can’t let the disk online in “Disk Management”

Make the SQL data disk online

Reconfig the IP

Click “Start Role” to let SQL Server online

We can see SQL is Running now

But it can’t auto start after reboot server, we need to config Quorum

Delete the old one, because Quorum can’t online in local disk

Create a SMB share

This step need AD to authenticate

MsSQL dbname(Suspect)

EXEC sp_resetstatus dbname;
ALTER DATABASE dbname SET EMERGENCY;

DBCC checkdb(‘dbname’);
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CheckDB (‘dbname’, REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE dbname SET MULTI_USER;

SQL check client IP

select

conns.client_net_address, conns.auth_scheme, sess.* from sys.dm_exec_sessions sess

LEFT OUTER JOIN sys.dm_exec_connections conns on sess.session_id = conns.session_id

exec sp_who2

EXECUTE 使用權限在物件 ‘xp_cmdshell’,資料庫 ‘master’,擁有者 ‘dbo’ 上被拒絕。

EXECUTE 使用權限在物件 ‘xp_cmdshell’,資料庫 ‘master’,擁有者 ‘dbo’ 上被拒絕。

資料庫-master-延伸預存程序-xp_cmdshell-權限
xp_cmdshell1

在public增加EXEC權限
xp_cmdshell2

己經可以正常修改SQL資料, 但安全問題會出現

由於目前的安全性內容不是系統管理員 (sysadmin),並且沒有正確設定 Proxy 帳戶,所以無法執行 xp_cmdshell 。如需相關資訊,請參考線上手冊以搜尋 xp_sqlagent_proxy_account 的相關主題。
Msg 50001, Level 1, State 0

管理-SQL Server代理程式-內容
UnClick只有具有系統管理員權限的使用者可以執行 CmdExec 和 ActiveScripting 的作業步驟
xp_cmdshell3

增加一個比較低權限的Windows User做SQL代理
xp_cmdshell4

SQL statement

select 產品名稱,單價,單價+30 from 產品
select 產品名稱,單價,單價-20 from 產品
select 產品名稱,單價,單價*1.15 from 產品
select 產品名稱,單天產量,單天產量/3 from 產品
select 產品名稱,單天產量,單天產量/3,單天產量%3 from 產品
select * from 產品 where 單價 = 640 and 單天產量 = 18
select * from 產品 where 單價 = 20 or 單天產量 > 120
select * from 產品 where not 產品名稱 like ‘%蛋糕’
select * from 產品 where 單天產量 is null
select * from 產品 where 單價 in (500,560,580)
select * from 產品 where 單天產量 between 50 and 200

合拼TABLE
select * from 業務部員工 union select * from 行銷部員工
select * from 業務部員工 union all select * from 行銷部員工

Inner Join
select A.行政區, A.門市名稱, B.門市名稱, B.特價日 FROM 行政區 A, 門市 B WHERE A.門市名稱 = B.門市名稱

Subquery
select * from 訂貨單 where 產品編號 = (select 產品編號 from 產品 where 產品名稱 = ‘小泡芙’)

SQL 2000 還原bak及master.bak

net stop MSSQLSERVER

C:\Program Files\Microsoft SQL Server\MSSQL\Binn>sqlservr.exe -m

osql -S localhost -E
Password:
1)restore database master from disk=”D:\master.bak”
2)go

restore database xxx from disk=”D:\xxx.bak” with norecovery
restore log xxx from disk=”D:\xxx.trn” with norecovery
restore log xxx from disk=”D:\xxx.trn”

use sa
osql -S localhost -U sa

指定還原時間
http://www.dotblogs.com.tw/wjl0127/archive/2011/09/27/37946.aspx

啟動Database Mail Error

無法在資料庫 msdb 中啟用 Service Broker,因為資料庫 (x) 中的 Service Broker GUID 與 sys.databases (x) 中的不相符。

use master
go
alter database msdb set single_user with rollback immediate
go
alter database msdb set single_user
go
alter database msdb set new_broker
go
alter database msdb set multi_user
go

SQL 2008 還原Master

cd C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=administrator /SAPWD=xxxxxxxx

cd C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn
sqlservr.exe -m

sqlcmd
USE MSDB
GO
RESTORE DATABASE master
FROM disk=’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\master.bak’
GO