Restore sql doesnt fire

Question

Hi

Im trying restore database from backup dynamically with application code

simple sql command for restore

con.execute("RESTORE FILELISTONLY FROM DISK='c:\old.bak' " & vbcrlf &_
        "RESTORE DATABASE newdb " & vbcrlf &_
        "FROM DISK='c:\old.bak' " & vbcrlf &_
        "WITH MOVE 'newdb' TO 'c:\newdb.mdf', " & vbcrlf &_
        "MOVE 'newdb_log' TO 'c:\newdb_log.ldf'")

but it doesn't fire, i mean no any errors tried check it with sql profiler and look correctly

RESTORE FILELISTONLY FROM DISK='c:\old.bak' 
RESTORE DATABASE newdb 
FROM DISK='c:\old.bak' 
WITH MOVE 'newdb' TO 'c:\newdb.mdf', 
MOVE 'newdb_log' TO 'c:\newdb_log.ldf'

if i run sql from sql profiler its works

how can u explain this issue?

Answer 1

To perform a restore you are blocked if the database is in use with other sessions. Make sure you wait for a full 60 seconds and see if there are any timeout errors.

Answer 2

I guess the connection you are using as a lock on the database so it can't restore. When you run it directly, you haven't.

What about this?

con.execute("USE master" & vbcrlf &_
        "RESTORE FILELISTONLY FROM DISK='c:\old.bak' " & vbcrlf &_
        "RESTORE DATABASE newdb " & vbcrlf &_
        "FROM DISK='c:\old.bak' " & vbcrlf &_
        "WITH MOVE 'newdb' TO 'c:\newdb.mdf', " & vbcrlf &_
        "MOVE 'newdb_log' TO 'c:\newdb_log.ldf'")

Or changing the database in the connection string?

Answer 3

Try killing all the users in the database your restoring to before running the restore, a restore will fail if there are any active connections to the db. There is example code of how to do this all over.

Also make sure your connection string used when you call the restore from your application doesn't connect to the db your restoring to, but connects to something like master or msdb.