sql job to backup and restore db automatically

  • Hi,

    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

    go

    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

    go

    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

    Server SITNACMAI52

    Job Name Restore

    Step Name Restore

    Duration 00:00:00

    Sql Severity 16

    Sql Message ID 3013

    Operator Emailed

    Operator Net sent

    Operator Paged

    Retries Attempted 0

    Message

    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.

    Best regards,

    Wallace

  • To restore a database successfully, there should not be any other connection to that DB.

    Now to answer your questions:-

    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?

    NO

    If not then is it necessary to auto clean up all the connections before restore DB?

    YES

    Is there any sql scripts that can clean up all the connections for dedicated db?

    You have check all the active connections and kill each of them. You can write a script also or search on the web.

    Can take db offline task can clean up all the connections?

    YES

    ===========================================
    Better try and fail than not to try at all...

    Database Best Practices[/url]

    SQL Server Best Practices[/url]

  • Hi Wallace,

    You can query sysprocesses to check which session is currently active on that database and kill it.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • Now I have to ask - why would you do this?

    It really does not make any sense to backup the current database, then immediately restore over the existing database that you just backed up.

    I could see this process if you were copying to a secondary copy (read only, reporting, etc...). Or, if you were restoring to another instance as a reporting instance. But, restoring over the existing system with the current backup - just doesn't make sense.

    When I have to restore over an existing database that might have connections, I use the following:

    ALTER DATABASE {database} SET OFFLINE WITH ROLLBACK IMMEDIATE;

    RESTORE DATABASE ...

    This will disconnect any active connections to the database immediately, then start the restore operation. I only do this on test/dev/report systems and never on a production system.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams-493691 (1/3/2011)


    Now I have to ask - why would you do this?

    It really does not make any sense to backup the current database, then immediately restore over the existing database that you just backed up.

    I could see this process if you were copying to a secondary copy (read only, reporting, etc...). Or, if you were restoring to another instance as a reporting instance. But, restoring over the existing system with the current backup - just doesn't make sense.

    When I have to restore over an existing database that might have connections, I use the following:

    ALTER DATABASE {database} SET OFFLINE WITH ROLLBACK IMMEDIATE;

    RESTORE DATABASE ...

    This will disconnect any active connections to the database immediately, then start the restore operation. I only do this on test/dev/report systems and never on a production system.

    Since we want to backup the latest database and restore the yesterday database image for our training center(i.e. the latest data is not crucial), and this job is done by daily.

    Thanks and regards,

    Wallace

  • So, you are restoring the backup to another system - not the same system.

    It looked like you were backing up the database, and restoring it to the same system. At least, that is how I read it.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I think you have the MPServer database, which, you are trying to restore, specified in the "Database" text box in the job step definition. That would run the job with a connection to the MPServer database. Change that database to something else like master.

  • Try this Way

    USE db_name

    GO

    ALTER DATABASE db_Name SET SINGLE_USER WITH ROLLBACK AFTER 10 SECONDS -- you chagne value of Seconds

    GO

    Use master

    Go

    RESTORE DATABASE [db_name]

    FROM DISK = N'File Path'

    WITH FILE = 1,

    Move N'db_data' To N'file Path'

    Move N'db_log' To N'file path.ldf'

    USE db_name

    GO

    ALTER DATABASE db_name SET MULTI_USER

    GO

    Aim to inspire rather than to teach.
    SQL Server DBA

  • As replayed earlier, you can change the database to single mode and then do your restore and change it back to multi mode, but the problem with that is, if your database that you are restoring is huge, then it is going to take sometime for the restore to complete, in which the database is going to be unusable during that time. I would suggest the following:

    - Restore to a different database name

    - Once restore is done to the second database, kill all the connections to the live database

    - Followed by a quick rename of the database names (switch the live one with the one you just restored)

    - Drop the old live database

    This way your downtime for the users very minimal.

    Thanks,

    Fitsum

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply