Restoring SQL Server databases - tips and tricks?

Question

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.

Answer 1

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".

Answer 2

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.