Online Restore of Databases

Question

Online Restore of Databases

greg.bull

SSCrazy

Points: 2093
More actions
July 16, 2014 at 12:18 am

#287914

Dear All,
I'm using 2014 SE.
I know a backup of a database doesn't take the source DB offline, but then I need to move this DB to another server (for intensive reporting work). At present we restore the DB, but that means putting the DB in single user mode, kicking everyone off, and completing the restore.
This leads to three questions:
I see from 2014 EE notes that "online restore" is possible. Does anyone know if this is the only solution? EE is of course, mightily expensive.
Or perhaps it's possible to configure things to speed up the restore process somehow, so there is less downtime?
Also, the resource impact in creating the backup is quite high, perhaps there's a way to (apart from playing with backup compression) reduce the impact on the source server here?
Regards, Greg.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 1

There isn't a way to restore data without a user being locked out of the table it is stored in. The Online Restore allows you to partially restore a filegroup, allow access to that filegroup, while you then restore other filegroups.

http://msdn.microsoft.com/en-us/library/ms188671.aspx

The restore can be slightly speeded up if the files exist and you have IFI enabled. That way it's only a data copy taking place.

As far as the impact to the source server, if you see a high impact, your server is underpowered for the load. There isn't much you can do natively. You can add hardware, either to the source server, or perform striped backups to multiple devices to speed things up.

greg.bull SSCrazy Points: 2093 More actions · Answer 2

OK, thanks for confirming that. I understand the EE position on filegroup restore better now. Didn't appreciate IFI would make a difference here, so will investigate.

Cheers, Greg.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Steve Jones - SSC Editor (7/16/2014)
The restore can be slightly speeded up if the files exist and you have IFI enabled. That way it's only a data copy taking place.

OR, not AND. Either the files exist or you have instant file init on to speed up the 'create the files' part of the restore

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

greg.bull SSCrazy Points: 2093 More actions · Answer 4

Thanks Gail,

I checked and this was on...a copy of the DB already exists on the reporting server, we're replacing it though with a fresh copy.

Sean Pearce SSCoach Points: 15999 More actions · Answer 5

What about restoring to a temporary database. Then once the restore is complete you can do a drop and rename.

USE [master];

GO

DECLARE

@DataFile NVARCHAR(255) = N'C:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyRestoreDemo.mdf',

@LogFile NVARCHAR(255) = N'C:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyRestoreDemo_log.ldf';

IF EXISTS(SELECT * FROM MyRestoreDemo.sys.database_files WHERE physical_name = @DataFile)

BEGIN

SELECT

@DataFile = N'C:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyRestoreDemo1.mdf',

@LogFile = N'C:\MSSQL\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyRestoreDemo1_log.ldf';

END;

RESTORE DATABASE [MyRestoreDemo_Tmp]

FROM DISK = N'C:\Temp\MyRestoreDemo.bak'

WITH

FILE = 1,

MOVE N'MyRestoreDemo' TO @DataFile,

MOVE N'MyRestoreDemo_log' TO @LogFile,

NOUNLOAD,

STATS = 25;

GO

ALTER DATABASE MyRestoreDemo

SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

DROP DATABASE MyRestoreDemo;

GO

ALTER DATABASE MyRestoreDemo_Tmp

SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

ALTER DATABASE MyRestoreDemo_Tmp

MODIFY NAME = MyRestoreDemo;

GO

ALTER DATABASE MyRestoreDemo

SET MULTI_USER;

GO

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

greg.bull SSCrazy Points: 2093 More actions · Answer 6

Thanks Sean,

That'd be my best bet I think, a very fast cutover with hardly any down time. Many thanks.

Regards,

Greg.

AppSup_dba SSCrazy Points: 2215 More actions · Answer 7

BTW whats stopping you to configure replication ??

Cheers

greg.bull SSCrazy Points: 2093 More actions · Answer 8

Ah, because we receive a DB backup daily over SFTP from a disconnected data provider - another company.