November 16, 2012 at 3:12 am
Hi 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
----------------------------------------------------------
--Drop previous reporting database
----------------------------------------------------------
PRINT 'Check if DB exists if so drop'
GO
IF 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 ReportDB
END
GO
----------------------------------------------------------
--Get backup file names
----------------------------------------------------------
PRINT 'Grab backup Files'
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell',1 --turn on
RECONFIGURE
CREATE 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 off
RECONFIGURE
GO
----------------------------------------------------------
--Restore Database
----------------------------------------------------------
--get lastest file name
DECLARE @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 ReportDB
FROM DISK = @lastestBakFile
WITH 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'
GO
ALTER DATABASE ReportDB SET MULTI_USER
GO
DROP TABLE #Files
November 16, 2012 at 5:08 am
--Drop previous reporting database
----------------------------------------------------------
PRINT 'Check if DB exists if so drop'
GO
IF 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 ReportDB
END
GO
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.
-----------------------------------------------------------------------------
संकेत कोकणे
November 16, 2012 at 5:34 am
sanket kokane (11/16/2012)
--Drop previous reporting database
----------------------------------------------------------
PRINT 'Check if DB exists if so drop'
GO
IF 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 ReportDB
END
GO
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.
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
November 16, 2012 at 5:40 am
bugg (11/16/2012)
sanket kokane (11/16/2012)
--Drop previous reporting database
----------------------------------------------------------
PRINT 'Check if DB exists if so drop'
GO
IF 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 ReportDB
END
GO
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.
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
I will Recommend you to go through this topic.
http://www.sqlservercentral.com/Forums/Topic1063979-1237-1.aspx
EDIT : Forgot to put URL
-----------------------------------------------------------------------------
संकेत कोकणे
November 16, 2012 at 5:53 am
I will Recommend you to go through this topic.
http://www.sqlservercentral.com/Forums/Topic1063979-1237-1.aspx
EDIT : Forgot to put URL
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.
--Take DB offline
ALTER DATABASE Test_DB SET OFFLINE WITH ROLLBACK IMMEDIATE
November 16, 2012 at 6:56 am
I'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.
November 16, 2012 at 9:05 am
Cheers I Have used the with restore, and changed from
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
TO
ALTER DATABASE Test_DB SET OFFLINE WITH ROLLBACK IMMEDIATE
Do you think this will suffice?
November 16, 2012 at 11:20 pm
bugg (11/16/2012)
Cheers I Have used the with restore, and changed fromSET SINGLE_USER WITH
ROLLBACK IMMEDIATE
TO
ALTER DATABASE Test_DB SET OFFLINE WITH ROLLBACK IMMEDIATE
Do you think this will suffice?
No ,database will not go offline till there users connected to it.
-----------------------------------------------------------------------------
संकेत कोकणे
November 17, 2012 at 6:30 am
sanket kokane (11/16/2012)
bugg (11/16/2012)
Cheers I Have used the with restore, and changed fromSET SINGLE_USER WITH
ROLLBACK IMMEDIATE
TO
ALTER DATABASE Test_DB SET OFFLINE WITH ROLLBACK IMMEDIATE
Do you think this will suffice?
No ,database will not go offline till there users connected to it.
Okay so back to either using the kill script or set single user then
November 17, 2012 at 10:49 am
bugg (11/17/2012)
sanket kokane (11/16/2012)
bugg (11/16/2012)
Cheers I Have used the with restore, and changed fromSET SINGLE_USER WITH
ROLLBACK IMMEDIATE
TO
ALTER DATABASE Test_DB SET OFFLINE WITH ROLLBACK IMMEDIATE
Do you think this will suffice?
No ,database will not go offline till there users connected to it.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply