Todays challenge was to move a production database down to development nightly. It needed to happen after 4pm each weekday. Easy right?

This was all to be done in sql 2005, but the same ideas could be applied to 2000.

My plan was to create a backup at 4pm, copy it to the destination server and then do a restore.

1. Create the backup.

Normally sql will try to append date and time on the end of the name. I needed the same name all the time, so I made that happen. Saved to a local drive with the name of backup_export.bak .

2. Copy the backup.

This was a foray into unexplored territory for me. DOS seemed like a good solution – since sql server only seems to be able to back up to local drives (am I right about this?). I found the xcopy command and created a .bat file which looked like this:

t:
cd backup\inventory
xcopy inventory_export.bak file://myserver/e$/BACKUPs/Inventory
/Y

First i change to the t: drive, where my backup is and then move to the inventory directory. The xcopy command is next which says xcopy myfile tohere
/Y means to not confirm overwriting, which is what I want. Being inexperienced I tried a few ridiculous things before using the UNC path to get straight to my server.
I scheduled this .bat file to run with windows scheduled tasks shortly after my database backup is created. The backup was small and only took 30 seconds to create.
3. Set up the restore
Created a maintenance plan with a restore task on the development server to restore the file. This is the trickyest part. We go in and modify the job that the maintenace plan creates and add steps before and after to do prep and cleanup.
First we disconnect users who are connected – otherwise it won’t restore.
ALTER DATABASE dbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Then we restore the database
Next we need to fix the sql logins which have been messed up by copying a database from one server to another:
DECLARE @SQLString VARCHAR(7500)
SELECT @SQLString = ”
SELECT @SQLString = @SQLString + ‘EXEC sp_Change_Users_Login ”UPDATE_ONE”,’+QUOTENAME([Name],””)+’,’+QUOTENAME([Name],””)+’; ‘FROM SysUsersWHERE IsSQLUser = 1AND (SId IS NOT NULL AND SId <> 0x0)AND SUSER_SNAME(SId) IS NULLORDER BY [Name]

PRINT @SQLString
EXEC (@SQLString)
Next you need to fix any windows users which were there by granting them access. I didn’t have this problem, but you just need to do a grant for each windows user in the db.
Here is some info on doing that in 2005 sp2:
ALTER USER – addition of WITH LOGIN clause
This new syntax for ALTER USER allows remapping a user to another login, by changing the user’s SID value to match the login’s SID. This can be used to repair orphaned users. It works for both Windows and SQL Server logins, unlike sp_change_users_login, which worked only for SQL Server logins. This should become the preferred command for fixing orphaned users. If the user is a Windows user and has a Windows user name (domain\user), then the user will be automatically renamed to the login name as part of the remapping operation. from laurentiu
As Laurentiu says, beware using sp_change_users_logon ‘report’ to find orphaned users – it only returns sql users, not windows.