When working with MSSQL server I have spotted some general problems during restoring databases. Below I’m presenting some common solutions that can save you development time.
If you are restoring database backup taken from the same database but running on different server eg. restoring production database on your test server – you may get security error when trying to connect with the old DB user.
This is because DB user account was generated on different server where windows security token was different. The solution is to recreate the user on restored database:
//recreate user ALTER User DB_user with LOGIN = DB_user; //set password ALTER LOGIN DB_user WITH PASSWORD = 'secret'
If you must overwrite you test database with the different one, you need to first close all current database connections, otherwise you may get an error saying that database is in use. The quick solution for that is the script below.
It simply sets database off-line and back on-line. This is sometimes much faster then waiting for all connections to be closed in other ways (note: use it only on the test server)
ALTER DATABASE [MyDB] SET OFFLINE WITH ROLLBACK IMMEDIATE ALTER DATABASE [MyDB] SET ONLINE GO
If you will get following error: “Cannot execute as the database principal because the principal “dbo” does not exist, this type of principal cannot be impersonated, or you do not have permission”. This is solution:
ALTER AUTHORIZATION ON DATABASE::[myDatabase] TO [sa];
If your database backup takes a lot of space but you know that there is not too much data in it, you can simply shrink the log files before creating a backup.
Use MyDB ALTER DATABASE MyDB SET RECOVERY SIMPLE; GO DBCC SHRINKFILE ('MyDB_log',TRUNCATEONLY); GO ALTER DATABASE MyDB SET RECOVERY FULL;
Finally, if you want to grant execute permissions to all stored procedures at once (on the restored database), you can use following script:
CREATE PROCEDURE [dbo].[procExecuteOnProcs] ( @User varchar(max) ) AS declare procs cursor for select routine_schema, routine_name from information_schema.routines where routine_type = N'PROCEDURE' declare @Schema nvarchar(128), @Name nvarchar(128), @SQL nvarchar(512) open procs fetch next from procs into @Schema, @Name while @@fetch_status = 0 begin set @SQL = N'grant execute on [' + @Schema + N'].[' + @Name + N'] to ' + @User exec sp_ExecuteSQL @SQL fetch next from procs into @Schema, @Name end close procs deallocate procs
Hope these simple tricks will help you. Please note that you can also automate above scripts to be part of other processes.