DOS batch script to restore a SQL 2000 database and grant permission to ASPNET account
I've found the most reliable method of moving SQL Server databases between servers is a simple backup / restore. The restore is a bit of a pain in the neck, though - it takes some unnecessary clicky clicky in the SQL Server Enterprise Manager (ugh).
Here's a simple DOS batch script that does away with most of the repetitive work. I put a copy of this in a \Project\Database directory, fill in the DBNAME variable, and from then on the restore is as simple as copying the SQL Backup file over and running the batch file. The granting permission to the ASPNET user thing is important if you're using trusted connections, which is a durn good idea. Barry Dorrans has some great info on that here.
set DBDIRECTORY=C:\Program Files\Microsoft SQL Server\MSSQL\Data
TITLE Restoring %DBNAME% Database
::PUT DATABASE IN SINGLE USER MODE TO ALLOW RESTORE
osql -E -d master -Q "alter database %DBNAME% set single_user with rollback immediate"
osql -E -d master -Q "restore database %DBNAME% from disk='%~dp0\%DBNAME%.bak' WITH MOVE '%DBNAME%_Data' TO '%DBDIRECTORY%\%DBNAME%_Data.MDF', MOVE '%DBNAME%_Log' TO '%DBDIRECTORY%\%DBNAME%_Log.LDF'"
::GRANT PERMISSION TO ASPNET USER
osql -E -d %DBNAME% -Q "sp_grantdbaccess '%COMPUTERNAME%\ASPNET'"
osql -E -d %DBNAME% -Q "sp_addrolemember 'db_owner', '%COMPUTERNAME%\ASPNET'"
::RESTORE TO MULTI USER
osql -E -d master -Q "alter database %DBNAME% set multi_user"
- Replace DBNAME variable with the actual database name.
restore database osql command should all be on one line; it's just formatted as is to look pretty. (fixed)
- This assumes you're using some defaults - for instance, you're saving your SQL database backup as DBNAME.bak, your database file names are DBNAME_Data.MDF and DBNAME_Log.LDF, and your DBDIRECTORY is the default directory. If this isn't the case, it's pretty easy to change the script to suit your bizarre tastes.
- This is built to be run in the same directory as the backup file. If that's not the case, replace the %~dp0 silliness with the path to the backup file.
You're running this on a server that can stand a SQL Server restart. I use this on my development server, so that's just fine. (fixed, see update note below)
UPDATE: Original version restarted SQL Server before performing the restore. Based on Sean's comment, I've changed this to use single user mode instead.