Losing my primary keys!

Question

Losing my primary keys!

paul-1139668

Grasshopper

Points: 12
More actions
August 7, 2009 at 5:05 am

#208361

I am doing a "full" backup of the db in SQL Express, but it doesnt maintain my indexes??! I have googled and havent found anything to say yup its this!
Can some one point me in the right direction!? What am I doing wrong?!
Cheers

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

When a backup is restored it always recreates the database exactly as it was at the time of the backup. If there are no indexes after the restore it means that there were no indexes when the DB was backed up.

Check the DB before you run the backup, make sure that no one's 'accidentally' dropped indexes.

How are you doing database backups?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 399289 More actions · Answer 2

If you're using the command "backup database" then you're getting a bit-by-bit copy. Nothing should be lost, data, structure, procedures... nothing.

"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

paul-1139668 Grasshopper Points: 12 More actions · Answer 3

How are you doing database backups?

in sql management studio, right click on the database, tasks -> backup

thanks

Lowell SSC Guru Points: 323547 More actions · Answer 4

it sounds like this was your order of operations, and explains why when you restore, you lose your keys:

1. created the database.

2. create the table

3. backed up the database

4. added pk's and indexes

5. restored the database, which reverted to the pre-step 4 savepoint.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 399289 More actions · Answer 5

Well, it's possible, if you're backing up to the same place every time, that your backups are aggregating within a single file. If so, when you run a restore, you might be seeing an older backup. You can open the file and see all the backups within it by checking the contents when you're in the file selection window. Be sure you're using the right backup. Nothing in the backup process can change your data or your structure, but it is possible to restore the wrong backup, which will look like data was lost or structure was changed.

"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

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 6

... always the indian, never the arrow 😉

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.