Home » discuss » Public forums » iMIS Community

SQL Server: Problems with security when doing nightly backup and restore

Posted April 22nd, 2009 by dcrosby
|

We are trying to automate a backup of our imis database (imis) and restore to a different database (imisYesterday) nightly. We are on version 10.6 and running SQL 2005 in SQL 2000 compatibility mode. The backup and restore is no problem (using the code below), but what we’re having problems with is the logins are now broken for the restored database. In iMIS under system setup, User Passwords, there are two buttons – Update All SQL Logins and Re-Create All SQL Logins - which will fix this problem but we need this to happen automatically. Are these buttons accessing a stored procedure within iMIS that we can call or is there code that has been written to accomplish this?

BACKUP DATABASE [imis]
TO DISK = N'D:\restore\imis.bak'
WITH NOFORMAT,
INIT,
NAME = N'Full backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO

--Drops connections to imisYesterday
ALTER DATABASE imisYesterday SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--Database restore
RESTORE DATABASE [imisYesterday]
FROM DISK = N'D:\restore\imis.bak'
WITH FILE = 1,
MOVE N'imis_Data' TO N'C:\MSSQL\DATA\imisYesterday_Data.mdf',
MOVE N'imis_Log' TO N'C:\MSSQL\DATA\imisYesterday_log.ldf',
NOUNLOAD,
REPLACE,
STATS = 10
GO

‹ Installing WCMTaskCentre ›
login or register to post comments | printer friendly version

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

try sp_asi_CreateLogins...

On April 22nd, 2009 Henry Huey says:

...it does most of the work in 10.6. There may be some additional SQL necessary, depending on your security model.

login or register to post comments

Thanks

On April 30th, 2009 dcrosby says:

We had found the sp_asi_CreateLogins stored procedure but for some reason that doesn't quite do the trick. It will create the accounts but you still can't login.

Devin Crosby
California School Boards Association

login or register to post comments

Any time a database backup

On April 22nd, 2009 Paul Bradshaw says:

Any time a database backup is restored to a different database name, you need to run DBRepair.exe and click the "Prepare Database" button in the bottom center. This fixes the login info.

Alternatively, if you wish to automate this, you can search your disk for the file "FixSqlLogins.sql" (should be under ...imis.net\IMIS15DBUpgrade\ScriptFiles\IMIS153DBUpgrade if you're using version 15.0.3), and just run that script against the restored database.

That should fix you right up.

login or register to post comments