Search code examples
sql-servert-sqlrestore

Can you rename a table to the new database when restoring from a .bak file?

So I've almost wrote all of the SQL code I have to restore from a backup file from sql 2008 to 2016. Problem is, one of the tables called Students has the same table structure in the old db and the new db (I'm restoring to a new database too), but in the old db, Students is name Stdnents. Is there ANY way I can restore correctly?

RESTORE FILELISTONLY FROM DISK='f:\newBDbackup.bak'

declare @DefaultData nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @DefaultData output

declare @DefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @DefaultLog output

declare @DefaultBackup nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @DefaultBackup output

declare @MasterData nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg0', @MasterData output
select @MasterData=substring(@MasterData, 3, 255)
select @MasterData=substring(@MasterData, 1, len(@MasterData) - charindex('\', reverse(@MasterData)))

declare @MasterLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg2', @MasterLog output
select @MasterLog=substring(@MasterLog, 3, 255)
select @MasterLog=substring(@MasterLog, 1, len(@MasterLog) - charindex('\', reverse(@MasterLog)))

select 
    isnull(@DefaultData, @MasterData) DefaultData, 
    isnull(@DefaultLog, @MasterLog) DefaultLog,
    isnull(@DefaultBackup, @MasterLog) DefaultBackup

declare @NewDefaultData nvarchar(512)
select isnull(@NewDefaultData, CONVERT(nvarchar(512), @DefaultData))
declare @NewDefaultLog nvarchar(512)
select isnull(@NewDefaultLog, CONVERT(nvarchar(512), @DefaultLog)) 

RESTORE DATABASE NewSqlDatabase FROM DISK='f:\newBDbackup.bak'
WITH
    MOVE 'newDatabase' TO @NewDefaultData,
    MOVE 'newDatabase_log' TO @NewDefaultLog
Solution

  • Not sure I understand the issue but can you not simply rename the table "Stdnents" to "Students" immediately after the restore (sp_rename)