Why Restore with Norecovery is incomplete?

[DL]

Background Info:
Instance A: SQL Server 2005 on Windows Server 2003 OS
Instance B: SQL Server 2005 on XP Prof w/ SP2

Process:
backup a database on Instance A like this:
step 1 -- Management Studio for Instance A
BACKUP DATABASE myAppNameDB
TO DISK = 'c:\Temp\myAppNameDB_FullRecovery.bak'
GO

step 2 -- copy the .bak file to a network drive and then copy it to
local drive of Instance B

step 3 -- -- Management Studio for Instance B
restore it to target
RESTORE DATABASE myAppNameDB
FROM DISK = 'C:\dbData\myAppNameDB_trueFullRecovery.bak'
WITH NORECOVERY
GO

Outcome of the above step:
{myAppNameDB} (restoring...)

Why?

TIA

[DL]

Never mind. I just restore it using the Restore feature of the
Management Studio.

On Sep 4, 9:50*am, DL <[e-mail]:

Background Info:
Instance A: SQL Server 2005 on Windows Server 2003 OS
Instance B: SQL Server 2005 on XP Prof w/ SP2

Process:
backup a database on Instance A like this:
step 1 -- Management Studio for Instance A
BACKUP DATABASE myAppNameDB
* * * * * * * TO DISK = 'c:\Temp\myAppNameDB_FullRecovery..bak'
GO

step 2 -- copy the .bak file to a network drive and then copy it to
local drive of Instance B

step 3 -- -- Management Studio for Instance B
restore it to target
RESTORE DATABASE myAppNameDB
* * FROM DISK = 'C:\dbData\myAppNameDB_trueFullRecovery.bak'
* * WITH NORECOVERY
GO

Outcome of the above step:
{myAppNameDB} (restoring...)

Why?

TIA

[Erland Sommarskog]

DL (ta********@gmail.com) writes:

Background Info:
Instance A: SQL Server 2005 on Windows Server 2003 OS
Instance B: SQL Server 2005 on XP Prof w/ SP2

Process:
backup a database on Instance A like this:
step 1 -- Management Studio for Instance A
BACKUP DATABASE myAppNameDB
TO DISK = 'c:\Temp\myAppNameDB_FullRecovery.bak'
GO

step 2 -- copy the .bak file to a network drive and then copy it to
local drive of Instance B

step 3 -- -- Management Studio for Instance B
restore it to target
RESTORE DATABASE myAppNameDB
FROM DISK = 'C:\dbData\myAppNameDB_trueFullRecovery.bak'
WITH NORECOVERY
GO

It's incomplete because you said so. That is, you specified WITH
NORECOVERY, which tells SQL Server that you plan to restore log backups
as well.

If you change your mind, you can say "RESTORE DATABASE db WITH RECOVERY".

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[DL]

On Sep 4, 4:55*pm, Erland Sommarskog <[e-mail]:

DL ([e-mail]) writes:

Background Info:
Instance A: SQL Server 2005 on Windows Server 2003 OS
Instance B: SQL Server 2005 on XP Prof w/ SP2

Process:
backup a database on Instance A like this:
step 1 -- Management Studio for Instance A
BACKUP DATABASE myAppNameDB
* * * * * * * TO DISK = 'c:\Temp\myAppNameDB_FullRecovery.bak'
GO

step 2 -- copy the .bak file to a network drive and then copy it to
local drive of Instance B

step 3 -- -- Management Studio for Instance B
restore it to target
RESTORE DATABASE myAppNameDB
* * FROM DISK = 'C:\dbData\myAppNameDB_trueFullRecovery.bak'
* * WITH NORECOVERY
GO

It's incomplete because you said so. That is, you specified WITH
NORECOVERY, which tells SQL Server that you plan to restore log backups
as well.

If you change your mind, you can say "RESTORE DATABASE db WITH RECOVERY".

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Links for SQL Server Books Online:
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:http://www.microsoft.com/sql/prodinf...ns/books.mspx- Hide quoted text -

- Show quoted text -

Like your witty banter. And I know what "RESTORE DATABASE db WITH
RECOVERY" would do. However, I believe MS knowledge piece about
Database Mirroring specfically requires full restore WITH NORECOVERY.
Anyway, I' ve resolved one key problem in the process.

[Erland Sommarskog]

DL (ta********@gmail.com) writes:

Like your witty banter. And I know what "RESTORE DATABASE db WITH
RECOVERY" would do. However, I believe MS knowledge piece about
Database Mirroring specfically requires full restore WITH NORECOVERY.
Anyway, I' ve resolved one key problem in the process.

I'm not sure what you are trying to say. In any case, I could not see
anything about mirroring in your original post. You will have to excuse,
but while I do bits of SQL Server fairly well, I am less skilled when
it comes to thought-reading.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx