Restoring transactional backups on a different server with a different database name
-
Hello,
I would like to be able to restore a database that has complete backups every day and transaction log backup every 15 mn on a different server, potentially with a different name.
Assuming I copy all files concerned onto the new server, could you let me know how I should write my T-SQL restore script?
Thank you
-
Here is how I do it for one of our databases:
RESTORE DATABASE [H88TRN] -- Restore of full backup
FROM DISK = N'F:\sql_backups\H88PRD\H88PRD_20060804.bak'
WITH FILE = 1,
MOVE N'H88PRD_data' TO N'F:\sql_DBs\User_DBs\H88TRN\H88TRN_Data.mdf',
MOVE N'H88PRD_log' TO N'F:\sql_DBs\User_DBs\H88TRN\H88TRN_Log.ldf',
NORECOVERY,
NOUNLOAD,
REPLACE,
STATS = 10
GO
RESTORE DATABASE [H88TRN] -- Restore of differential backup
FROM DISK = N'F:\sql_backups\H88PRD\H88PRD_20060808.bak'
WITH FILE = 1,
MOVE N'H88PRD_data' TO N'F:\sql_DBs\User_DBs\H88TRN\H88TRN_Data.mdf',
MOVE N'H88PRD_log' TO N'F:\sql_DBs\User_DBs\H88TRN\H88TRN_Log.ldf',
NORECOVERY,
NOUNLOAD,
REPLACE,
STATS = 10
GO
RESTORE LOG [H88TRN] -- Restore of transaction log
FROM DISK = N'F:\sql_backups\H88PRD\H88PRD_20060808.trn'
WITH FILE = 1,
NORECOVERY,
NOUNLOAD,
STATS = 10
GO
RESTORE LOG [H88TRN] -- Restore of transaction log
FROM DISK = N'F:\sql_backups\H88PRD\H88PRD_20060808.trn'
WITH FILE = 2,
NORECOVERY,
NOUNLOAD,
STATS = 10
GO
RESTORE LOG [H88TRN] -- Restore of transaction log
FROM DISK = N'F:\sql_backups\H88PRD\H88PRD_20060808.trn'
WITH FILE = 3,
NORECOVERY,
NOUNLOAD,
STATS = 10
GO
RESTORE LOG [H88TRN] -- Restore of transaction log
FROM DISK = N'F:\sql_backups\H88PRD\H88PRD_20060808.trn'
WITH FILE = 4,
NORECOVERY,
NOUNLOAD,
STATS = 10
GO
RESTORE LOG [H88TRN] -- Restore of transaction log
FROM DISK = N'F:\sql_backups\H88PRD\H88PRD_20060808.trn'
WITH FILE = 5,
NORECOVERY,
NOUNLOAD,
STATS = 10
GO
RESTORE LOG [H88TRN] -- Restore of transaction log
FROM DISK = N'F:\sql_backups\H88PRD\H88PRD_20060808.trn'
WITH FILE = 6,
NORECOVERY,
NOUNLOAD,
STATS = 10
GO
RESTORE LOG [H88TRN] -- Restore of transaction log
FROM DISK = N'F:\sql_backups\H88PRD\H88PRD_20060808.trn'
WITH FILE = 7,
NOUNLOAD,
STATS = 10
GO
You will have to adjust it for your specific environment.
hth,
Lynn
-
Thank you. I will test it on my development box and let you know if this works for me
-
It worked. Thank you. I had to change the file Id to 1 for the second file bizarrely but it worked.
-
That is because we append our transaction log backups to the same file each day. It makes sense that if each of your transaction log backups are written to seperate files you would almost always use file = 1.
Glad it helped!
Lynn
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply