March 31, 2015 at 7:51 am
All,
I am copy my databases from server A to server B by backup and restore.
since I want the process to be automated, I created a job on server A . to copy the bakup files from the nightly job of Server A using Xcopy form a batch file. the process is working and the files gets copied to the server B.
now I can simply write the restore script and run that as a job on server B.
the issue is .
the database files the of the naming convention databasename_2015_03_30_(some randon numbers,5192996).bak
to automate the process i want one of the two options.
- when xcopy copies the file to destination it will change the nae fo the file to simply databasename.bak
- or the restore script shoudl be able to pick the latest backup file in the folder base don the date/time.
Any help in appreciated.
Regards
March 31, 2015 at 7:57 am
You can either pull in a directory listing for the folder that contains the backup and use that to determine the most recent, or you can query msdb on Server A to find out the most recent backup for a particular database.
John
March 31, 2015 at 8:11 am
So I've used the following powershell script to copy the latest backup file from one folder to another:-
$H = (dir \\<SOURCE FILEPATH> *bak | sort -prop LastWriteTime | select -last 1).name
$H = "\\<SOURCE FILEPATH>\" + $H
copy-item $H <DESTINATION FILEPATH>
Does this help?
March 31, 2015 at 8:16 am
Thanks DBA from the Cold.
I am new to Power shell but can give this a try. But copying the latest file is still being done by my Xcopy command, the problem is , than I have to go and manually change the restore script for the database restore.
Is there a way we can change the destination file name to simply datbase.bak. this was i don't have to make changes to destination file and process can be automated.
March 31, 2015 at 8:19 am
Have a look in the detailed description section here:-
https://technet.microsoft.com/en-us/library/hh849793.aspx
Copy-item allows you to rename an item by using the destination parameter
April 9, 2015 at 7:59 am
Another approach:
Share a folder between the machines for the backup, use a static backup file name that can be reused (with INIT) and optionally use a copy only backup if desired to not affect your normal backup chains. Schedule the backup on Prod before scheduling the restore on Test:
-- Prod DB Backup
BACKUP DATABASE [DBNameIE]
TO DISK = '\\TestSQL01\LogShipped_Backups\DBNameIE_full_CopyOnly.BAK'
WITH STATS = 25, INIT, COPY_ONLY,
DESCRIPTION = 'Copy_Only Backup for Automated Restore to TestSQL01.'
GO
--Test DB Restore
USE master
RESTORE DATABASE [DBNameIETST]
FROM DISK = '\\TestSQL01\LogShipped_Backups\DBNameIE_full_CopyOnly.BAK'
WITH STATS, REPLACE
--,MOVE 'DBNameIE_Data' TO 'E:\Program Files\Microsoft SQL Server\MSSQL10.TestSQL01\MSSQL\Data\DBNameIETST.mdf',
--MOVE 'DBNameIE_Log' TO 'F:\Program Files\Microsoft SQL Server\MSSQL10.TestSQL01\MSSQL\Data\DBNameIETST_log.ldf'
GO
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply