0

I had an activity for restoring a full database in SQL server 2008.

I had to restore the database will norecovery since I had to restore the differential backups as well which will be shared everyday.

My query is how do I check the row count of a database which is under recovery?

Thank you.

1

You can RESTORE the database and leave it in STANDBY mode. Then it will be read-only, but you can still apply additional backups. EG

drop table if exists AdventureWorks2017.dbo.foo
create table AdventureWorks2017.dbo.foo(id int identity, a int)

go
backup database AdventureWorks2017 to disk='c:\temp\aw.bak' with init, format

insert into AdventureWorks2017.dbo.foo(a) values (1)

backup database AdventureWorks2017 to disk='c:\temp\aw.diff.01.bak' with init, format, differential

insert into AdventureWorks2017.dbo.foo(a) values (1)

backup database AdventureWorks2017 to disk='c:\temp\aw.diff.02.bak' with init, format, differential


go

RESTORE DATABASE [AdventureWorks2017_copy] 
FROM  DISK = N'C:\temp\aw.bak' 
WITH   
MOVE N'AdventureWorks2017' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2017_copy.mdf',  
MOVE N'AdventureWorks2017_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2017_copy_log.ldf',  
NOUNLOAD,  
STATS = 5,
STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2017_standby.bak'

go
select * from AdventureWorks2017_copy.dbo.foo

go

RESTORE DATABASE [AdventureWorks2017_copy] 
FROM  disk='c:\temp\aw.diff.01.bak'
WITH STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2017_standby.bak'

go

select * from AdventureWorks2017_copy.dbo.foo

go

RESTORE DATABASE [AdventureWorks2017_copy] 
FROM  disk='c:\temp\aw.diff.02.bak'
WITH STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2017_standby.bak'

go

select * from AdventureWorks2017_copy.dbo.foo

outputs

id          a
----------- -----------

(0 rows affected)

Processed 112 pages for database 'AdventureWorks2017_copy', file 'AdventureWorks2017' on file 1.
Processed 1 pages for database 'AdventureWorks2017_copy', file 'AdventureWorks2017_log' on file 1.
RESTORE DATABASE successfully processed 113 pages in 0.056 seconds (15.651 MB/sec).
id          a
----------- -----------
1           1

(1 row affected)

Processed 112 pages for database 'AdventureWorks2017_copy', file 'AdventureWorks2017' on file 1.
Processed 1 pages for database 'AdventureWorks2017_copy', file 'AdventureWorks2017_log' on file 1.
RESTORE DATABASE successfully processed 113 pages in 0.056 seconds (15.651 MB/sec).
id          a
----------- -----------
1           1
2           1

(2 rows affected)
8
  • Hi, thank you. Can I change the recovering database to standby mode when applying the differential backup or should I restore the full database again in standby mode?. – user133197 Jul 7 '20 at 5:41
  • Either works. The example in the answer applies multiple differential backups. – David Browne - Microsoft Jul 7 '20 at 7:11
  • Hi but the read only database will now only accept transaction logs and not differential backups. – user133197 Jul 8 '20 at 17:51
  • Did you run the sample I posted? It applies two different differential backups. – David Browne - Microsoft Jul 8 '20 at 18:26
  • Hi I'm using the gui, instead of sql commands and I can only see transaction log restore option. – user133197 Jul 8 '20 at 18:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.