Sql-server – SQL server row count

sql serversql-server-2008sql-server-2008-r2

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.

Best Answer

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)
Related Question