Re: force process termination on restore

From: Hari Prasad (hari_prasad_k_at_hotmail.com)
Date: 08/16/04 

Date: Mon, 16 Aug 2004 20:09:02 +0530

Hi,

There are 2 approaches...

1. If you are restoring in non production then you could disconnect all the
users using below command

    ALTER database <dbname> set single_user with rollback immediate
    go
    Restore database
    go
    ALTER database <dbname> set multi_user

2. Write a loop sort to check the sysprocesses table for this particular
database for any running process . If there
     is any running processes
    start1:
     if (select status from master..sysprocesses where status='runnable' and
db_name(dbid)='pubs')>1
     waitfor delay(........) -- set 1 minutedelay
     goto start1
     else
     kill all the users connected
     restore the database

I recommend the first method for development and test server databases.

Thanks
Hari
MCDBA

"Jason" <Jason@discussions.microsoft.com> wrote in message
news:03CD2B22-81B6-4EB1-B782-A86E2C6EF9A1@microsoft.com...
> Sorry if this is a dumb question, but we have several production databases
> that get automatically backed up, Rar'd, FTP'd internally and then
restored.
> Problem is, if someone has a process open against the restored database
(IE:
> a report is being run or something) the restore fails.
> There must be a way of making the restore wait until a process is finsihed
> or to force a termination of any process (altho probably not a good
practice)
> that is running.
> Basically, I'm just looking for a better backup/restore process. Any
ideas?

Relevant Pages

  • Re: Backend Exchange migrate to New Hardware (Server)
    ... Exchange mailbox stores and Exchange public folder stores. ... When you use Backup to restore Exchange databases, ... Storage Engine to restore Exchange database files and their ...
    (microsoft.public.exchange.setup)
  • Backup and Restore Files
    ... What is the best way to backup and restore a runtime database file? ... pszDisplayName As String ...
    (microsoft.public.access.gettingstarted)
  • RE: Moving 5 databases to new server
    ... I am placing the production database in single user mode to ensure there are no other users. ... If you do a fullbackup and restore without further ... > the existing production server to a new one. ...
    (microsoft.public.sqlserver.server)
  • RE: Restore public folders and companyweb
    ... > where the backup of the database. ... Before you can start to restore the SharePoint Services databases, ... > Microsoft CSS Online Newsgroup Support ... > This newsgroup only focuses on SBS technical issues. ...
    (microsoft.public.windows.server.sbs)
  • Re: Transaction Log ??
    ... we used to simplify our database restore process: ... every database got their respective backup folder. ... -- get logFileName, FirstLSN, LastLSN list ...
    (microsoft.public.sqlserver.security)