July 16, 2014 at 12:18 am
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.
July 16, 2014 at 12:38 am
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.
July 16, 2014 at 1:24 am
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.
July 16, 2014 at 2:19 am
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
July 16, 2014 at 2:52 am
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.
July 16, 2014 at 3:25 am
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
July 16, 2014 at 8:12 am
Thanks Sean,
That'd be my best bet I think, a very fast cutover with hardly any down time. Many thanks.
Regards,
Greg.
July 16, 2014 at 10:53 pm
BTW whats stopping you to configure replication ??
Cheers
July 17, 2014 at 12:10 am
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