March 19, 2010 at 8:13 am
Hi,
Our SQL Server 2005 database is backed up nightly via a Maintenance Plan, when the backup completes I would like a copy of the .bak file ftp'd to a reporting server and then restored there nightly. The restore command I am happy enough with, it is the filename that I am having trouble with. The Maintenance Plan adds a date/time stamp to the .bak file that obviously changes nightly, is it possible to rename the .bak file to a specific name so I can use that specific name in the restore step?
Thanks,
jo
March 19, 2010 at 12:26 pm
In the Maintenance Plan wizard, the Backup Database task, you can use the 'Back up databases across one or more files:' option to specify a specific file to write your backup to, and then specify the 'Overwrite' option for when the file already exists. This means you'll only ever have one 'version' of your backup at any given time, of course.
If you use the wizard's 'Create a backup file for every database' option, you'll get the date/time stamp added.
As an alternative, you could simply write SQL script to perform the backup to a specific file name, and then 'manually' schedule that as a SQL Agent job. If you need help coming up with the right syntax, you can use the backup 'wizard' (right-click on the database in SSMS, choose 'Tasks | Backup') to set your options and destination name, then click the 'Script' button at the top of the page to give you the proper T-SQL code. Copy that to a SQL job step and set up your schedule.
Rob Schripsema
Propack, Inc.
March 23, 2010 at 3:59 am
conor.devlin (3/19/2010)
I would like a copy of the .bak file ftp'd to a reporting server and then restored there nightly.
i guess you can use database mirroring to accomplish this task instead of copy backup every nite
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 23, 2010 at 8:38 am
conor.devlin,
Do you want the data for reporting propose (Read only)?
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
March 25, 2010 at 12:30 pm
Why destroy information which may be useful, that is the timestamp?
Change your restore script to dynamically set the fname of the file to be restored.
http://blog.tech-cats.com/2007/10/sql-server-script-to-restore-database.html
http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/66372/
March 26, 2010 at 9:56 am
We find that it's easiest to backup to a Backup Device, that'll have a static name. Then we XCopy that Backup device file to the server were we want to perform the restore. Next we run a job to perform the restore.
We do all of this with scheduled jobs, so it's automated. If you want to save the original Backup Device file, you can just create a batch job to copy and/or rename it. That batch step can also be added as a step to one of the jobs.
As far as the timestamp being valuable, you can always get that info by looking at the file's details.
March 26, 2010 at 7:24 pm
Don't create a maintenance plan. Just create a job with T-SQL.
In the job do a backup database to <<NAME>>
Just write disk = '<<DBNAME>>.BAK'
Then restore the same name nightly, it will backup the db with the same name every time it runs, so you can restore from the same name
This article from BOL describes how to do a backup in T-SQL:
http://msdn.microsoft.com/en-us/library/ms186865.aspx
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
March 26, 2010 at 7:55 pm
emily-1119612 (3/25/2010)
Why destroy information which may be useful, that is the timestamp?Change your restore script to dynamically set the fname of the file to be restored.
http://blog.tech-cats.com/2007/10/sql-server-script-to-restore-database.html
http://www.sqlservercentral.com/scripts/Backup+%2f+Restore/66372/
I'm with Emily here - it's easy enough to build a script to get the filename for the restore. Additionally, you could build a script to perform your backup that generates the filename - and also generates a .sql file with that filename in it to perform the restore. You would ftp that file also - and use it to perform the restore.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 29, 2010 at 5:51 am
In the restore routine the first thing I just do is query the remote (ie server that's performing the backup) MSDB database to retreive what the filename was and just insert that into the restore script, that way you don't have to do any file management at all.
March 30, 2010 at 9:26 am
1) stop using maintenance plans for doing maintenance. 🙂
2) Ola Hallengren has some WONDERFUL scripts for doing all the standard maintenance stuff (ola.hallengren.com)
3) as someone else mentioned you can interogate msdb.dbo.backupset to get filenames for backups. I custom-built a log-shipping system for a client that handles 7300+ databases on a single server each night. Works like a champ. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 31, 2010 at 7:02 am
Querying the msdb tables looks something like below:
SELECT TOP 1 bmf.physical_device_name [Backup File]
FROM msdb.dbo.backupmediafamily bmf
INNER JOIN msdb.dbo.backupset bs
ON bmf.media_set_id = bs.media_set_id
WHERE bs.database_name ='AdventureWorks'
ORDER BY bs.backup_finish_date DESC
You can also get the logical file name of the database (that are required for restore) from the table msdb.dbo.backupfile.
Thanks
Manoj
John
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply