Aug 17 2009 11:33AM GMT
Posted by: mrdenny
BACKUP DATABASE,
Backup & recovery
Taking a full backup when doing major database upgrades is a great idea. However if you are taking differential backups this one off full backup will break the differential backup chain. Continued »
Jun 1 2009 1:00PM GMT
Posted by: mrdenny
Backup & recovery,
sp_delete_backuphistory,
Database Administration
Something that most people don’t release needs a little TLC in SQL Server is the backup history.
Every time a database is backed up records are written about it. Over time this can add up to a lot of useless data floating around the SQL Server in the msdb database.
If you like to use the UI to restore you databases, this can also lead to the UI stalling when the restore database window comes up.
Fortunately Microsoft has provided a system stored procedure which you can use to clean up this old data. This procedure is the sp_delete_backuphistory system stored procedure. The usage of this procedure is very simple. It takes a single parameter @oldest_date which is simply the oldest date of data you want to keep. As an exmple:
EXEC sp_delete_backuphistory ‘1/1/2009′ would delete backup data older than Janunary 1, 2009.
Denny
Mar 8 2009 3:20AM GMT
Posted by: mrdenny
Article,
Backup & recovery
Last week I published a new article on Enterprise IT Planet called “Developing a SQL Server Database Backup Plan” in which I go over some of the techniques which are used to backup databases and why you need to ensure that your databases are backed up.
Denny
Jan 2 2009 8:46PM GMT
Posted by: mrdenny
Backup & recovery,
Database security,
SQL Injection,
JournalSpace.com,
SecurityFightClub
In case you live under a rock and haven’t heard about Journalspace.com’s little mistake, they have gone out of business due to a database problem. Here’s a screenshot in case the site is down when you look at it.
In a nutshell it appears that they were relying on a RAID1 array as the database backup. While we see this all the time in small database shops as noted on /. this site has been up since 2002 and had an Alexa page rank of 106,881 with 14k monthly visitors (according to Quantcast). For a site so large to be making such a simple mistake is just unacceptable. Continued »
Dec 29 2008 11:00AM GMT
Posted by: mrdenny
Backup & recovery,
RESTORE LOG,
Restore Database
I’m often asked (both online and offline) once you have all your database backups, in what order to they need to be restored in?
I’ve actually asked this very question to senior level DBAs as an interview question before, and gotten some very interesting answers.
When restoring your database you start with your full backup, then restore your diffential backup (if you have taken one) then restore all the transaction log backups begining with the backup taken after the full (or differential) and going until the last transaction log backup available (or until you reach the point in time you want to stop the restore at).
Denny
Aug 25 2008 11:00AM GMT
Posted by: mrdenny
Backup & recovery,
SQL Server 2005,
Log Shipping,
Recovery,
KILL,
RESTORE LOG,
xp_dirtree,
xp_delete_file
So you’ve been tasked with setting up a quick and dirty reporting server. The goal is to restore the log files from the production server to the reporting server nightly.
The backups are simple, use the SQL Maintenance plan to backup the logs, and then copy them to the remote machine. But how do you restore the logs to the reporting server nightly.
Well I’ve got a two step SQL job which should help you out.
Step 1 kills all current sessions in the database, and step 2 does the actual restores.
The code for step 1 is:
declare @spid varchar(20)
declare cur CURSOR FOR
select spid
from sys.sysprocesses
where dbid = db_id(’Your Database Name Here’) /*<—Put your database name here*/
and spid > 50
open cur
fetch next from cur into @spid
while @@FETCH_STATUS = 0
BEGIN
exec (’kill ‘ + @spid)
fetch next from cur into @spid
END
close cur
deallocate cur
The code for step 2 is:
create table #Files
(FileName nvarchar(4000),
Depth int,
IsFile bit)insert into #Files
exec xp_dirtree ‘d:\’, 1, 1
delete from #Files
where IsFile = 0
declare @FileName nvarchar(4000)
declare cur CURSOR FOR SELECT FileName from #Files
open cur
fetch next from cur into @FileName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @FileName = ‘D:\Path\To\Your\Log\Backups\’ + @FileName
RESTORE LOG YourDatabaseNameHere FROM DISK=@FileName WITH STANDBY=’D:\Path\To\Your\StandBy\File.standby’
IF @@ERROR <> 0
exec xp_delete_file @FileName
fetch next from cur into @FileName
END
close cur
deallocate cur
I hope this makes your process easier. Now this code only works on SQL Server 2005 and up as the system stored procedures which I use were not included until SQL Server 2005.
Denny
Apr 1 2008 6:43PM GMT
Posted by: mrdenny
Backup & recovery,
Article
I’ve just published a new tip on SearchSQLServer.com called Tips for scheduling and testing SQL Server backups. In it I’m talking about server backups including how to schedule those backups on SQL Server Express edition which doesn’t have the SQL Server Agent.
Denny
Mar 16 2008 9:39PM GMT
Posted by: mrdenny
Backup & recovery,
Quest Software,
Webcam
The webcast which I did with Quest Software a couple of weeks ago “Recover the data, the whole data and nothing but the data you need” has had the recording posted for viewing on the Quest website. If you missed the webcast live now is your chance to view it.
Denny
Feb 29 2008 12:01AM GMT
Posted by: mrdenny
Backup & recovery,
Quest Software,
Lightspeed,
Webcam
Coming on the heals of the extremely successful webcast “Under The Hood of SQL Server - Checking Out Internals”, Quest Software has asked me to co-present another webcast. This one is entitled “Recover the Data, the Whole Data and Nothing but the Data You Need” and will be on March 6, 2008 at 8am Pacific (11am Eastern). I’ve been told that so far the sign up rate has been incredible so far.
If you are interested in the product or doing object level restores (restoring one table or stored procedure, etc instead of restoring the entire database) then I highly recommend the webcast; granted I am a little biased.
See you at the webcast.
Denny