Sql-server – restore a database, from the same version SQL server, with different updates

restoresql server 2014

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.

Best Answer

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.

Related Question