Automated Restore

• mwelcome

  Right there with Babe

  Points: 739

  More actions

  September 11, 2006 at 5:11 pm

  #62043

  I am trying to get an automated restore to run on a reporting instance of SQL Server.

  I tried setting the database to single user like this:

   Alter DATABASE CAT

   set SINGLE_USER

   WITH ROLLBACK After 60

   print 'Restoring Database'

   RESTORE DATABASE MyDB FROM DISK = @DBFileName

   

  It gives me an error message that the database is in use

  Any hints???

   

• Sreejith Sreedharan

  SSCertifiable

  Points: 5403

  More actions

  September 11, 2006 at 5:49 pm

  #659777

  1.Looks like some one is connected to your database. Could it be your process. When u restore your connection should point to Master DB not the DB that you are trying to restore?

  2.Will your restore be done in 60 seconds, if so and if you don't care about the connections to your destination database,you can programatically kill the process (spid) thats connected to the DB and run a restore.

  Thanks

  Sreejith

• Pankaj Khanna

  Say Hey Kid

  Points: 707

  More actions

  September 12, 2006 at 1:33 am

  #659805

  go to the query analyzer and type sp_who command to see who are the users that are using the database. If the running transactions are not critical (meaning that if you can afford to roll them back), change your SET SINGLE_USER AFTER 60 to SET SINGLE_USER WITH ROLLBACK IMMEDIATE. This option will immediately rollback any tranasaction that is active atthe time of start ofyour command.

   

  All the best

  Pankaj

  Pankaj Khanna
  Database Administrator - SQL Server 2000

  Keep hope to keep you intact...

• mwelcome

  Right there with Babe

  Points: 739

  More actions

  September 14, 2006 at 5:08 pm

  #660544

  I looks like the problem was with trying to run the restore procedure from the Database I was trying to restore.  One I moved it to the master database everything works.

  Thanks.

   

  Michael