Post reply

Restore a database using a Job in SQL Server 2005

  • May 16, 2007 at 6:00 pm

    #120138

    Hi guys, I have a question, how can I restore a database using a Job in a specific schedule, I'm working with SQL Server 2005...

    Thanks...

  • May 16, 2007 at 7:59 pm

    #706975

    Create a sql agent job with a restore script like the one below; if you need to restore over an existing db then firstly would be to set the db (UserDBCopy below) in single user mode, then add "WITH REPLACE" to the restore line in script, then bring your restored db in multi-user mode.

    Declare

    @vDataFileName

    Varchar(255),

    @vFileName

    Varchar(100),

    @vDataPath

    Varchar(100),

    @vLogPath

    Varchar(100),

    @vRestoreString

    Varchar(1000)

    Set

    @vDBName='UserDB'

    Set

    @vFileName='YourBackupPath\BackupFileName.bak'

    Set

    @vDataFileName='UserDB_Data'

    Set

    @vDataPath='YourDataPath\UserDBCopy_Data'

    Set

    @vLogFileName='UserDB_Log'

    Set

    @vLogPath='YourLogPath\UserDBCopy_Log'

    Set

    @vRestoreString = 'RESTORE DATABASE [UserDBCopy] FROM

    DISK '''

    + @vFileName

    + ''' WITH FILE = 1, MOVE N''' +

    @vDataFileName

    + '''TO N'''+ @vDataPath + ', MOVE N'''+ @vLogFileName + '''TO N'''

    + @vLogPath +', NOUNLOAD,

    STATS = 10'

    Exec

    (@vRestoreString)

    GO

    Good luck.

  • May 17, 2007 at 3:08 pm

    #707263

    You don't need to set user databases in single user mode.  To have a succesfull restore you do need to make sure none is connected to the database though.

     

  • May 18, 2007 at 10:26 am

    #707525

    ..so you can use some kind of kill users stored procedures in the previous step...

  • May 19, 2007 at 4:06 pm

    #707657

    you can do the same using alter database command...

    ALTER DATABASE <DBNAME> set single_user rollback immediate...

    check BOL for correct syntax...

     

    MohammedU
    Microsoft SQL Server MVP

  • May 20, 2007 at 8:20 pm

    #707717

    I like to do it this way, because it prevents anyone from connecting to the database.  Even a single user connection will cause the restore to fail.

    use master
alter database My_Database_Name set offline with rollback immediate

  • May 23, 2007 at 12:46 pm

    #708415

    Hello,

    http://blog.sqlauthority.com/2007/02/25/sql-server-restore-database-backup-using-sql-script-t-sql/

     

    Kindest Regards,

    Pinal Dave
    sqlauthority.com

Viewing 7 posts - 1 through 6 (of 6 total)

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