Is there any simple script which I can use to count the user objects in a database? Reason is, I'm planning to do an upgrade from SQL 2008R2 to SQL 2019 & my plan is to take a count of the objects on the old SQL and 2019 so I can compare and see if there's any missing objects.
I know typical full backup & restore won't cause any missing objects, however I have a bad experience where normal backup & restore didn't picked up some indexes from the source DB. This has causes tons of headache as users complaining on new DB running exceptionally slow & further checking found that some critical indexes wasn't migrated over to the new DB
First, an answer to your question, then, some discussion on the nature of backups.
To just see the objects in a database:
SELECT COUNT(*) FROM sys.objects AS o
WHERE o.type <> 'S';
That will give you a count of all non-system objects. Done.
Now, to backups. A database backup, done through the use of the BACKUP command, not some export/import process, but backups, is a page-by-page copy of the database. At the lowest storage level, it copies all the pages of the database. There's a bunch of foofaraw around the log too. However, the core is, it copies all the pages. I've had this fight so many times with dev & test teams. "You forgot to backup one of the indexes." No. I didn't. Because I can't forget to backup an index. The backup process is a page-by-page copy of the database. I can't forget anything. It's all there. Inevitably, after investigation, indexes were missing or different in the restored database because they were missing or different in the database they were taken from, or, the backup was older, or, the backup was from a different database. However, there's no chance that you missed indexes. None. Zero. Zilch. Nada. It has to be, can only be, some other explanation.
This does assume we're talking about a SQL Server backup using the BACKUP command and not some other process. If it's another process, might want to check that out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 16, 2020 at 3:09 pm
What Grant noted. Nothing from the source db is missing from the backup. The only chance for something not included is that it is in a transaction that does not commit. Otherwise, it's all there.
June 16, 2020 at 3:12 pm
if you have a backup file and you are not sure what it is from or when it was taken, you can use the RESTORE HEADERONLY command to examine it:
it can even work with a file on the network using a UNC path, e.g.
RESTORE HEADERONLY FROM DISK = N'\\backupshare\database.BAK';
June 17, 2020 at 1:19 am
Noted Grant! Thanks for the explanation. You're are absolutely correct that nothing can be missing from SQL BACKUPs, I did argue with the developers on this as well. However with the lacking of any evidence and merely logical explanation doesn't satisfy them & they insist backups were to blame.
So to avoid any issues for future upgrades & migration, I've decided to do a counts for non-system objects before and after the backup & restore.
June 17, 2020 at 11:28 am
Oh I hear you. The reason I could so easily spew out that rant is because of the number of times I've had to explain it. It got to the point where some of the lead devs, when a junior dev would comment about a missing index, would shut them down with "It's a page by page copy. You're wrong. Let's move on" while casting fearful eyes my way. Ha!
Do what you're doing, absolutely. However, I'd also go through the explanation for them. If not for your own sanity, in order to ensure that there's faith in the backup process. If people think it's some kind of wonky export that occasionally drops stuff randomly, why on earth would you rely on it in an emergency. Education is the key to ensuring everyone knows how things work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply