Can I restore a database, from the same version SQL server, with different updates?

Question

The main server ( the one I backed up the database ) is :

Microsoft SQL Server 2014 - 12.0.4100.1 (X64)
Apr 20 2015 17:29:27 Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: )
(Hypervisor)

I'm trying to restore the database in this server:

Microsoft SQL Server 2014 (RTM-CU14) (KB3158271) - 12.0.2569.0 (X64) May 27 2016 15:06:08 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 10586: )

But the restore never completes. using SP_WHOISACTIVE the complete_percentage is null ( for like, 10 minutes ).

In my own machine, I could restore the database, this is my server:

Microsoft SQL Server 2014 (SP1-CU7) (KB3162659) - 12.0.4459.0 (X64) May 27 2016 15:33:17 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 10586: ) (Hypervisor)

What can cause this problem? the database is 140GB. Is there a place where I should verify?

With verifyonly the file is valid.

I'm using T-SQL to the restore:

restore database Database
    from disk ='F:\Database.BKP'
        with
            move 'Database' to 'path\database.mdf',
            move 'Database2' to 'path\database.ndf',
            move 'Database3' to 'path\database_FULLTEXT',
            move 'Databaselog' to 'path\database_log',stats=1

One thing that I noticed was the wait type Async_io_

Is there a way to verify if the disk has a problem, but via SQL SERVER?

Oh, and when I pause the script, I got this message:

Msg 3122, Level 16, State 1, Line 4 File initialization failed.

RESTORE cannot continue. Msg 3204, Level 16, State 1, Line 4 The

backup or restore was aborted. Msg 3013, Level 16, State 1, Line 4

RESTORE DATABASE is terminating abnormally. Query was cancelled by

user.

Answer 1

But the restore never completes. using SP_WHOISACTIVE the complete_percentage is null ( for like, 10 minutes ).

10 minutes is not nearly enough time to qualify for using the word "never"!

What can cause this problem? the database is 140GB.

If you don't have instant file initialisation (see here and here amongst other places) turned on then it will be trying to zero 140Gb+ of disk space ready to restore the data - this could easily take longer than ten minutes^[*] and during that part of the process the percent done will not count (this only counts as the pages are restored from the backup file to the data files, not while the data files are being created).

^{[*]: ~14Gb in ~10 minutes is about 240MByte/second - how fast can your I/O subsystem write and what other activity is going on at the same time?}

To answer the question in the title:

Can I restore a database, from the same version SQL server, with different updates?

Yes, you should be able to, even if the updates that differ are service packs, though I generally recommended that you only restore upwards where possible just in case.

Answer 2

you can check the restore remaining time with the following script, maybe that could tell you how much time it will take to restore:

select
session_id,
convert(nvarchar(22),db_name(database_id)) as [database],
case command
when 'BACKUP DATABASE' then 'DB'
when 'RESTORE DATABASE' then 'DB RESTORE'
when 'RESTORE VERIFYON' then 'VERIFYING'
when 'RESTORE HEADERON' then 'VERIFYING HEADER'
else 'LOG' end as [type],
start_time as [started],
dateadd(mi,estimated_completion_time/60000,getdate()) as [finishing],
datediff(mi, start_time, (dateadd(mi,estimated_completion_time/60000,getdate()))) - wait_time/60000 as [mins left],
datediff(mi, start_time, (dateadd(mi,estimated_completion_time/60000,getdate()))) as [total wait mins (est)],
convert(varchar(5),cast((percent_complete) as decimal (4,1))) as [% complete],
getdate() as [current time]
from sys.dm_exec_requests
where command in ('BACKUP DATABASE','BACKUP LOG','RESTORE DATABASE','RESTORE VERIFYON','RESTORE HEADERON')

Answer 3

Try this: Start the database restore, let it run for a few minutes, then cancel the restore and rerun the restore command in the same command window without making any other changes.

This worked for me, when I faced the same error

Msg 3122, Level 16, State 1, Line 4 File initialization failed.

Possible logic behind this: The first restore attempts to initialize the DB files, the second restore goes ahead with the restore without attempting to initialize. Can this logic be wrong? Absolutely.