Bat SQL Script to restore Database

When developing a site I quite often have to restore SQL databases and this can take up a lot of time.

If you have to frequently restore SQL backups you can use this script:

@echo off

set BAKFILE=%cd%\[database-backup].bak
set DBNAME=[database-name]
SET SERVERINSTANCE=[.\SQLEXPRESS2008R2]
SET DBUSER=[DatabaseUser]

set /p RESPONSE="Restore %BAKFILE% to %DBNAME%? (Y/N)"

if NOT %RESPONSE%==y goto END

::clear any existing connections
sqlcmd -E -S %SERVERINSTANCE% -Q "ALTER DATABASE %DBNAME% SET SINGLE_USER WITH ROLLBACK IMMEDIATE"

::restore DB
set SQLCMD=-E -S %SERVERINSTANCE% -Q "RESTORE DATABASE %DBNAME% FROM DISK='%BAKFILE%'"
sqlcmd %SQLCMD%

::remove user if it already has access to the DB
sqlcmd -E -S %SERVERINSTANCE% -Q "USE %DBNAME%; EXEC sp_revokedbaccess '%DBUSER%'"

::add user to the DB and grant required access
sqlcmd -E -S %SERVERINSTANCE% -Q "USE %DBNAME%; EXEC sp_grantdbaccess '%DBUSER%', '%DBUSER%'"
sqlcmd -E -S %SERVERINSTANCE% -Q "USE %DBNAME%; EXEC sp_addrolemember 'db_owner', '%DBUSER%'"
sqlcmd -E -S %SERVERINSTANCE% -Q "USE %DBNAME%; EXEC sp_addrolemember 'db_datareader', '%DBUSER%'"
sqlcmd -E -S %SERVERINSTANCE% -Q "USE %DBNAME%; EXEC sp_addrolemember 'db_datawriter', '%DBUSER%'"

pause

:END

Update the following variables to match our needs:

• BAKFILE – the name of the file to restore
• DBNAME – the name of the database to overwrite
• SERVERINSTANCE – name of the SQL server instance
• DBUSER – Existing database user to grant access to 

Only change the parts in square brackets.

Save it as a .bat file in the same directory as the .bak file you want to restore.

When you run it you will be prompted to continue, then it will clear connections to the existing database, restore the backup and grant the required user read/write access.

Be careful if you use and make sure you keep backups, it might make things a little quicker but it's also easier to make mistake.