Recover a Corrupt Database Using SQL Server

Here’s an informative post explaining some options when recovering a corrupt database using SQL Server: http://www.sqlservercentral.com/articles/Corruption/96117/

Don’t forget to backup the tail of the log!

Here’s the process, including a few extra tips if the original server is unavailable: http://www.sqlskills.com/blogs/paul/disaster-recovery-101-backing-up-the-tail-of-the-log/

Check last database restore date/time using SQL Server

Use one of the following scripts.

The first script is quick and simple:

SELECT *
FROM MSDB..RestoreHistory WITH (nolock)
WHERE destination_database_name = DB_NAME()
ORDER BY restore_date DESC

The second script also shows the backup file used during the restore:

SELECT [rs].[destination_database_name],
[rs].[restore_date],
[bs].[backup_start_date],
[bs].[backup_finish_date],
[bs].[database_name] as [source_database_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC

Source:

SQL Server full and transaction log backups completing but differential backups failing because of Veeam B&R configuration

Scenario

I recently refreshed our SQL Server Maintenance Plans, but System Centre Essentials has been intermittently reporting failures of the daily differential backups, with an error like this:

Executing the query “BACKUP DATABASE [DBNAME] TO DISK = N’…” failed with the following error: “Cannot perform a differential backup for database “DBNAME”, because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. BACKUP DATABASE is terminating abnormally.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

The strange thing is that the logs show all Weekly Full backups have completed without error. If I run a manual Full Backup, the next Differential Backup will complete, then the following day will fail.

Solution

First, I used the following query to show more details on the backups (make sure you select the DB in SSMS first):

[Read more…]

Understanding SQL Server Backups and Recovery

I’ve read many help files/articles/posts on backup and recovery over the years, but I’ve recently read some of the best yet that have filled in some gaps when it comes to SQL Server:

Using File-level restore mount in Veeam B&R 6.1

One of new features in Veeam Backup & Replication 6.1 is “File-level restore mount”:

When performed by the user interactively (using the backup console UI),
the Windows file-level restore process now mounts the volumes of the backed-up VM under C:\VeeamFLR.
This functionality is useful if you prefer to perform file-level recovery with native tools instead of Veeam
Backup Browser.

See this in action below.

[Read more…]

vCenter Server Appliance Service fails after upgrading ESXi hosts to 5.0 U1 Veeam backups now fail

Scenario

You’ve just upgraded your ESXi hosts from 5.0 to 5.0 U1. They updated fine and there are no issues with the VMs. However, you have noticed that your Veeam backups have started to fail.

Further investigation shows the vCenter Server Appliance (vCSA) service is not running.

[Read more…]

Copy Veeam backups to USB drive

As we are still rolling out VMware and Veeam B&R throughout our offices, we are not yet in a position to set up replication.

Until we are ready, I thought it was a good idea to at least backup the Veeam backup repository to USB3 drives, which would be rotated and taken offsite daily.

Initially I setup a ‘Veeam File Copy’ job, but later noticed it was taking ages to copy the new incremental files. It seems ‘Veeam File Copy’ jobs do not just copy the new files, but all files every time; not what I wanted.

[Read more…]

The best way to copy files using built-in commands – Robocopy

I’ve tried countless ways of copying and syncing files in the past, the last being the venerable SyncBack backup program.

Although it served its purpose, these days I’m not a fan of installing more applications than absolutely needed, so I thought I’d explore built-in options again.

I’ve used xcopy previously, but I wanted to try something new. Enter Robocopy…and no, it has nothing to do with this guy:

Robocopy was not created by Robocop!

[Read more…]

How to create a Backup Job for Veeam Backup & Replication 6

So, you’ve installed Veeam Backup & Replication 6, created a new Backup Repository, added a server, and now you want to create a Backup Job for Veeam Backup & Replication 6. Continue reading below to find out how.

[Read more…]

How to create a Backup Repository for Veeam Backup & Replication 6

Before you can start backing up Virtual Machines (VMs), you need to create a Backup Repository after installing Veeam Backup & Replication 6.

During installation, Veeam Backup & Replication 6 will create a Default Backup Repository with a path of C:\backup. As you may not want to store your backups on the C drive – where your Operating System files usually reside – follow these instructions to create another Backup Repository on a separate drive.

[Read more…]