Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Anyone have any tips and tricks when it comes to restoring SQL Server databases?

I recently restored a .bak from one server/instance to another, using SQL Server 2005 on both, and have run into the following issues (so far):

All of the identity insert values were reset to their original values (1 in my case). A pain in the butt until I found the following command to go through all tables, and reseed to the current value + 1:

 exec sp_MSforeachtable @command1 = 'DBCC CHECKIDENT (''?'', RESEED)'

Permissions were gone. The database level accounts came over of course (with their role assignments), but most of the DB permissions for each Role were gone...meaning read,insert,delete,execute etc permissions were missing on each table,view, and stored procedure. I wrote some scripts like the below to rectify, but still a pain in the ass.

 select 'GRANT SELECT ON ' + TABLE_NAME + ' TO rolename' from Information_schema.tables      

Instance level permissions obviously aren't present if restoring to a new instance...but that was kind of a pain since it seems you can't recreate an instance level account if the account already exists at the database level. What's the best approach here? In my case I deleted the database account, the created the instance, then granted permissions using scripts like the above.

Generally I'm just curious about some best practices, when it comes to restoring SQL Server databases... and/or tips on somehow avoiding the issues I encountered. I'm just trying to pave the way for the most error free restores I can.

share|improve this question
2  
Can you give more detail as to how you restored the backup? This does not sound right at all. A backup taken and restored will contain all the objects and permissions. Was this perhaps a really old backup? –  datagod Nov 28 '11 at 20:33
    
I ran the backup on one server (right click on DB, All Tasts, Backup), created a .bak file, transferred the .BAK file, and then right clicked on the other server/instance, and selected Restore Database, and pointed it at the .BAK file. –  Albert Nov 30 '11 at 15:26
    
Try running the backup again using "with verify". You need to make sure the backup is valid before restoring it. Something is very wrong here. –  datagod Nov 30 '11 at 18:42
add comment

2 Answers 2

up vote 4 down vote accepted

It looks like you did not restore a backup but you just transferred the tables with some kind of bcp tool.

Common issues with backup/restore are, in my experience: a) Logins missing or mismatched, because they are in the master database b) Collation problems when the collation of the server on which you restore the backup is not the same as the one from which you made the backup.

I had exactly the problems you had on sqlserver 7 when I had a primary / slave setup based on replication. The day I switched the slave as a master I had to reseed all the identity columns and process all the grants manually. This was not a surprise this was because replication does not replicate system tables and because the identity columns were set as "NOT FOR REPLICATION".

share|improve this answer
    
"I had exactly the problems you had on sqlserver 7 when I had a primary / slave setup based on replication. The day I switched the slave as a master I had to reseed all the identity columns and process all the grants manually. This was not a surprise this was because replication does not replicate system tables and because the identity columns were set as "NOT FOR REPLICATION"." This was it. I did restore a backup...but it was a backup of the slave database in a log-shipping setup. Thanks! –  Albert Dec 7 '11 at 18:56
add comment

A database backup when restored will not do what you have described. Identity values will remain where they are. Permissions will all stay where they are.

Logins can be created to match the user if the user already exists. This can be done by specifying the SID for the login when it's created. Look up CREATE LOGIN in books online and it'll give you the syntax to do this. You can get the correct SID from the sys.database_principals system catalog in your database.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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