Restore database using t-sq
-
hi i have developed some t-sql to backup/restore any database. while restoring a database, if any session or connection be active on it i can not restore database and recieve exception. How can i restore such databases? How can i kill all sessions from database to solve my problem?
-
hi i have developed some t-sql to backup/restore any database. while restoring a database, if any session or connection be active on it i can not restore database and recieve exception. How can i restore such databases? How can i kill all sessions from database to solve my problem?
WDI wrote:
How can i kill all sessions from database to solve my problem?
That would not be nice for your users. Imagine, you are working along and wham database session is killed and you lose work.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
-
WDI wrote:
How can i kill all sessions from database to solve my problem?
That would not be nice for your users. Imagine, you are working along and wham database session is killed and you lose work.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
I second that, those type of tasks are usually scheduled for when people are not working.
-
hi i have developed some t-sql to backup/restore any database. while restoring a database, if any session or connection be active on it i can not restore database and recieve exception. How can i restore such databases? How can i kill all sessions from database to solve my problem?
The only way I can think to end all the sessions would be by stopping the SQL service and then restarting it, your users might open their session again by the time you have started restoring though in which case it will still fail. You could just run the command "NET STOP MSSQLSERVER" and then "NET START MSSQLSERVER" within your application. There probably is a better way though but like the others said, it's probably not a good idea.
There are 10 types of people in the world, those who understand binary and those who dont.
-
hi i have developed some t-sql to backup/restore any database. while restoring a database, if any session or connection be active on it i can not restore database and recieve exception. How can i restore such databases? How can i kill all sessions from database to solve my problem?
You can put the database into a mode where only a single connection is allowed at a time by using
ALTER DATABASE _db_ SET SINGLE_USER. Use theWITH ROLLBACKoption to tell SQL Server when to rollback/abort any other existing connections. The connection you runALTER DATABASEfrom remains connected. You may also be able to restore the database if you take itOFFLINE.Stability. What an interesting concept. -- Chris Maunder