MSSQL database backup/ restore helper scripts

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.