Post reply

Programatic Restores

  • April 23, 2002 at 6:12 pm

    #158316

    I want to be able to allow my users to backup their data on my web site, then later select one of their backups to restore, if necessary, all from a web page.

    I'm having difficulty restoring, as I need to clear all users off in order to restore. I have a sp_ to remove all users, but I still get the error. All SQL is done through T-SQL.

    Any ideas for allowing the restore to happen (a restore on top of existing data)?

    Robb

  • April 23, 2002 at 6:31 pm

    #427704

    I guess you doing a Force Restore, right?

    Or is it something else.

    Also, could you please post the code & error

    Thanks,

    NeoNash

    Edited by - NeoNash on 04/23/2002 6:33:14 PM

  • April 23, 2002 at 6:54 pm

    #427705

    I have tried:

    restore database [database] from device

    as well as:

    RESTORE DATABASE [database] FROM DISK = '\\box\d$\MSSQL7\BACKUP\database.BAK' WITH FILE = 1, STATS = 40, RECOVERY

    and either way I'm getting the error:

    Server: Msg 3101, Level 16, State 1, Line 1

    Database in use. The system administrator must have exclusive use of the database to run the restore operation.

    This error comes after I run the sp_killusers script (from this site, thanks). I do an sp_who and I still see sa users, but I appear to be the only one active (the other sa users are listed as background, etc.).

    Thanks for any help.

  • April 23, 2002 at 7:40 pm

    #427707

    No body can be active in it icluding the person issuing the command. When you get this error testing immediately check from QA sp_who or sp_who2 to see what connections are active.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • April 24, 2002 at 10:46 am

    #427756

    Thanks. Your info helped me figure out that I can have no connections in that database. So in Query Analyzer, to test, I have to connect to a different DB (e.g. master) and then do all the backup/restore operations on my DB. For anybody interested:

    -- these are the necessary commands to create a backup and restore it. It is important

    -- that you NOT be connected to the database that is being backed up and restored

    exec sp_dropdevice 'backup_test'

    EXEC sp_addumpdevice 'disk', 'backup_test','\\file\d$\sqlbak\dev\backup_test_database.BAK'

    BACKUP DATABASE backup_test_database TO backup_test

    exec usp_Killusers 'backup_test_database'

    RESTORE DATABASE [backup_test_database] FROM DISK = '\\file\sqlbak\dev\backup_test_database.BAK' WITH FILE = 1, RECOVERY

Viewing 5 posts - 1 through 4 (of 4 total)

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