What does this give you?
RESTORE FILELISTONLY FROM DISK ='D:\backupdb\bis_backup_201711100007.bak’
you should get something like this
LogicalName PhysicalName Type FileGroupName Size MaxSize
------------------ -------------------------------- ---- ------------- ---- -------
MyOldDatabase_data D:\MSSQL\DATA\MyDatabaseName.mdf D PRIMARY 1234 567890
MyOldDatabase_log D:\MSSQL\DATA\MyDatabaseName.ldf L NULL 1234 567890
Based on YOUR results globally change these values (in all the scripts below) :
-- Location of BACKUP FILES which you want to restore
-- x:\Mypath\ Path to your backup files
-- x:\Mypath\MyFullBackupfile.BAK Path/Filename of the FULL BACKUP file
-- x:\Mypath\MyDifferentialBackupfile.BAK Path/Filename of the DIFFERENTIAL BACKUP file (optional)
-- TARGET Location for the Database (Data and Log - which may be separate drives etc)
-- x:\MySQLDataPath\MyNewDatabaseName.mdf
-- x:\MySQLLogPath\MyNewDatabaseName.ldf
--
-- ORIGINAL logical names
-- MyOldDatabase_data
-- MyOldDatabase_log
-- also change these to what would be SENSIBLE names for the NEW logical objects:
-- MyNewDatabase_data
-- MyNewDatabase_log
--
-- MyNewDatabaseName
-- Restore script
USE master -- Must not be "sitting" in the database being restored!
GO
-- If database is being overwritten force all users off:
ALTER DATABASE MyNewDatabaseName SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE MyNewDatabaseName
FROM DISK = 'x:\Mypath\MyFullBackupfile.BAK'
WITH
REPLACE, -- Overwrite DB - if one exists
NORECOVERY,
STATS = 10, -- Show progress (every 10%)
MOVE 'MyOldDatabase_data' TO 'x:\MySQLDataPath\MyNewDatabaseName.mdf',
MOVE 'MyOldDatabase_log' TO 'x:\MySQLLogPath\MyNewDatabaseName.ldf'
GO
-- If you have a Differential backup to restore do this:
RESTORE DATABASE MyNewDatabaseName
FROM DISK = 'x:\Mypath\MyDifferentialBackupfile.BAK'
WITH
NORECOVERY
-- If you have any Transaction Backup files to restore repeat this for each one in turn:
-- Copy & Paste this multiple times if you have many TLog files to restore
RESTORE LOG MyNewDatabaseName FROM DISK = 'x:\Mypath\MyTransactionBackupfile0001.BAK' WITH NORECOVERY, STATS = 10
RESTORE LOG MyNewDatabaseName FROM DISK = 'x:\Mypath\MyTransactionBackupfile0002.BAK' WITH NORECOVERY, STATS = 10
...
-- Set the database ready for use (after all backups have been restored)
RESTORE DATABASE MyNewDatabaseName WITH RECOVERY
GO
-- Rename logical names:
ALTER DATABASE MyNewDatabaseName MODIFY FILE (NAME = 'MyOldDatabase_data', NEWNAME = 'MyNewDatabase_data')
GO
ALTER DATABASE MyNewDatabaseName MODIFY FILE (NAME = 'MyOldDatabase_log', NEWNAME = 'MyNewDatabase_log')
GO