Automated Database Restore script for Reporting DB

Rate Topic

Display Mode

Topic Options

Author

Message

bugg

Posted Friday, November 16, 2012 3:12 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 123, Visits: 409

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

sanket kokane

Posted Friday, November 16, 2012 5:08 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 11:12 PM
Points: 323, Visits: 973

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

bugg

Posted Friday, November 16, 2012 5:34 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 123, Visits: 409

sanket kokane (11/16/2012)

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

sanket kokane

Posted Friday, November 16, 2012 5:40 AM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 11:12 PM
Points: 323, Visits: 973

bugg (11/16/2012)

sanket kokane (11/16/2012)

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

bugg

Posted Friday, November 16, 2012 5:53 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 123, Visits: 409

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

Gazareth

Posted Friday, November 16, 2012 6:56 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 4:11 AM
Points: 1,474, Visits: 2,344

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

bugg

Posted Friday, November 16, 2012 9:05 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 123, Visits: 409

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

sanket kokane

Posted Friday, November 16, 2012 11:20 PM

Old Hand

Group: General Forum Members
Last Login: Yesterday @ 11:12 PM
Points: 323, Visits: 973

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

bugg

Posted Saturday, November 17, 2012 6:30 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 123, Visits: 409

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

bugg

Posted Saturday, November 17, 2012 10:49 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 123, Visits: 409

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 »