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.