Piecemeal restore of a partitioned table

A few weeks ago I presented my Advanced Backup and Restore session on SQL Rally in Denmark.
One of the topics that I showed was the piecemeal restore functionality. This allows you to restore your database in “pieces”. The files or filegroups that have been restored are already accessible while the others are still restoring. Jes Borland (blog | twitter) has made a good blog post on this topic.
During the session, I got a question if it was possible to restore filegroups that were used by a partioned table, which should result that the table would be partially available.
My answer to that question was that you had to restore all the files before the table becomes available, but I figured out that my answer was not correct.

With this blog post I want to correct my answer.

Setup the test scenario

For testing purposes, I created a database, MyDatabase, with 5 filegroups: PRIMARY, Partition1, Partition2, Partition3 and Partition4.

Once the database was created I added my partition function and partition scheme.
As you can see, the partition function has 3 boundaries (1, 100, 1000) and the partition scheme is created that specifies the filegroups to hold each one of the four partitions.

AS RANGE LEFT FOR VALUES (1, 100, 1000);
TO (Partition1, Partition2, Partition3, Partition4);

Next step was creating a partitioned tabled based on the partition scheme

--Create Partitioned table
create TABLE PartitionedTable 
	(PartitionColumnID int not null,
	Name varchar(100) not null,
	PartitionColumnID ASC	
) ON [myRangePS1]([PartitionColumnID])	
)ON myRangePS1(PartitionColumnID);

And fill the table with 1000 records

declare @i as int
set @i=1
while @i<=1000
		insert into PartitionedTable
		select @i,'Name' + CAST(@i as varchar)
		set @i=@i+1

The query below give me the number of records per partition

SELECT $PARTITION.myRangePF1(PartitionColumnID) AS Partition, 
COUNT(*) AS [COUNT] FROM PartitionedTable
GROUP BY $PARTITION.myRangePF1(PartitionColumnID)
ORDER BY Partition ;

Restore the partitioned table

Before I can start with a piecemeal restore, I need to have a backup of course of my database. Don’t forget to change the <BackupPath> in the script.

--Take a new FULL BACKUP
TO DISK = N'<BackupPath>MyDatabase.bak' WITH COMPRESSION,INIT

Ok, let’s drop the database and begin with the piecemeal restore

USE [master]

First of all, I’m going to restore the PRIMARY filegroup so that my database is already available and “ONLINE” again.

FROM DISK = N'<BackupPath>MyDatabase.bak'
RESTORE LOG [MyDatabase] FROM DISK='<BackupPath>MyDatabase_Log.trn' WITH RECOVERY

When I check the status of my filegroups, you can see that my PRIMARY file is already ONLINE

SELECT [name], [state_desc] 
FROM [MyDatabase].sys.database_files;

Secondly, I restore the Partition1 filegroup. Don’t forget to always apply the transaction log backup.

USE master;
FILEGROUP = 'Partition1'
FROM DISK = N'<BackupPath>MyDatabase.bak'
RESTORE LOG [MyDatabase] FROM DISK='<BackupPath>MyDatabase_Log.trn' WITH RECOVERY

When I checked the status of my filegroups again, the Partition1 filegroup is now ONLINE

I know that in this filegroup, there is 1 record stored (see “the number of records per partition”), so let’s see if I can query the table

use [MyDatabase]
select PartitionColumnID,Name from PartitionedTable

Well, I get an error that the second partition cannot be accessed, but if you look at the Results, the query returned 1 record!

I restored the Partition2 filegroup

USE master;
FILEGROUP = 'Partition2'
FROM DISK = N'<BackupPath>MyDatabase.bak'
RESTORE LOG [MyDatabase] FROM DISK='<BackupPath>MyDatabase_Log.trn' WITH RECOVERY

And executed the same query. I got again an error that Partiton3 filegroup was not accessible but the query returned 100 rows. This is correct because I know that there were 100 records in my first 2 partitions of the table.

Of course, if you’re using the correct where-clause and only query the ONLINE filegroups, you will not get any errors at all.

--Try to select the tables
use [MyDatabase]
select PartitionColumnID,Name from PartitionedTable where PartitionColumnID<=100

Finally, I also restored Partition3 and 4 and my table was completely available again.


My answer during my session was not correct. It is possible to restore a partitioned table in pieces and make part of the table available while the other filegroups are still in recovering state.
This can be really helpful to improve your RTO. Instead of restoring the complete table in 1 time, you can restore only the partitions that you really need to get your application back up and running.
The less important partitions, that for example only contain history data, can be restored in a later stage.

Well, as always, you’re never too old to learn something!

Speaking at PASS SQLRally Nordic 2015

PASS SQLRally Nordic is back – bigger than ever! And it looks like they are going to be right! Well, if you look at the agenda, it will definitely be a top event!
There are a lot of international top speakers like Brent Ozar, Grant Fritchey, Itzik Ben-Gan and many more. But also other top industry speakers from across the Nordic region and the world for 3 days of SQL Server and BI training.
The event will be held on March 2-4, 2015, at Tivoli Congress Center in Copenhagen, Denmark. I’ve never been in Copenhagen before. So, I’m really looking forward to visit the city and catch a glimpse of the Little Mermaid.

I’m really happy and proud that I was selected as one of the speakers!
I’m going to talk about Advanced Backup and Restore. Most DBA’s know the standard options for implementing a basic backup and restore plan. But there are also not so well known options that can help you with a more advanced recovery plan and that will increase the speed of your backups and restores. My session is bulk loaded with demos and it will give you a good idea what infrequently features can be helpful in your environment, like partial backup, backup encryption, piecemeal restore … You will also learn how you can improve the performance of your backups! My timeslot is on the third day, Wednesday, March 4, from 12:45am – 01:45pm. The full agenda of the SQL Rally Nordic can be found here.

Well, I hope to see you all in Copenhagen in March!


Restore System Databases

Did you ever had to restore your system databases? It’s not that difficult when the databases are still available (or at least some version of it). However, what do you need to do when all system databases are gone?
For example when your system database LUN is missing J. Now, Books Online is not really explaining this in detail so I’ve made this blog post to make it clearer.

Before you read on, please make sure that you have a full backup of all your system databases.

Restore MASTER database

SQL Server requires the master database before a SQL Server instance can run at all. Some version of a master database must exist. Without that “version” the service will not start and it will be impossible to restore the backup.
So, the first step is to rebuild the system databases.

To rebuild the system databases, you can follow these steps on http://msdn.microsoft.com/en-us/library/dd207003.aspx

Insert the SQL Server installation media into the disk drive or mount the iso-file.

From a command prompt window (run as administrator), enter the following command.


<InstanceName> Is the name of the instance of SQL Server. For the default instance, enter MSSQLSERVER.
<accounts> Specifies the Windows groups or individual accounts to add to the sysadmin fixed server role.

When the rebuild is finished, you will find new (empty) data and log files in the data folder of the instance.

Once you have the “dummy” master.mdf and ldf file in place, you need to start up the instance in single user mode. This is done by adding the –m startup parameter. Open the SQL Server Configuration Manager to add it. (see screenshot below)

In the SQL Server Configuration Manager, start the SQL Server service. Once it is started we finally can restore the master database. The example starts sqlcmd and executes a RESTORE DATABASE statement that restores a full database backup of master from a disk device.

The REPLACE option will replace the current master database with the one in the backup file. Also notice that after the restore, the instance is shutting down. Remove the –m option again from the startup parameters before you restart SQL Server. You will see that all your user databases are back online and that all your server logins exist.

Restore MASTER database on a failover cluster

The procedure is slightly different than the restore of a stand-alone instance. The rebuild of the system databases must be performed on the active node and the SQL Server resource in the corresponding cluster application group must be taken offline before performing the procedure. I’m trying to restore the master database on a Windows 2012 R2 Failover Cluster and I’m running SQL Server 2012 SP2 Enterprise Edition.

I started with a rebuild of the system databases and started up the SQL Server instance with –m parameter (single user mode), as described above.

To restore the master database, I open a SQL CMD but I always get the error that only one administrator can connect to the instance. The problem is that as soon as I startup the service, the sp_server_diagnostics process kicks in and takes the first connection…
After searching for a while, I figured out that you have to startup a cluster instance in single user mode in a different way. More information can be found on http://msdn.microsoft.com/en-us/library/ms188236.aspx . Check the Note for Clustered Installations.

Restore MASTER database alternative

Another alternative method to restore the MASTER database could be the following:

  • Startup the instance with the new empty master database after the rebuild of the system databases
  • Restore the original master database as a user database (use a different name)
  • Shut down the service again
  • Remove the master.mdf and ldf files.
  • Rename the restored master database back to master.mdf and mastlog.ldf
  • Restart the service

The method above is also working but the above mentioned procedures are the best practices for SQL Server.

Restore MODEL database

The model database can be restored as a normal database. However, if the database should be corrupt, you have to start up the SQL Server Instance with trace flag –T3608. It prevents SQL Server from automatically starting and recovering any database except the master database.

To activate the trace flag, open the SQL Server Configuration manager and add it to the startup parameters.

(Re)start your SQL Server Instance. Do not forget to remove this trace flag after the model database has been restored.

Open the SQL Server Management Studio and restore the original mode database from the backup

Restore MSDB database

Before you can restore the MSDB, you’ll have to stop the SQL Server Agent. You can do this with the SQL Server Configuration manager or just right click on the agent in the SSMS and click stop

The MSDB database can be restored like a normal user database.

Once the restore is finished, you can restart the SQL Server Agent and you should see all your jobs again in the SSMS.

Restore Tempdb database

You cannot backup or restore the tempdb. This database is re-created every time an instance of SQL Server is started. When the server instance is shut down, any data in tempdb is deleted permanently.

If your tempdb is corrupt, just restart your SQL Server Instance. In case, your disk is corrupt, you will have to move your tempdb. More information on how to move system databases can be found here http://msdn.microsoft.com/en-us/library/ms345408.aspx

Restore RESOURCE database

It is a hidden database. The Resource database resides in the mssqlsystemresource.mdf file, which contains only code. Therefore, SQL Server cannot back up the Resource database. It can be restored by a file-level restore in Windows or by running the setup program for SQL Server.



Limit RESTORE to a single database

A client came to me with the following question: “Is it possible to limit a login to restore only one specific database?”
Apparently, the application that he was trying to upgrade took a backup of the database before the upgrade. In case of failure, the upgrade procedure would automatically restore the backup.
Good idea to restrict that user but I don’t know by hart if that is possible….

Let’s figure it out!

I’ve created a test login “RestoreUser” that isn’t a member of any server role, except Public. The login is linked to a database user that is member of the db_owner role of my Sales database.

Looks like I’m not able to perform a restore of the database with the current user rights.


If we check the server roles, there might be an option to use the dbcreator role. However, if we look at the description…

Members of the dbcreator fixed server role can create, alter, drop, and restore any database.

There might be an issue as we don’t want the user to create or restore other databases.
My test results confirmed this, so dbcreator role … no option…

Custom Server role

The database was running on a SQL Server 2012. Maybe I can create a new server role to put a restriction on the restore and add my login into that group.
The only option that looked interesting was the CREATE and ALTER DATABASE option. No RESTORE database option could be selected and the other roles were not relevant.

When I tried the restore, it didn’t work either. And even if it should have worked, the description mentions “any” database so again the same issue as the dbcreator role…

DDL Trigger

Maybe I can capture the RESTORE command in a DDL Trigger? In the trigger, I could check the user and the database that is been restored.
I’ve checked the DDL Events that are possible and the RESTORE command is not a DDL Event…

DDL trigger…? No option…

Database Owner

If you check the RESTORE command on Books Online and look for the security part, you will see the following:

If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database

We already know that the sysadmin and dbcreator are not an option, but let’s try “the owner of the database” option.

To change the owner of the database, you can execute this command. Of course, change the name of the database and the user to your database name and user.


Well… the restore of my database succeeded!

And if I try to restore another database it didn’t work.

Mission accomplished!

Is it wrong to change the Database Owner?

The database owner gets full privileges on the database itself. So be careful if you’re changing the database owner, that the former database owner can still access the database.
In this post of Brent Ozar, there is a check that the database owner should be sa. But in fact, it’s not wrong to have another database owner than sa.
It’s even recommended to create a dedicated account with zero privileges as the database owner. Check this post of Andreas Wolter on SQL Server Database Ownership.

Now, to come back to my situation. It’s not wrong to have my RestoreUser as the database owner. The user had already full privileges because it was member of the db_owner role.


Changing the database owner will allow the user to restore the just that single database. Other restores and creation of new databases will not be possible.
There are a few restrictions that you have to keep in mind. The database must already exist. If it doesn’t you must have CREATE DATABASE permission to restore the database. As a consequence, the user will be able to create any other database as well.
This option will also not work if you don’t allow database ownership for that user. If that’s not possible, you’re out of options.

I don’t know if there are other possibilities to restrict the user to restore 1 single database. If you do know, just drop me a comment 😉


How to change the database owner for the secondary replica database

When you perform a restore of a database to a SQL Server Instance where the database doesn’t exist, the owner of the database is changed to the user that is executing the restore. Actually, this also the case for attaching a database. The ownership can give the login extra permissions which in some cases you don’t want. A good rule of thumb is to choose “sa” as the database owner. This can be easily done by executing the following query after the restore is done.


Just keep in mind, when you change the owner of the database that the user that owned the database might have less or no permission anymore on the database.

Unfortunately, if you restore a database as read-only or if you leave it into restoring mode for AlwaysOn Availability Group configuration, you cannot change database owner anymore.
Even if you setup an AlwaysOn Avaliability Group with the wizard of the SQL Server management studio, the owner of the database on the secondary replica will be the user that is executing the wizard. Quite obvious because in the background the wizard is performing the restore J.
Your database must be in a read/write mode before you can change the database owner.

If the database is read-only, it can briefly be set to Read/Write for the modification, and then set back. That shouldn’t be an issue.
In case of AlwaysOn Availability Groups, you will have to perform a failover to the secondary replica, change the database owner and failback to the original primary replica.
But, I can imagine that there will be situations where a failover will not be possible, like for example asynchronous commit mode or just the business that doesn’t want downtime.

A possible workaround could be to manually prepare the secondary database for the availability group but perform the restore as the user sa. The following script can be used

EXECUTE AS login='sa'
RESTORE DATABASE [YourDatabaseName] FROM DISK = '<YourBackupPath>' 

More information on Manually Prepare a Secondary Database for an Availability Group can be found here

It’s a simple script but it does change the owner of the database to sa and you don’t have to worry about a failover to set the correct database owner.


Restore a database from Windows Azure with SQL Server 2014 CTP2

In my previous blog post I explained how you could take a backup of a database to Windows Azure Storage with SQL Server 2014 CTP2.
In this blog post I’m going to explain how you can restore your database from Windows Azure.

If your database is still available on your server, right click on the database, select Tasks à Restore à Database


Apparently, the management studio knows that a backup has been taken to Windows Azure and it opens the “Connect to Windows Azure Storage” window.

First of all you need to fill in the Storage Account. That will be the one that you have created when taken the backup to Windows Azure.
To know the correct storage account, go to your Windows Azure web site, select Storage and choose the correct storage account.
In my example here, this will be “koherastorage”.


You should also fill in the Account Key that is linked to your storage. To access stored content, applications and also the SSMS, use one of two 512-bit storage access keys.
Click on the button in the main storage window. A new window with the manage keys will open.

Copy the primary Access Key on your clipboard and past it in the Account Key field of the “Connect to Windows Azure Storage” windows of the SSMS.
You will notice that the key will be masked with *stars*

Select the correct credential to connect to Windows Azure. You can use the credential that you have created while creating the backup.

Click the Connect button and the restore window will appear as we all know it, like in previous SQL Server editions.


Select the appropriate backup sets that you want to restore and click OK to start the restore.
On top, you see that a tail-log backup will be taken. And yes indeed, it’s also stored to my Windows Azure Storage Container!
You should get a similar result like this J


As you can see, restoring a backup from Windows Azure is as simple as restoring a database from local disk.
However, there is still one question that keeps me busy. How will I backup or restore my database if I don’t have any internet connection available…?

Have fun!




Restore Database Enhancements in SQL 2012

In SQL 2012, Microsoft has introduced some nice restore database enhancements.
The major enhancements are:

  • Point-in-time restore has now a visual timeline that allows you to quickly select the target time and perform your restore.
  • Page Restore worked already in SQL 2008 (R2) and SQL 2005 but it has now a nice user interface. It allows you to check your database for corrupt pages and restore them from a good backup file.

In this blog, I’ll give you an overview how to use these 2 new features.

Point-in-time restore

In the Object Browser of your SSMS, right click on Databases and select “Restore Database”
In this example, I will perform a restore of the Adventureworks2008R2 database.  I selected Device to get my backup files.  Just press the […] button


In the locate backup file window, I select all the backup files (Full backups and Transaction Logs) that have been made.  To create the backups, I just created a simple Maintenance Plan.
Click on OK.


Now all the backup sets are in the list (this is not new…).  As you can see, there is a new button called “Timeline”.  Click on it to open the timeline interface.


Now, you can choose to restore to the last backup taken or choose a specific date and time.  With the timeline, you can scroll to the restore time that you want. On the timeline you can also see what types of backups will be used to perform the restore. Once you selected the correct time, just press the OK button.


Now press OK again, to start your restore. A restore plan is automatically generated and your database is restored till the requested time.



How cool is that!

Page Restore

To perform a page restore,I first need to have a corrupt database Knipogende emoticon and you also need to have a GOOD backup file,which means, without the corrupt page.
As you can see below, I did a DBCC checkdb and my database is indeed corrupt.


Let’s fix this database!
Right click on your DB, select Tasks – Restore – Page


In the Restore Page window, the database is selected and the Pages grid is automatically showing the  damaged pages. You can also run DBCC CHECKDB, by clicking on the button “Check Database Pages”, to find out if there are more damaged pages in the database. You also need to set the location for the Tail-Log backup file. The Backup sets grid shows you all the backups that can be used to fix your pages.



Just click on the OK button to start the page restore


When I check my database again with DBCC CHECKDB I see that the damaged page has been fixed Emoticon met brede lach


I think those 2 new features will make the life of the DBA just a little bit easier.

Have fun!