InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 23:38:13 GMT20http://www.sqlservercentral.com/Forums/Topic1385574-1292-1.aspx[quote][b]bugg (11/17/2012)[/b][hr][quote][b]sanket kokane (11/16/2012)[/b][hr][quote][b]bugg (11/16/2012)[/b][hr]Cheers I Have used the with restore, and changed fromSET SINGLE_USER WITHROLLBACK IMMEDIATETOALTER DATABASE Test_DB SET OFFLINE WITH ROLLBACK IMMEDIATEDo you think this will suffice?[/quote]No ,database will not go offline till there users connected to it.[/quote]Sat, 17 Nov 2012 10:49:49 GMTbugghttp://www.sqlservercentral.com/Forums/Topic1385574-1292-1.aspx[quote][b]sanket kokane (11/16/2012)[/b][hr][quote][b]bugg (11/16/2012)[/b][hr]Cheers I Have used the with restore, and changed fromSET SINGLE_USER WITHROLLBACK IMMEDIATETOALTER DATABASE Test_DB SET OFFLINE WITH ROLLBACK IMMEDIATEDo you think this will suffice?[/quote]No ,database will not go offline till there users connected to it.[/quote]Okay so back to either using the kill script or set single user thenSat, 17 Nov 2012 06:30:44 GMTbugghttp://www.sqlservercentral.com/Forums/Topic1385574-1292-1.aspx[quote][b]bugg (11/16/2012)[/b][hr]Cheers I Have used the with restore, and changed fromSET SINGLE_USER WITHROLLBACK IMMEDIATETOALTER DATABASE Test_DB SET OFFLINE WITH ROLLBACK IMMEDIATEDo you think this will suffice?[/quote]No ,database will not go offline till there users connected to it.Fri, 16 Nov 2012 23:20:07 GMTsanket kokanehttp://www.sqlservercentral.com/Forums/Topic1385574-1292-1.aspxCheers I Have used the with restore, and changed fromSET SINGLE_USER WITHROLLBACK IMMEDIATETOALTER DATABASE Test_DB SET OFFLINE WITH ROLLBACK IMMEDIATEDo you think this will suffice?Fri, 16 Nov 2012 09:05:14 GMTbugghttp://www.sqlservercentral.com/Forums/Topic1385574-1292-1.aspxI've never had any problem in a script when using SINGLE_USER, followed by another command against that database.You could move the script around so the drop and restore are done straight after the set single user command to lessen the time that anything else could connect.Even with the kill method you're still susceptible to something else reconnecting to the database.Although I'd still recommend using WITH REPLACE on the restore rather than dropping the database first then restoring.Fri, 16 Nov 2012 06:56:21 GMTGazarethhttp://www.sqlservercentral.com/Forums/Topic1385574-1292-1.aspx[quote]I will Recommend you to go through this topic.[url]http://www.sqlservercentral.com/Forums/Topic1063979-1237-1.aspx[/url]EDIT : Forgot to put URL[/quote]Thanks , I've had a read through and may use the kill script, I changed my code to read the below but I might run the kill script as mentioned in those posts alter DB may not be available.[quote]--Take DB offlineALTER DATABASE Test_DB SET OFFLINE WITH ROLLBACK IMMEDIATE[/quote]Fri, 16 Nov 2012 05:53:20 GMTbugghttp://www.sqlservercentral.com/Forums/Topic1385574-1292-1.aspx[quote][b]bugg (11/16/2012)[/b][hr][quote][b]sanket kokane (11/16/2012)[/b][hr][quote]--Drop previous reporting database----------------------------------------------------------PRINT 'Check if DB exists if so drop'GOIF EXISTS(SELECT * FROM SYS.DATABASES WHERE NAME='ReportDB')BEGIN --Remove any connections ALTER DATABASE ReportDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE --Drop DB DROP DATABASE ReportDBENDGO[/quote]setting db in single user mode can kill you .there are chances,that after putting db in single user mode,one of your application session can act as single user and you will not get access to database.Also why you are dropping database , use REPLACE option instead.[/quote]Regarding single user mode, is there a better alternative way to kill the connections to the database?I will change that to replace rather then drop good point!Thanks[/quote]I will Recommend you to go through this topic.[url]http://www.sqlservercentral.com/Forums/Topic1063979-1237-1.aspx[/url]EDIT : Forgot to put URLFri, 16 Nov 2012 05:40:30 GMTsanket kokanehttp://www.sqlservercentral.com/Forums/Topic1385574-1292-1.aspx[quote][b]sanket kokane (11/16/2012)[/b][hr][quote]--Drop previous reporting database----------------------------------------------------------PRINT 'Check if DB exists if so drop'GOIF EXISTS(SELECT * FROM SYS.DATABASES WHERE NAME='ReportDB')BEGIN --Remove any connections ALTER DATABASE ReportDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE --Drop DB DROP DATABASE ReportDBENDGO[/quote]setting db in single user mode can kill you .there are chances,that after putting db in single user mode,one of your application session can act as single user and you will not get access to database.Also why you are dropping database , use REPLACE option instead.[/quote]Regarding single user mode, is there a better alternative way to kill the connections to the database?I will change that to replace rather then drop good point!ThanksFri, 16 Nov 2012 05:34:14 GMTbugghttp://www.sqlservercentral.com/Forums/Topic1385574-1292-1.aspx[quote]--Drop previous reporting database----------------------------------------------------------PRINT 'Check if DB exists if so drop'GOIF EXISTS(SELECT * FROM SYS.DATABASES WHERE NAME='ReportDB')BEGIN --Remove any connections ALTER DATABASE ReportDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE --Drop DB DROP DATABASE ReportDBENDGO[/quote]setting db in single user mode can kill you .there are chances,that after putting db in single user mode,one of your application session can act as single user and you will not get access to database.Also why you are dropping database , use REPLACE option instead.Fri, 16 Nov 2012 05:08:36 GMTsanket kokanehttp://www.sqlservercentral.com/Forums/Topic1385574-1292-1.aspxHi All,I have created the following script which i plan to put in a SQL job with notifications etc . The script drops the existing DB then grabs the Lastest bak from a folder which it uses to do a full restore of the DB. I plan on adding some clean up scripts afterwards.As I'm pretty new to this I was wondering if some of the gurus on here could give it a once over? Its this a sound script for doing this job? Any comments and recommendations would be great.Many thanks[quote]------------------------------------------------------------Drop previous reporting database----------------------------------------------------------PRINT 'Check if DB exists if so drop'GOIF EXISTS(SELECT * FROM SYS.DATABASES WHERE NAME='ReportDB')BEGIN --Remove any connections ALTER DATABASE ReportDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE --Drop DB DROP DATABASE ReportDBENDGO------------------------------------------------------------Get backup file names----------------------------------------------------------PRINT 'Grab backup Files'EXEC sp_configure 'show advanced options', 1RECONFIGUREEXEC sp_configure 'xp_cmdshell',1 --turn onRECONFIGURECREATE TABLE #Files( Name nvarchar(500))INSERT INTO #Files(Name)EXECUTE master.dbo.xp_cmdshell 'DIR "E:\Backups\" /A-D /B'EXEC sp_configure 'xp_cmdshell',0 --turn offRECONFIGUREGO------------------------------------------------------------Restore Database------------------------------------------------------------get lastest file nameDECLARE @lastestBakFile nvarchar(500)SET @lastestBakFile = 'E:\Backups\' + (select TOP 1 Name from #Files where Name like 'Test_backup%.bak' order by name desc)PRINT 'Restoring DB'RESTORE DATABASE ReportDBFROM DISK = @lastestBakFileWITH MOVE 'Test_Data' TO 'D:\ReportingDB\DATA\ReportDB.mdf',MOVE 'Test_Log' TO 'D:\ReportingDB\LOG\ReportDB.ldf',MOVE 'ftrow_search' TO 'D:\ReportingDB\LOG\ftrow_search.ldf'GOALTER DATABASE ReportDB SET MULTI_USERGODROP TABLE #Files[/quote]Fri, 16 Nov 2012 03:12:09 GMTbugg