patternsqlMinor
Restoring SQL Server databases - tips and tricks?
Viewed 0 times
databasestipssqlandrestoringservertricks
Problem
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:
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.
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.
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.tablesInstance 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.
Solution
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".
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".
Context
StackExchange Database Administrators Q#8493, answer score: 5
Revisions (0)
No revisions yet.