Sunday, October 30, 2011

2 Restore database (4) Piece meal Restore–Online restore

Piecemeal restore refers to restore just page,file or file group. If the SQL Server is Enterprise. SQL Server may do the online restore which means the database will be online and only the restoring file or file group is not accessible.

Logical and physical file names of a database

Lets Create new file group and add 1 file to the file group

  1. -- Create 2nd file group
  2. USE [master]
  3. GO
  5. GO
  6. -- Add 2 files on the file group
  7. USE [master]
  8. GO
  9. ALTER DATABASE [AdventureWorks_new] ADD FILE ( NAME = N'GROUP2file1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2\MSSQL\DATA\GROUP2file1.ndf' , SIZE = 1024KB , FILEGROWTH = 1024KB ) TO FILEGROUP [MYFILEGOUP]
  10. GO

Exam the file group status

  1. --- Exam FIlegroup
  2. use [AdventureWorks_new]
  3. go
  4. SELECT, df.size*8/1024 AS [SIZE IN MB],
  5. FROM sys.database_files df
  7. sys.filegroups fg on df.data_space_id = fg.data_space_id


Create the Table on the new file group

  1. -- Create Tabe1 on FIle 1 add 1000 row data
  2. use [AdventureWorks_new]
  3. go
  5.          (number INT IDENTITY(1,1) NOT NULL,
  6.           name varchar(50) NOT NULL,
  7.           updateTIme datetime default getdate(),       
  8.          ) on MYFILEGOUP

Backup the database

  1. --- Backup database
  2. BACKUP DATABASE  [AdventureWorks_new] to DISK ='C:\dump\AdventureWorks_new.1.db' with format
  3. --- Exam the backup file
  4. RESTORE FILELISTONLY FROM DISK ='C:\dump\AdventureWorks_new.1.db'

Insert some data

  1. --- Insert Test data
  2. use [AdventureWorks_new]
  3. go
  4. DECLARE @count INT
  5. SET @count = 0
  6. WHILE (@count < 40000)
  7. BEGIN
  8.    Insert into TB_1 (name)values('SUMMER')
  9.    SET @count = (@count + 1)
  10. END

Backup the transaction log

  1. -- Backup Log
  2. BACKUP LOG [AdventureWorks_new] to DISK ='C:\dump\tran\AdventureWorks_new.1.log' with format

Exam the file group size change

  1. --- Exam FIlegroup
  2. use [AdventureWorks_new]
  3. go
  4. SELECT, df.size*8/1024 AS [SIZE IN MB],
  5. FROM sys.database_files df
  7. sys.filegroups fg on df.data_space_id = fg.data_space_id
  8. GO
  9. --- Exam table
  10. use [AdventureWorks_new]
  11. go
  12. declare @id int
  13. set @id = object_id('TB_1')
  14. exec sp_objectfilegroup @id
  15. go
  16. sp_spaceused TB_1
  17. go
  18. select top 5 * from TB_1
  19. go


To simulate the file group corruption, we need to shutdown the database and delete the  GROUP2file1.ndf then restart the SQL Server.

Upon the SQL Server start up, you will find out the Database is in the RECOVERY PENDING state, the error log will also indicate it can not find the database file.

  1. --- database status
  2. SELECT state_desc DatabaseStatus_sysDatabase
  3. FROM sys.databases
  4. WHERE name = 'AdventureWorks_new'
  5. GO


Lets start the piece meal recovery

First, we need to backup the tail log

  1. --- Backup tail log
  2. use master;
  3. BACKUP LOG  [AdventureWorks_new] TO DISK ='C:\dump\tran\AdventureWorks_new.tail.log' WITH NORECOVERY, NO_TRUNCATE, FORMAT

Check the Database status again, the status become “RESTORING”

  1. --- database status
  2. SELECT state_desc DatabaseStatus_sysDatabase
  3. FROM sys.databases
  4. WHERE name = 'AdventureWorks_new'
  5. GO


Since the Primary file group is intact, Lets bring it up ASAP

Restore Primary file group ,  log 1 and the tail log

  1. --- Restore filgrop from backup
  2. RESTORE DATABASE  [AdventureWorks_new] FILEGROUP='PRIMARY' FROM disk ='C:\dump\AdventureWorks_new.1.db'
  4. --- Revcover log
  5. RESTORE LOG [AdventureWorks_new] FROM DISK ='C:\dump\tran\AdventureWorks_new.1.log'  WITH NORECOVERY
  6. RESTORE LOG [AdventureWorks_new] FROM DISK ='C:\dump\tran\AdventureWorks_new.tail.log'  WITH RECOVERY

Check the status again, the database is online now, but if you try to select the TB1, you will get the error message regarding the file group is not online. The table in the primary file group will be accessible for user .


Lets restore the 2nd file group.

  1. --- Recover the filgroup
  2. use master;
  3. RESTORE DATABASE  [AdventureWorks_new] FILEGROUP='MYFILEGOUP' FROM disk ='C:\dump\AdventureWorks_new.1.db' WITH NORECOVERY
  4. RESTORE LOG [AdventureWorks_new] FROM DISK ='C:\dump\tran\AdventureWorks_new.1.log'  WITH NORECOVERY
  5. RESTORE LOG [AdventureWorks_new] FROM DISK ='C:\dump\tran\AdventureWorks_new.tail.log'  WITH RECOVERY

Now , you have fully usable database.


Performing Piecemeal Restores

Perform Page restore

Saturday, October 29, 2011

2 Restore database (3) Restore/Rebuild the master database

Restore the master database from previous backup

  1. -- Backup the master database
  2.   BACKUP DATABASE [master] TODISK ='C:\dump\master_20111029.db' with format
  3. -- Find the master database file
  4. select physical_name from sys.database_files
  5. -- shutdown sql server

Start up the SQL server from command line as single user mode (-m)

“C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2\MSSQL\Binn\sqlservr.exe" -sMSSQL2008R2 -m


Connect to SQL server and restore the master db from previous backup. Upon the restore competition. The SQL server will shut down.

  1. -- restore the sql server master db from backup
  2. RESTORE DATABASE master from DISK ='C:\dump\master_20111029.db' WITH REPLACE;


Rebuild the master database

In the event when the master db corruption, we will need to rebuild the master db from scratch and restore the master db from previous backup.

The procedure will not just only rebuild the master db, but also the msdb and model. you can not just rebuild the master. Make sure you have the most current backup for all the system db before you tried.

To simulate the master db corruption, lets rename master.mdf to master_old.mdf

When start up the sql server, it gives up the error as below



C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQL2008R2  /SQLSYSADMINACCOUNTS=SG11\administrator

The detail report will be log under C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\summary.txt

After following up MSFT suggestion, I hit the error below


As this post discuss

We need to run the setup from the installation media


If your sql server set up as mix authentication mode, you must also provide  /SAPWD, otherwise you will find the error message “ Configuration error description: Missing sa account password. The sa account password is required for SQL Authentication Mode.” in the summary log at C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\log\[TIMESTAMPE]\SUMMARY…..


After master being restore, restart SQL server to see whether it can be recover. Once it is confirmed, restart it again with single user mode, restore master from the last backup. Then restore msdb and model from backup.

  1.   RESTORE DATABASE [master] FROMDISK ='C:\dump\master_20111029.db' WITH REPLACE;
  2. go
  3. -- Restore model and msdb
  4.   RESTORE DATABASE [model] FROMDISK ='C:\dump\model_20111029.db' WITH REPLACE;
  5. go
  6.   RESTORE DATABASE [msdb] FROMDISK ='C:\dump\msdb_20111029.db' WITH REPLACE;

As alternative method, Quantum John suggest here is that instead of rebuild the master db. restore the master db backup to the test server for different name (ex:restore_master) using WITH MOVE. Then detach the db and copy the db file back to the corrupt server. rename the mdf and ldf file. Bonce the corrupt server.

The version of the database server must be the same. Only major version matter, such as 2005 and 2008. Hotfix and service pack only exist in the resource db.

Although, it sounds very promising and easy, I have not try it myself. Something interesting to try it~~!!


Rebuild the system database

2 Restore Database (2) Snapshot and Snapshot restore

Database snapshot is the read only database storing ONLY the modify pages from the original database. Internally it use sparse file type, therefore it use less space compare the original database size. After the snapshot being created. Whenever the original database page being modified, sql server copy the original page to the snapshot file.

Create the snapshot. The snapshot can be created at the same server as the source database.

  1. CREATE DATABASE SSAdventureWorks_201110291450 ON
  2. ( NAME = AdventureWorks_Data, FILENAME =
  3. 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2\MSSQL\DATA\' )
  4. AS SNAPSHOT OF AdventureWorks;
  5. GO

We can query the snapshot just like query regular database.

  1. Select * from SSAdventureWorks_201110291450.HumanResources.Department


When access the snapshot, user actual access the source database unless those page has been updated from the source. Form this logical, all the security, file group status will be maintain as is when the time the snapshot is created. The snapshot is the read only copy. hence even user has write permission, user still can not update the table in the snapshot.

If the source database, or file group become unavailable, user may encounter issue accessing the snapshot as well. Therefore we can not treat the snapshot as type of disaster recovery solution.

Copy-on-write operation

We can find the snapshot from the sys.database

  2.       database_id,
  3.       source_database_id,
  4.       is_read_only
  5.   FROMsys.databases where name='SSAdventureWorks_201110291450'


Find out the snapshot file size

Form Windows explorer: The size is the max size it can grow. The Size on the disk is the size of the current file.


Find current size from DMV sys.dm_io_virtual_file_stats

  1. select size_on_disk_bytes from sys.dm_io_virtual_file_stats(DB_ID(N'SSAdventureWorks_201110291450'),1)


Find max size within the database’s sys.database_files or sys.master_files. The size number shows in the size column is in Pages. So it needs to times 8192 to get the actual byte.

  1. use SSAdventureWorks_201110291450;
  2. select size from sys.database_files
  4. select size  from sys.master_files where database_id=14


21760* 8192 = 178 257 920 bytes

Lets try create table and insert rows in the source database to see how the snapshot file size grow.

  1. -- Create The test Table
  2. use AdventureWorks;
  4.          (number INT IDENTITY(1,1) NOT NULL,
  5.           name varchar(50) NOT NULL,
  6.           updateTIme datetime default getdate(),       
  7.          )
  8. --- Insert Test data
  9. DECLARE @count INT
  10. SET @count = 0
  11. WHILE (@count < 10000)
  12. BEGIN
  13.    Insert into TB_1 (name)values('SUMMER')
  14.    SET @count = (@count + 1)
  15. END
  17. select * from dbo.TB_1
  18. go
  19. select size_on_disk_bytes from sys.dm_io_virtual_file_stats(DB_ID(N'SSAdventureWorks_201110291450'),1)

As you can see when the source database changes, the snapshot file size will start increasing.


To restore (revert) the database from snapshot

  1. Only one snapshot can be use to restored. we have to drop all other snapshot first.
  2. Only can be restore to the source database, we can not restore the snapshot to the different database.
  3. If the source database contain FILESTREAM file group, the file group will be marked offline in the snapshot. The snapshot can not be used to revert it to the source database.

Msg 3138, Level 16, State 2, Line 2
The database cannot be reverted because FILESTREAM BLOBs are present.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

  1. use master;
  2. RESTORE DATABASE AdventureWorks  from
  3. DATABASE_SNAPSHOT = 'SSAdventureWorks_201110291450';
  4. GO

Drop the snapshot

  1. DROP DATABASE SSAdventureWorks2008R2_201110291550


Database snapshots

File steam storage

2 Restore Database (1) Point In Time Recovery


We can only do the point in time recovery under Full or Bulk insert mode.

  1. -- Setup the test ENV
  3. -- Backup the database
  4. BACKUP DATABASE [AdventureWorks_new] TO  DISK ='C:\dump\AdventureWorks_new_20111029.db' with format
  5. -- Create The test Table
  7.          (number INT IDENTITY(1,1) NOT NULL,
  8.           name varchar(50) NOT NULL,
  9.           updateTIme datetime default getdate(),       
  10.          )
  11. --- Insert Test data
  12. DECLARE @count INT
  13. SET @count = 0
  14. WHILE (@count < 40)
  15. BEGIN
  16.    Insert into TB_1 (name)values('SUMMER')
  17.    -- wait for 10 second
  18.     WAITFOR DELAY '00:00:10'
  19.    SET @count = (@count + 1)
  20. END
  22. BACKUP LOG [AdventureWorks_new] TO  DISK ='C:\dump\tran\AdventureWorks_new.1.log' with format
  24. -- Slect the data
  25. select * from TB_1 where TB_1.number = 5;
  27. -- Delete Data
  28. delete TB_1 where TB_1.number =5;

The row number 5 is gone after line 28 delete statement. Lets try to restore it.

First, we take another tran log backup

  2.   BACKUP LOG [AdventureWorks_new] TODISK ='C:\dump\tran\AdventureWorks_new.2.log' with format;

Then, lets restore with below orders:

  • Full backup with no recovery
  • Tran log 1 with no recovery and time stamp
  • Tran log 2 with no recovery and time stamp
  1. -- Restore the FULL DB BACKUP with norecovery
  2. -- KIll all the connection and start the restore
  3. use master;
  4.   RESTORE DATABASE [AdventureWorks_new] fromDisk ='C:\dump\AdventureWorks_new_20111029.db' with NORECOVERY;

Since it is NORECOVERY, the database shows as Restoring…


  1. -- Restore the 1 tran dump with norecovery
  2. RESTORE LOG [AdventureWorks_new] from  Disk = 'C:\dump\tran\AdventureWorks_new.1.log'
  3. with NORECOVERY , STOPAT = '2011-10-28 22:32:01.133';
  4. -- Restore the 2 tran dump till the time it is being deleted and
  5. RESTORE LOG [AdventureWorks_new] from  Disk = 'C:\dump\tran\AdventureWorks_new.2.log'
  6. with NORECOVERY , STOPAT = '2011-10-28 22:32:01.133';
  7. --- RESTORE DB

STOPAT must follow by the time stamp where you want SQL Server stop applying the transaction log. Any log before that time (Including that time will not apply to the database)


In the production environment , if row being delete by accidently, it may not be a good idea to do this type of restore. As the database is not available during the time of restore. Most importantly, you will take a risk what if restore fail and you are losing the entire db. It is the best you can restore the database in the test environment and recover from there.

Friday, October 28, 2011

3 Disable Local security policy on Win Server 2008

Disable the the password complexity is not recommend. This is just for my convenience in my lab environment. 

Click Start and then Administrative Tools and then click on Local Security Policy. In Local Security Policy, click on Account Policies and then click on Password Policy. Under Password Policy, double click on “Password must meet complexity requirements” and then select the Disable.

1 Trouble shooting xp_cmdshell

xp_cmdshell is Microsoft extend store procedure. It will spawn the cmd shell under sql server process to let user to run any command or script under windows host. By default it is disable on the sql server.

Enable xp_cmdshell

  1. EXEC master.dbo.sp_configure 'show advanced options', 1
  3. EXEC master.dbo.sp_configure 'xp_cmdshell', 1

Lets see how that works, Below code will ping the loop back interface and sleep for 500 secs

  1. xp_cmdshell "ping -n 500 > nul"

From procexp, you can see sql server has spawn the child process cmd and it is running the same credential as SQL server account.


By default, only sysadmin can use xp_cmdshell. As alternative, we can create the sp_xp_cmdshell_proxy_account to allow user to run it.

When I tried the sp_xp_cmdshell_proxy_account, I got below error

  1. EXEC sp_xp_cmdshell_proxy_account 'PO7\summer','XXXXXXXXXX'

Msg 15137, Level 16, State 1, Procedure sp_xp_cmdshell_proxy_account, Line 1
An error occurred during the execution of sp_xp_cmdshell_proxy_account. Possible reasons: the provided account was invalid or the '##xp_cmdshell_proxy_account##' credential could not be created. Error code: '0'.

So I tried to look around the error message, the dm_os_ring_buffer usually store the os feed back information.

  1.   select r.ring_buffer_address,
  2. r.ring_buffer_type,  
  3.   dateadd (ms, r.[timestamp] - sys.ms_ticks, getdate()) as record_time,
  4. cast(r.record as xml) record  
  5. from sys.dm_os_ring_buffers r  
  6. cross join sys.dm_os_sys_info sys

unfortunately, this time it does not tell us something we do not already know. ( Msg 15137, Level 16, State 1)


It is because the UAC policy on my Win7 cause the issue. To resolve the issue. Just run the SSMS under administrator mode.

Test/Verify xp_cmdshell

To test the proxy account, we need to grant the user permission to use xp_cmdshell too, otherwise you will get the error:

Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

Below example use execute as to test the permission

  1. EXECUTE AS LOGIN = 'PO7\summer' ;
  2. GO
  3. use master;
  4. exec xp_cmdshell "whoami"
  5. REVERT ;

Here is the syntax to go grant the regular user to use xp_cmdshell through proxy account.

  1. USE [master]
  2. GO
  4. GO
  5. GRANT EXECUTE ON xp_cmdshell TO [PO7\summer]


How to fix the error code 1326 when use xp_cmdshell

Since sp_xp_cmdshell_proxy_account store user’s password inside the sql server, if the windows account password get changed, the proxy account needs to be updated. Otherwise, it will get the error message as:

Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
An error occurred during the execution of xp_cmdshell. A call to 'LogonUserW' failed with error code: '1326'.

To fix the issue, run the

USE master;
ALTER CREDENTIAL [##xp_cmdshell_proxy_account## ] WITH IDENTITY = 'DOMAIN\USERID', 


Change the credential does not require bounce the SQL server to make it effective.

Another scenario for error message is the account get drop and recreate in the domain, even the name is the same but the SSID has been changed. In this case, we have to drop the credential and recreate it.

select name,sid,type_desc,create_date,modify_date from sys.server_principals 



To drop and re create the credential:

sp_xp_cmdshell_proxy_account NULL;
sp_xp_cmdshell_proxy_account 'PO7\summer','XXXXXXXXXX';
GRANT EXECUTE ON xp_cmdshell TO [PO7\summer]


Trouble shooting XP_cmdshell hang

What happen if windows host hang or the xp_cmdshell just hang without returning the result?

Use sp_who2 to identify the spid

Use sys.dm_exec_requests and sys.dm_exec_sql_text to confirm it is the xp_cmdshell

  2.     session_id,
  3.     status,
  4.     command,
  5.     sql_handle,
  6.     database_id
  7. FROM
  8.     sys.dm_exec_requests
  9. WHERE
  10.     session_id = 60
  11. GO


  1. SELECT   
  2.     st.text
  3. FROM
  4.     sys.dm_exec_requests r
  6.     sys.dm_exec_sql_text(sql_handle) AS st
  7. WHERE
  8.     r.session_id = 60
  9. GO

Till now, we still have no way to identify what was xp_cmdshell is running

After issuing the kill command. The spid is in the roll back state.


You will find below in the sql server error log. The host process ID is process id where you issue the kill command NOT the child process id where the xp_xmdshell is running. image

The easiest way to get the hanging child process id is using procexp but the procexp is the GUI tool

The command line version of it is pslist

pslist \\hostnmae –t


taskkill /PID 8036 /F


Check proxy account for xp_cmdshell

select * from sys.credentials
  • sys.credentials store the ##xp_cmdshell_proxy_account## information, we can query it to verify whether proxy account is created or not.

: image

Thursday, October 27, 2011

1 Dedicated administrator connections(DAC)


Enable for SQL Express

DAC connection is disable by default in sql express. It can be enable via trace flag 7806. There are 2 ways to enable the trace flag. DBCC TRACEON or via sql server start up parameters.

Method 1: DBCC

  1. -- Enable the Trace
  2. DBCC TRACEON (7806,-1)
  3. -- Check the trace flag status
  5. -- Turn off the trace flag
  6. DBCC TRACEOFF(7806)


However, I can not get that working and still see the error message.

Method 2: Trace flag on the start up parameters

Trace flag -T 7806


After bonce the sql server, you can see the DAC port being enable in the sql server error log.

SQL Server error log shows the DAC port


Lets try to connect to the DAC.


SQLCMD -S Servername -E –A


If you try to connect to the DAC from SSMS, you will get below error message

Dedicated administrator connections are not supported. (Microsoft.SqlServer.Management.SqlStudio.Explorer)


That’s because we are trying to connect through object explorer.

The supportive way is

SSMS>New>Database Engine Query > ADMIN:Servername (on the server name)



To check whether the connection is really using the DAC, you can join the sys.dm_exec_connections and sys.endpoints as below.

  1. select * from sys.dm_exec_connections ec
  2. join sys.endpoints e on (ec.endpoint_id=e.endpoint_id)
  3. where'Dedicated Admin Connection'
Only one DAC is allowed at any given moment.
Below shows the error message if 2nd DAC try to connect.
By default, DAC only allow to connect to the sql server locally. To enable the remote connection:
  1. sp_configure 'remote admin connections', 1
  2. GO
  4. GO

All my previous example shows connect sql server using name instance, this will require SQL browser running to allocate the port number for DAC

if you need to connect to DAC via hostname and port number,

1. Remote connection must be enable

2. Must manually find the port number form error log

  1. sqlcmd -S tcp:PO7,51515



How to: Use the Dedicated Administrator Connection with SQL Server Management Studio
Using a Dedicated Administrator Connection

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates