Will a COPY_ONLY backup initialise the log chain?

 General  2 Responses »
Feb 272013
 

While setting up some databases into an Availability Group, a colleague asked me why, after switching to the FULL recovery model, he was getting the message “A full backup is required”, given that he was planning on using the “Full” data synchronization, which states:

Starts data synchronization by performing full database and log backups for each selected database. These databases are restored to each secondary and joined to the availability group.

The obvious question there is then “Why do we need to perform a full database backup if the Availability Groups wizard will be taking one for me?”

Looking at the script generated by the Availability Groups wizard, we can see the following steps:

:Connect SQL01

BACKUP DATABASE [AW2012] TO  DISK = N'\\SQL01\DataSynch\AW2012.bak' WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5
GO

:Connect SQL02

RESTORE DATABASE [AW2012] FROM  DISK = N'\\SQL01\DataSynch\AW2012.bak' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5
GO

:Connect SQL01

BACKUP LOG [AW2012] TO  DISK = N'\\SQL01\DataSynch\AW2012_20130213234500.trn' WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5
GO

:Connect SQL02

RESTORE LOG [AW2012] FROM  DISK = N'\\SQL01\DataSynch\AW2012_20130213234500.trn' WITH  NORECOVERY,  NOUNLOAD,  STATS = 5
GO

Clearly, the full backup is a COPY_ONLY backup. From Books Online:

A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. Copy-only backups serve this purpose.

…

Copy-only full backups (all recovery models)

A copy-only backup cannot serve as a differential base or differential backup and does not affect the differential base.

Restoring a copy-only full backup is the same as restoring any other full backup.

What Books Online doesn’t mention is whether a COPY_ONLY backup can be used as a base for the first full backup taken after switching the recovery model from SIMPLE to FULL. Whenever I find ambiguous information, I like to run a test:

-- Switch the database into the SIMPLE recovery model
ALTER DATABASE TestDB SET RECOVERY SIMPLE

-- Switch the database into the FULL recovery model
ALTER DATABASE TestDB SET RECOVERY FULL

BACKUP LOG TestDB TO DISK = 'D:\SQLBackup\TestDB_LOG_1.trn' WITH COPY_ONLY
-- Failed! Msg 4214, BACKUP LOG cannot be performed because there is no current database backup.

BACKUP DATABASE TestDB TO DISK = 'D:\SQLBackup\TestDB_COPY_ONLY.bak' WITH COPY_ONLY
-- Successfully took a COPY_ONLY backup

BACKUP LOG TestDB TO DISK = 'D:\SQLBackup\TestDB_LOG_2.trn' 
-- Failed! Msg 4214, BACKUP LOG cannot be performed because there is no current database backup.

BACKUP DATABASE TestDB TO DISK = 'D:\SQLBackup\TestDB_FULL.bak'
-- Success – full backup complete

BACKUP LOG TestDB TO DISK = 'D:\SQLBackup\TestDB_LOG_3.trn'
-- Success - the log chain is initialised

We have the proof – COPY_ONLY backups will not initialise the log chain.

The bottom line is that if you skip using the wizard and use a T-SQL script to initialise the databases, you can change these COPY_ONLY backups to normal FULL backups. However, you probably should be ensuring your database is already in the FULL recovery model before you start your HA adventures. From an engineering perspective, it’s a lot easier to write a wizard that assumes you’ve already got the database in the correct state than to try and fix the state as part of the wizard.

T-SQL Tuesday #39 – Powershell Sorts!

 General  1 Response »
Feb 122013
 

This month’s T-SQL Tuesday is brought to us by Wayne Sheffield (blog | twitter), who asks for some awesome uses of Powershell to manage SQL Server.

As with most scripts, I had a flash of inspiration that I could use Powershell to make my life a little bit easier. I spent 2 hours to save 10 seconds of effort, but it keeps saving me often. Six months ago, I needed to sort a list of server names. Nothing too exciting, and most people would fire up Excel to perform the sort.

You would first copy your unsorted list into the clipboard, paste it into Excel, highlight the rows (or the entire column) and hit “Sort”.  Then you’d select the newly sorted rows, copy it back into the clipboard, and paste it elsewhere.

That’s way too much effort.

Let’s try this workflow instead:  You copy your unsorted list. You click on an icon in your taskbar, watch a Powershell window flash up for half a second, and then you paste your clipboard, noting that the results are sorted.

The script isn’t too long, only complicated by the fact we need to hook into System.Windows.Forms to gain access to the clipboard:

## This will only work if you run Powershell with the -sta switch:
#
#    Powershell -sta

function Get-ClipboardText()  
{
    Add-Type -AssemblyName System.Windows.Forms
    $tb = New-Object System.Windows.Forms.TextBox
    $tb.Multiline = $true
    $tb.Paste()
    $tb.Text
} 

function Set-ClipboardText($text)
{
    # Load System.Windows.Forms assembly.
    $null = [Reflection.Assembly]::LoadWithPartialName(“System.Windows.Forms”)

    $dataObject = New-Object windows.forms.dataobject

    # Add generated strings to data object.
    $dataObject.SetData([Windows.Forms.DataFormats]::UnicodeText, $true, $text)

    [Windows.Forms.Clipboard]::SetDataObject($dataObject, $true)
}

$data = Get-ClipboardText;
$data = $data.Replace("`r`n", "|||||||")
$data.Split("|||||||") | Sort-Object | Set-Variable -Name sorted

[string] $output = ""

foreach ($line in $sorted)
{
    if ($line -ne "") 
    {
        $output += $line + "`r`n"
    }
}
Set-ClipboardText($output)

I’m sure there must be an easier way to perform the sort than my 10 line solution, but this was the best solution I was able to come up with in the limited time I allowed myself.

In order to set this up as an icon, we simply create a shortcut with the target: 

powershell.exe -sta ./Sort-Clipboard.ps1

making sure to “Start In” the path with the Sort-Clipboard.ps1 file. The –sta flag is necessary to run Powershell in Single Threaded Apartment mode, in order to gain access to the Clipboard class.

If you want to use the Sort-Clipboard.ps1 script from within Powershell, you’ll need to spin up a new Powershell session in –sta mode, which can be done using a simple one-line script:

Start-Process powershell -args "-sta -noprofile .\Sort-Clipboard.ps1"

Credit and code for these techniques must go to Brian Reiter, Dave Regal, and the MSDN Powershell blogging team. Thank you all!

Server names are one example, but there’s many more situations where you might want to sort a list in day-to-day database management. You could adapt the code to sort anything – the really useful part here is the access to the clipboard.