August 21, 2008 at 10:57 am
I'd like all my scripts to "automatically" backup and then restore in the even of an error.
How would I save the currently selectd DB abd then use it after the backup?
i.e.
USE [TEST1]
GO
...do something
USE [MASTER]
BACKUP DATABASE [TEST4]
TO DISK = 'TEST1_8_21_08.bak'
GO
USE [TEST1]
BACKUP LOG TEST1 WITH TRUNCATE_ONLY
DBCC SHRINKFILE(Test1_Log, 2)
GO
... do a bunch of other things until error
IF @@ERROR<>0 OR @@TRANCOUNT=0
BEGIN
USE [MASTER]
RESTORE DATABASE [TEST1]
FROM DISK = 'TEST1_8_21_08.bak'
WITH REPLACE
END
I want TEST1 to be variable, if possible so this script can be run on different DBs without modifying the many places I have it in the code.
Understand?
August 21, 2008 at 11:25 am
You could set it up as dynamic SQL. Have the database name be a variable, concatenate it into the commands, then execute them as strings.
declare @DBName nvarchar(126), @Cmd nvarchar(1000)
select @DBName = 'Test1'
select @Cmd = '
USE [MASTER]
RESTORE DATABASE [' + @DBName + ']
FROM DISK = ''' + @DBName + '_8_21_08.bak''
WITH REPLACE'
exec (@Cmd)
Will that accomplish what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 21, 2008 at 12:34 pm
I forgot about using dynamic SQL.
Thanks...
August 21, 2008 at 12:37 pm
Is there a way to get the currently selected DB name?
August 21, 2008 at 12:57 pm
oh, select db_name()...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply