SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automated Database Restore script for Reporting DB


Automated Database Restore script for Reporting DB

Author
Message
bugg
bugg
SSChasing Mays
SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

Group: General Forum Members
Points: 642 Visits: 983
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

sanket kokane
sanket kokane
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 1022

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

-----------------------------------------------------------------------------
संकेत कोकणे
bugg
bugg
SSChasing Mays
SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

Group: General Forum Members
Points: 642 Visits: 983
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
sanket kokane
sanket kokane
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 1022
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

-----------------------------------------------------------------------------
संकेत कोकणे
bugg
bugg
SSChasing Mays
SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

Group: General Forum Members
Points: 642 Visits: 983

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

Gazareth
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4002 Visits: 5820
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.
bugg
bugg
SSChasing Mays
SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

Group: General Forum Members
Points: 642 Visits: 983
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?
sanket kokane
sanket kokane
SSC-Addicted
SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)SSC-Addicted (475 reputation)

Group: General Forum Members
Points: 475 Visits: 1022
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.

-----------------------------------------------------------------------------
संकेत कोकणे
bugg
bugg
SSChasing Mays
SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

Group: General Forum Members
Points: 642 Visits: 983
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
bugg
bugg
SSChasing Mays
SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)SSChasing Mays (642 reputation)

Group: General Forum Members
Points: 642 Visits: 983
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.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search