The log scan number (3079:456:1) passed to log scan in database 'master' is not valid. This error may indicate data corruption or that he log file (.ldf) does not match the data file (.mdf).

master db壞真的好麻煩, 處理方法都不同, 不過它只是SQL的結構資料, 相信不會有多大改動, 找到一個近期的backup已經不作考慮data lost問題, 直接restore吧, 如果其他database壞就不會用以下的方法, 因為SQL能online, 方法可以好簡單

我的解決方法是重新安裝一台相同版本,相同機名, 相同Instance的SQL, 然後將master.bak那台SQL上還原, 原因是這台SQL的single mode都進入不到

Production是SQL 2008 R2 10.50.4000
Restore的是SQL 2008 R2 10.50.1600(原來是Patch版本都要一樣), 全新沒有數據

Part 1: Restore master db

master.bak已經抄到Restore的SQL Server, Instance是MSSQLSERVER

net stop MSSQLSERVER
net start MSSQLSERVER /m

sqlcmd -S localhost\MSSQLSERVER

RESTORE DATABASE master FROM DISK ='C:\master.bak'
WITH REPLACE
GO

以下這個是因為版本不一

以下是成功例子

這時master.mdf & mastlog.ldf已經還原了, 可以抄到Production裏使用

Part 2: 將沒有權限的User加入為sysadmin

不過我還是不安心, 我將所有資料庫抄到Restore裏測試一下, 結果估計我這個Windows administrator在Production裏是沒有權限, 而且我沒有sa密碼, 再次進入single mode增加administrator為sysadmin

net stop MSSQLSERVER
net start MSSQLSERVER /m

sqlcmd -S localhost\MSSQLSERVER

EXEC sp_addsrvrolemember 'DOMAIN\administrator', 'sysadmin';
GO

然後所有資料都真的可以看到了

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