Query SQL Server backup history and restore history records

SQL Server備份還原紀錄 1.使用以下TSQL語法查詢備份檔紀錄 SELECT bs.backup_set_id, bs.database_name, bs.backup_start_date, bs.backup_finish_date, CAST(CAST(bs.backup_size/1000000 AS INT) AS VARCHAR(14)) + ‘ ‘ + ‘MB’ AS [Size], CAST(DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date) AS VARCHAR(4)) + ‘ ‘ + ‘Seconds’ [TimeTaken], CASE bs.[type] WHEN ‘D’ THEN ‘Full Backup’ WHEN ‘I’ THEN ‘Differential Backup’ WHEN ‘L’ THEN ‘TLog Backup’ WHEN ‘F’ THEN ‘File or filegroup’ WHEN…

4

SQL Server 2012 Installation fail "Managed SQL Server Installer has stopped working"

SQL Server 2012 Installation fail “Managed SQL Server Installer has stopped working” 在Windows 8上安裝了SQL Server 2012 Developer Edition RTM出現一個錯誤”Managed SQL Server Installer has stopped working”,搜尋網路上的資源都指向是.net framework的問題,試了很多方法,最後的解決方法是將Windows 8 內建的.Net Framework 3.5與4.5所有子功能都一併安裝起來才解決的。我的狀況如下1.當我先安裝了SQL Server 2008, Visual Studio 2008, SQL Server 2008 R2, Visual Studio 2010。2.接著安裝SQL Server 2012 RTM,當我啟動Setup.exe沒有問題,正常出現SQL Server Installation Center,接下來點選Installation裡面的New SQL Server stand-alone installation or add features…

3

Setting Up SQL Server 2008 R2 Database Mirroring with Certificate step by step in a Workgroup

Setting Up SQL Server 2008 R2 Database Mirroring with Certificate step by step in a Workgroup LAB VM Principal Server: SQL2008R2M1 Mirroring Server: SQL2008R2M2 Witness Server: SQL2008R2M3   [Step by Step] 1.Create Database Master key, certificate and endpoint on Principal Instance(SQL2008R2M1) USE master GO SELECT * FROM sys.symmetric_keys GO CREATE MASTER KEY ENCRYPTION BY PASSWORD=’P@ssw0rd’;…

3

Setting Up SQL Server 2008 R2 Log Shipping step by step

Setting Up SQL Server 2008 R2 Log Shipping step by step 1.Prerequisites (1)確認資料庫復原模式recovery model為[完整](Full)或[大量記錄](bulk-logged) (2)建立一個交易紀錄備份用目錄並設定分享,如果只有兩台SQL Server通常建立在主要伺服器Primary Server上,此目錄也可以建立在第三台主機上。 此範例的目錄建立在主要伺服器上,C:\LogShip 並給予主要伺服器Primary Server的SQL Agent啟動帳戶SQLservice有寫入與讀取權限   (3) 建立一個放置要還原的交易紀錄檔目錄並設定分享,此目錄建立在次要伺服器secondary Server主機上。 此範例的目錄建立在次要伺服器上,C:\LogShipRestore 並給予次要伺服器secondary Server的SQL Agent啟動帳戶SQLservice有寫入與讀取權限 2.Prepare a Secondary Database for Log Shipping (1)在主要伺服器上對要設定log shipping的資料庫執行完整備份。 (2)並執行一次交易紀錄檔備份Log Backup。(非必要Optional) (3)複製備份檔到次要伺服器。 (4)在次要伺服器,以WITH NORECOVERY參數還原完整備份與交易紀錄檔備份。 還原後資料庫狀態為(正在還原中)(Restoring)   3.開始設定Log Shipping (1)在主體伺服器設定主體資料庫屬性 (2)在選取頁面點選[交易紀錄傳送],右邊勾選[將此啟用為記錄傳送組態的主要資料庫] Under Select a page, click Transaction Log…

2

Setting Up SQL Server 2008 R2 Database Mirroring in a Workgroup

1.Verify SQL Server service account and configure SQL Server Login for Database Mirroring (1)SQL Server service account Principal Server SQL Server service account is local account name “SQLServer” Mirroring Server SQL Server service account is local account name “SQLService” (2)create a local account “SQLservice” on Principal Server create a login for local account “SQLservice” (3)create a…

2

SQL Server 2012 certifications

Microsoft SQL Server certifications http://www.microsoft.com/learning/en/us/sql-certification.aspx MCSA->MCSE->MCSM Microsoft Certified Solutions Associate (MCSA) SQL Server 2012 MCSA (required exam: 461 + 462 + 463) 70-461 (Training 10774) Querying Microsoft SQL Server 2012 70-462 (Training 10775) Administering Microsoft SQL Server 2012 Databases 70-463 (Training 10777) Implementing a Data Warehouse with Microsoft SQL Server 2012 Microsoft Certified Solutions Expert…

2

Good SQL Server Blogs

SQL Release Services Bloghttp://blogs.msdn.com/b/sqlreleaseservices/ CSS SQL Server Engineershttp://blogs.msdn.com/b/psssql/ SQL Server Engine Tipshttp://blogs.msdn.com/b/sqltips/ Microsoft SQL Server Development Customer Advisory Teamhttp://blogs.msdn.com/b/sqlcat/ SQL CAT(Customer Advisory Team)http://sqlcat.com/ SQL Server Storage Engine Bloghttp://blogs.msdn.com/sqlserverstorageengine/ Tips, Tricks, and Advice from the SQL Server Query Processing Teamhttp://blogs.msdn.com/b/sqlqueryprocessing/ SQL Server Performancehttp://blogs.msdn.com/b/sqlperf/ SQL Programmability & API Development Team Bloghttp://blogs.msdn.com/b/sqlprogrammability/ Euan Garden’s BLOGhttp://blogs.msdn.com/b/euanga/   SQL Server…

2

SQL Server 2008 R2 Performance Dashboard Reports

SQL Server 2008 R2 Performance Dashboard Reports SQL Server 2008 R2效能儀表板報表 Microsoft® SQL Server® 2012 Performance Dashboard Reports https://www.microsoft.com/en-us/download/details.aspx?id=29063 Supported Operating System Windows 7, Windows Server 2008 R2, Windows Server 2008 Service Pack 2, Windows Vista Service Pack 2 Works with the following SQL Server versions: SQL Server 2008, SQL Server 2008 R2, SQL Server…

1

Setting Up SQL Server 2008 R2 Database Mirroring step by step

Setting Up SQL Server 2008 R2 Database Mirroring step by step 1.Prepare a Mirror Database for Mirroring (1)在主體伺服器,對要設定鏡像的資料庫執行完整備份。(Create either a full database backup or a differential database backup of the principal database.)   (2)執行交易紀錄檔備份(Take a log backup) ,為何需要Log Backup可以參考這篇文件 Typically, you need to take at least one log backup on the principal database. However, a…

1

Database Corruption Challenge – Steve Stedman

2016年9月13日 PASS Database Corruption Challenge – Steve Stedman Presented by Steve Stedman https://www.youtube.com/watch?v=5oBA5nL0uRc&feature=youtu.be&a 1.備份資料庫,還原至測試機來進行修復工作(確保還原程序遭遇問題時,可以重頭再來修復一次) 2.如果決定用allow data loss,嘗試先找出可能遺失的資料並事先嘗試SELECT INTO保留下來 3.執行之前尋求是否有其他人的意見   定期執行 osql -E -Q”DBCC CHECKDB (AdventureWorks) WITH ALL_ERRORMSGS, NO_INFOMSGS” -oC:\outputfile.txt sqlcmd -E -Q”DBCC CHECKDB (AdventureWorks) WITH ALL_ERRORMSGS, NO_INFOMSGS” -o C:\outputfile.txt   PS.WITH NO_INFOMSGS只顯示錯誤訊息,一般的information不顯示   基本流程 1.將有損毀的資料庫備份檔還原到測試機,切換成single_user mode。 ATLER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK_IMMEDIATELY; 2.取得錯誤訊…

0