Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Automated Database Restore script for Reporting DB Expand / Collapse
Author
Message
Posted Friday, November 16, 2012 3:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 6:15 AM
Points: 184, Visits: 654
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
Post #1385574
Posted Friday, November 16, 2012 5:08 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 1, 2013 10:17 AM
Points: 323, Visits: 984

--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.


-----------------------------------------------------------------------------
संकेत कोकणे
Post #1385615
Posted Friday, November 16, 2012 5:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 6:15 AM
Points: 184, Visits: 654
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



Post #1385624
Posted Friday, November 16, 2012 5:40 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 1, 2013 10:17 AM
Points: 323, Visits: 984
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


-----------------------------------------------------------------------------
संकेत कोकणे
Post #1385629
Posted Friday, November 16, 2012 5:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 6:15 AM
Points: 184, Visits: 654

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
Post #1385635
Posted Friday, November 16, 2012 6:56 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:46 AM
Points: 1,976, Visits: 3,280
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.
Post #1385676
Posted Friday, November 16, 2012 9:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 6:15 AM
Points: 184, Visits: 654
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?
Post #1385736
Posted Friday, November 16, 2012 11:20 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 1, 2013 10:17 AM
Points: 323, Visits: 984
bugg (11/16/2012)
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?



No ,database will not go offline till there users connected to it.


-----------------------------------------------------------------------------
संकेत कोकणे
Post #1385904
Posted Saturday, November 17, 2012 6:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 6:15 AM
Points: 184, Visits: 654
sanket kokane (11/16/2012)
bugg (11/16/2012)
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?



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
Post #1385936
Posted Saturday, November 17, 2012 10:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 6:15 AM
Points: 184, Visits: 654
bugg (11/17/2012)
sanket kokane (11/16/2012)
[quote]bugg (11/16/2012)
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?



No ,database will not go offline till there users connected to it.


Post #1385955
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse