October 12, 2009 at 10:11 am
We have been working with development consultants who often times need a database backup. If I run a full backup to a location separate from our normal backups and then delete it, will this cause a problem with restoring that database later? Is it recorded as part of the backups, is it expecting it to exist?
October 12, 2009 at 10:21 am
You should check out copy-only backups in BOL. They're recorded in the backup set, but don't affect the sequence of backups.
Greg
October 12, 2009 at 10:42 am
tvanharp (10/12/2009)
If I run a full backup to a location separate from our normal backups and then delete it, will this cause a problem with restoring that database later?
Only if you are using differential backups. If all you're running is full and log backups then it won't matter. If you're running differential backups then it means that all differential backups after that are based on that ad-hoc backup and if the ad-hoc backup is deleted all of those differential backups are useless.
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
October 12, 2009 at 4:18 pm
Thanks for the info.
I found that I can't restore the backup created with the copy only clause in mgnt studio.
The restore source was blank.
I scripted it using the following and that works.
RESTORE DATABASE Testing
FROM DISK = 'C:\testing.bak'
WITH FILE = 1;
October 13, 2009 at 1:17 am
tvanharp (10/12/2009)
I found that I can't restore the backup created with the copy only clause in mgnt studio.
Known bug. Can't recall if there's a connect item opened for it or if it was fixed in a service pack.
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
October 13, 2009 at 1:21 am
GilaMonster (10/13/2009)
tvanharp (10/12/2009)
I found that I can't restore the backup created with the copy only clause in mgnt studio.Known bug. Can't recall if there's a connect item opened for it or if it was fixed in a service pack.
Interesting, I backup and restore "copy only" backups all the time, never had any issues.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 13, 2009 at 1:32 am
Silverfox (10/13/2009)
Interesting, I backup and restore "copy only" backups all the time, never had any issues.
T-SQL restore statement or Management studio restore dialog?
It was a known problem on SQL 2005 management studio. I ran into it a few times myself. Don't recall if it was fixed in a service pack and, if it wsa, which one.
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
October 13, 2009 at 2:42 am
Now you habe me curious, i have 2005 and 2008 on my machine, going to check that out now. I am assuming that the copy only was. backup done as tsql, management studio for the restore
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 13, 2009 at 3:05 am
That is really strange. I done a T-SQL copy only backup using SSMS 2005. tried to do a restore. got a blank restore option, so couldnt select the backup to restore. done a restore headeronly, shows no backup description. even playing around with adding the backup description doesnt seem to work.
If you keep re-trying to do the restore, it eventually sees all of the backup sets.
my build version is 9.0.4035
Something isnt right...
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 13, 2009 at 9:06 am
same issue I have and same version. Like you said in the header only you see it. But the restore source in the mgnt studio dialog is blank.
v 4035 is SP 3 without cumulative 10 and 11. maybe it's in the hotfixes. Not a big deal, looking forward to implementing 2008.
October 13, 2009 at 10:05 am
Silverfox (10/13/2009)
my build version is 9.0.4035
That the server version or management studio version?
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
October 13, 2009 at 4:23 pm
Same version for both.
October 14, 2009 at 9:29 pm
You can only do COPY_ONLY backups/restores in SQL Server 2005 with t-sql.
In SQL Server 2008 you can use the GUI to make and restore COPY_ONLY backups.
Edit - Pretty strange any of these four posts showed up considering I couldn't get a reply from the site and got timeout errors each time I tried to post. I actually gave up in the end...
October 14, 2009 at 9:33 pm
:w00t:
October 14, 2009 at 9:35 pm
:w00t:
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply