November 25, 2007 at 2:24 pm
OS: windows 2003 Svr std.
SQL: SQL server 2000 std.
hello all,
i've been tasked to auto script a backup from a live database and restore it to a test SQL server. (that was the easy bit, done that)
also, the database needs to be renamed with the date tacked on the end.. e.g. (northwind > northwind23112007) also the log and data files in the same manner.
(northwind.mdf > northwind23112007.mdf) etc....
i'm not sure if its possible to pass in a variable for the date and tack it onto the end of the DB name, log and data Files.
e.g.
@useDate = Replace(date(),"/","")
RESTORE DATABASE northwind
FROM DISK 'f:\mydataorthwind.bak'
WITH MOVE 'northwind_Data' TO 'C:\MySQLServerorthwind.mdf',
MOVE northwind_Log' TO 'C:\MySQLServerorthwind.ldf';
GO
any help with this would be most appreciated!
cheers
Dave
Software Dev, SQL DBA (almost 😉 ) and Network Admin.
November 25, 2007 at 3:59 pm
Here is a script for backing up a db while attaching the date/time of the backup. This can be a sql agent job step.
Declare
@vDBNameVarchar(255),
@vFileNameVarchar(100),
@vNewPathVarchar(1000),
@vBackupStringVarchar(1000)
Set @vDBName='YOURDB'
Set @vNewPath='D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\' --FILL IN YOUR BACKUP PATH
--Generate backup file name from getdate() function converted to string type varchar()
Set @vFileName = 'YOURDB_db_'+
LEFT(CONVERT(VARCHAR(19), getdate(), 120),10)+'-'
+SUBSTRING(CONVERT(VARCHAR(19), getdate(), 120),12,2)+'-'
+SUBSTRING(CONVERT(VARCHAR(19), getdate(), 120),15,2)+'_Full'+ '.bak'
--Generate backup command name from getdate() function converted to string type varchar()
Set @vBackupString = 'BACKUP DATABASE YOURDB TO DISK = ''' + @vNewPath + @vFileName
+ ''' WITH INIT , NOUNLOAD , NAME = N''' +
@vDBName + ''', NOSKIP , STATS = 50, NOFORMAT'
Exec (@vBackupString)
GO
You can pass a parameter to your restore script only if you would save it as a sp. Then, if would need to be scheduled then exec your_sp as a sql agent job step.
November 26, 2007 at 4:23 am
Just an FYI. If you want to append anything, dates, times, characters, to a name (table/column/backup file) in a SQL Script, generally you have to build it in a dynamic SQL String. That's what michaela's example is demonstrating.
I've tried to do it the other way before, just appending the datetime to the end of the name, but it doesn't work unless you assign the whole thing to a variable and execute the variable (which is dynamic SQL).
November 26, 2007 at 5:35 am
many thanks both, I'm just going through and re-building the string.
i'll let you know and post a finished sample when its done
many thanks for your help.
dave
November 26, 2007 at 6:20 am
here is the final code i went with, its works perfectly! many thanks for your help..
declare
@UseDate as varchar(50),
@dbname as varchar(50),
@mycommand as varchar(2000),
@fromdisk as varchar(500),
@frommoves1 as varchar(500),
@frommoves2 as varchar(500),
@frommoves3 as varchar(500)
set @usedate = replace(LEFT(CONVERT(VARCHAR(19), getdate(), 120),10),'-','')
set @dbname = 'NAVTEMP'+@usedate
set @fromdisk = '''V:\tempstore\liveBackup.bak'''
set @frommoves1 = 'MOVE ' + '''Navision370Test_Data''' + ' TO ' + '''V:\Volatile Data\SQL Data\' + @usedate + 'NAV.mdf'', '
set @frommoves2 = 'MOVE ' + '''Navision370Test_1_Data''' + ' TO '+ '''V:\Volatile Data\SQL Data\'+ @usedate + 'NAV.ndf'', '
set @frommoves3 = 'MOVE ' + '''Navision370Test_Log''' + ' TO '+ '''V:\Volatile Data\SQL Log\' + @usedate + 'NAV.ldf'' '
set @mycommand ='RESTORE DATABASE '+ @dbname +
' FROM DISK = '+@fromdisk +
' WITH norecovery,'+ @frommoves1 + @frommoves2 + @frommoves3
--print @mycommand
Exec(@mycommand)
go
regards
Dave
November 26, 2007 at 6:24 am
Glad we could help. @=)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply