We're using SQL server 2005 SP2 and want to schedule a job that can automatically backup and restore DB at mid-night. We use sql agent schedule job and run the following backup and restore scripts:
DB backup scripts:
BACKUP DATABASE [MPServer] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\MPServer.bak' WITH NOFORMAT, INIT, NAME = N'MPServer-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM, CONTINUE_AFTER_ERROR
DB Restore script:
RESTORE DATABASE [MPServer] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\MPServer.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
Both above scripts would use master database to run.
However, some errors occasinally occur during DB restore job indicating the database is in use and cannot be restored and the job would fail. Here is the error detail:
Date 1/3/2011 3:24:47 PM
Log Job History (Restore)
Step ID 1
Job Name Restore
Step Name Restore
Sql Severity 16
Sql Message ID 3013
Operator Net sent
Retries Attempted 0
Executed as user: SITNACMAI52\apvcsadm. RESTORE cannot process database 'MPServer' because it is in use by this session. It is recommended that the master database be used when performing this operation. [SQLSTATE 42000] (Error 3102) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Any options for the sql scripts that can force to restore and overwrite the cuurent db even there's still some connections connected to the db? If not then is it necessary to auto clean up all the connections before restore DB? Is there any sql scripts that can clean up all the connections for dedicated db? Can take db offline task can clean up all the connections? Thanks a lot.