December 29, 2009 at 10:01 am
Hi. I'm no SQL Server expert, and I'd be grateful if someone could clarify something for me.
I've been asked to do a one-off backup to disk (for archiving) of a SQL Server 2005 user database, but am uncertain which overwrite options to select, and what the implications are of 'Backup to a new media set, and erase all existing backup sets'.
The DB in question gets backed up daily (scheduled) to a specific directory. Only the most recent backup is retained. A substantial number of other databases on the same server are backed up in the same way, each to its own directory.
When I right-click the DB and choose 'Backup', the path and file displayed in the 'Destination' box are those of the daily scheduled backup. I want to place the one-off backup in a separate folder, and don't want to erase the existing backups of this or any other DB.
I presume I use 'Add...' to specify a new path and filename for the one-off backup, highlight it when I run the backup, and leave the existing alone(?), but I don't know what effect the various selections on the Options tab will have and therefore which to choose.
Alternatively, can I just use Explorer to copy and rename a scheduled backup file? Any advice much appreciated.
December 29, 2009 at 10:39 am
edtraviles (12/29/2009)
I presume I use 'Add...' to specify a new path and filename for the one-off backup, highlight it when I run the backup, and leave the existing alone(?),
No. If you do that the backup will be striped across the two destinations, half in each and you'll need both to do the restore.
Remove all backup locations from the box, use add to specify a new file somewhere and don't worry about the options. They're there for when you're appending backups to a single file (not usually a recommended practice)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 29, 2009 at 10:50 am
Gail is right.
Alternately, I suggest you take a look at the backup script below.
BACKUP DATABASE [DBNAME]
TO DISK = N'X:\DBNAME_Full_DDMMYY_Time.bak'
WITH NOFORMAT, NOINIT,
NAME = N'DBNAME-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
GO
All you need to do is replace the DBNAME with your Database Name (twice) and X:\DBNAME_Full_DDMMYY_Time.bak with the path on your server. Remember the to include your database name here also.
You would be better if you check that drive has enough space for your full backup.
You choose whatever way to backup, see that you use a unique name so that you do not need to worry about the options about overwriting and deleting old backups etc.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 29, 2009 at 10:50 am
Thanks for the reply.
I presume removing the existing backup destination in this dialog won't affect anything except the one-off backup I want to run, i.e. the removed destination will still be valid for the daily scheduled backups for this database?
December 29, 2009 at 10:51 am
Yes that's right.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 29, 2009 at 10:54 am
Sorry - I was referring to Gail's reply, when I added my second post. I hadn't seen Bru's reply at that point. Either way (script or GUI), I presume any existing backups and any scheduled backups will be unaffected?
December 29, 2009 at 10:54 am
OK -many thanks, both.
December 29, 2009 at 10:59 am
edtraviles (12/29/2009)
Sorry - I was referring to Gail's reply, when I added my second post. I hadn't seen Bru's reply at that point. Either way (script or GUI), I presume any existing backups and any scheduled backups will be unaffected?
Yeah I knew you were referring to Gail's reply, since there was a difference of 11 seconds between my reply and yours.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 29, 2009 at 12:23 pm
edtraviles (12/29/2009)
I presume removing the existing backup destination in this dialog won't affect anything except the one-off backup I want to run, i.e. the removed destination will still be valid for the daily scheduled backups for this database?
It won't affect anything. The backup destination in the dialog is simply the location of the last backup that was manually run using this management studio.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 29, 2009 at 12:29 pm
That said...
Check with whoever's responsible for the backups and ask whether or not there are any differential backups been run. If there are, to avoid messing up the recovery chain, you would need to do the backup with the COPY_ONLY option.
In 2008 there is a copy_only checkbox on the main screen of the backup dialog. If you use Bru's script then replace the NOFORMAT and NOINIT with COPY_ONLY (no point in specifying NoFormat or NoInit as they're only useful when backing up to an existing file)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 29, 2009 at 2:41 pm
GilaMonster (12/29/2009)
That said...Check with whoever's responsible for the backups and ask whether or not there are any differential backups been run. If there are, to avoid messing up the recovery chain, you would need to do the backup with the COPY_ONLY option.
In 2008 there is a copy_only checkbox on the main screen of the backup dialog. If you use Bru's script then replace the NOFORMAT and NOINIT with COPY_ONLY (no point in specifying NoFormat or NoInit as they're only useful when backing up to an existing file)
Gail, I am glad you caught that - it was going to be my response also. Since this is a one-off backup, I would recommend using the COPY_ONLY option even if there are no differentials backup *currently* being run. Who knows - a couple of months from now you might need another one-off and now differentials are being performed. Better safe than sorry - I say.
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
December 29, 2009 at 2:48 pm
I think Jeffrey has some good advice. don't disrupt things if you don't have to.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply