During one of my current tasks, which was to run the same test on multiple copies of the same database, I needed to setup a flow where I could
- deploy the database from VS2010 or setup database w/ vsdbcmd
- run the test (import 5.5 mio facts + 250K dimension elements into a dw model)
- rename the database including the physical files
- goto step 1 w/out overwriting the databases already created
Since both a deploy from VS2010 and a setup run through vsdbcmd will produce the same name on the physical files over and over again, I needed to alter these names. Here’s how the script looks:
DECLARE @Database NVARCHAR(255) = '<name of database>'
DECLARE @BackupDevice NVARCHAR(255) = N'<full path to backupmedia>'
DECLARE @CurrentDataFileName NVARCHAR(255) =
N'<name of logical database file>'
DECLARE @TargetDataFileFullPath NVARCHAR(255) =
N'<operating system file name for new database file>'
DECLARE @CurrentLogFileName NVARCHAR(255) =
N'<name of logical log file>'
DECLARE @TargetLogFileFullPath NVARCHAR(255) =
N'<operating system file name for new log file>'
DECLARE @SQL VARCHAR(MAX) = N'
RESTORE DATABASE ' + @Database + N'
FROM DISK = N''' + @BackupDevice + '''
WITH FILE = 1,
MOVE N''' + @CurrentDataFileName + '''
TO N''' + @TargetDataFileFullPath + ''',
MOVE N''' + @CurrentLogFileName + '''
TO N''' + @TargetLogFileFullPath + ''', STATS = 10