Online Restore of Databases

  • 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.

  • 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.

  • 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.

  • 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
  • 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.

  • 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]

  • Thanks Sean,

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

    Regards,

    Greg.

  • BTW whats stopping you to configure replication ??

    Cheers

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

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

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