Proactive thoughts on test restores

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Proactive thoughts on test restores

Author

Topic

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-07-25 : 09:10:14

Taking into account the reliability of SQL Server backups (be it reliable or not), would you perform test restores only on major production databases or as many as possibly capable?

Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

nr
SQLTeam MVY

12543 Posts

Posted - 2007-07-25 : 10:38:51

On all of them.
If they are important then test restore every backup.
see
http://www.nigelrivett.net/SQLAdmin/s_TestRestore.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-07-25 : 10:41:43

They're all important (to each department), but there's over 100 databases.

Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-25 : 11:16:51

You can set schedule to test them, pick several dbs to restore each time.

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-07-25 : 11:21:13

I'll do that. Thanks

Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

Kristen
Test

22859 Posts

Posted - 2007-07-25 : 12:01:38

If you don't test-restore a database you can't be sure you'll be able to restore it for real!

You could do once-a-week test-restore of FULL backup only for databases where you are happy to loose a weeks changes Natch!

If you need to be able to restore to-the-minute from TLog backups then they all have to be restored and DBCC Checked.

If you test-restore to another machine there are potentially some "tasks" that you can offset from the production server:

CHECKDB
SHOWCONTIG to establish which indexes are fragmented and need reindexing / defragging.

You might be able to do move some reporting load onto that server too ...

Kristen

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-07-25 : 12:14:56

That's a lot of work.

What I Select "min(columnname)", and the column is of type varchar what is this representing? The last row from the table?

select @s = min(lname) from #files where lname > @s

Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

Kristen
Test

22859 Posts

Posted - 2007-07-25 : 12:34:40

"What I Select "min(columnname)", and the column is of type varchar what is this representing?"

Lowest alphanumeric (well, ASCII actually) value.

"The last row from the table?"

For that you need something like

SELECT TOP 1 *
FROM MyTable
ORDER BY MyUpdateDate DESC
or
ORDER BY MyIdentity ASC

Kristen

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-07-25 : 12:44:02

I've got to apologise for all my typos. I only notice them after someones replied.

Lowest ASCII value? I'm confused now.

Nigel has this in his code:

	select @cmd = 'dir /B ' + @remotepath + @dbname + '*.*'
	create table #a (s varchar(2000))
	insert #a exec master..xp_cmdshell @cmd


	delete	#a 
	where s is null
	or s not like '%.bak%'


	select 	@filename = max(s) from #a

I thought Max was only used with numbers. So what will @filename now store (above)?

and one (last) question in Nigels code...

if @d > @StartDate AND @i > 20

Does this mean...

If the date the db was created is greater then todays date (when would it not be?) AND if the number of databases (with that name) is greater than 20?

That can't be right.

Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

Kristen
Test

22859 Posts

Posted - 2007-07-25 : 14:45:59

"I've got to apologise for all my typos"

I sue Google Toolbar's SpellChecker on all Web Forms ... so now all my posts have Grammar errors instead because I don;t need to proof them anymore, given that the spelling is without error ...

"Lowest ASCII value? I'm confused now."

"AAAAAAAAAAAAAAAA" beats "B" in the MIN() stakes.

But CHAR(0) would be "lower" still.

"I thought Max was only used with numbers"

Nope. You can have the MAX(SurName) or MAX(DateOfBirth). For a field with less "structured" data it may make less sense though. An alphanumeric field with 2, 11, 111, 1111, ... will give "11" as the minimum value (i.e. the Alphanumeric minimum)

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-25 : 14:50:11

quote:
Originally posted by drewsalem

That's a lot of work.

You don't do it manually. It would all be done automatically through jobs. It just requires a bit of work to get it all coded and scheduled. Nigel's code should help you get there though.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-07-26 : 04:30:03

Sorry, when I said I was confused, I meant in terms of Nigel's code (Nigel, where are you?!). It's producing the contents of the back up directory and then by using the max function, selects the most recent backup. How does it know what the most recent backup is from the ASCII value of a word?

It's a neat script though. I'd written a test restore script that looked at the Backupset table and just ordered it descendingly by backup_start_date. I'm not sure whether to scrap it now and use Nigel's :)

Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

Kristen
Test

22859 Posts

Posted - 2007-07-26 : 06:31:53

"How does it know what the most recent backup is from the ASCII value of a word"

I haven't looked at the script, but if the backup filename is of the style:

MyDatabase_yyyymmdd_hhmm.BAK

then that is an every-ascending series (well, until the year 10,000 anyway!).

"I'm not sure whether to scrap it now and use Nigel's"

If Nigels is working off the physical files in the directory, and yours is working of the MSDB, then for Disaster Recovery I'd be more inclined to go with physical files ... you can then work of what you were able to rescue from Tape etc., rather than some possibly MORE or LESS recently state-of-affairs from MSDB.

Kristen

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-07-26 : 06:48:17

Ah. Thanks, Kristen.

Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-09-14 : 05:09:07

Hi all,

I'm extending a script to restore bak files and their corresponding trns instead of just restoring the bak files.

Currently the *last* backup file is extracted from the backup folder and copied to the restore server.

Now I need to copy all trn files that were created *after* the lastest bak file was created.

Any ideas of how to isolate the relevant trn files? For instace, can file creation date be compared in dos?

Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-14 : 05:34:25

yes it can... I used it instead of /b use /N

--------------------
keeping it simple...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-14 : 05:41:34

you should also check if a new full has been created unscheduled and pick that up for restore instead of the trns, then from there pick up the trns created afterwards

it's quite handy if you have the names down to the dot but sometimes, stuff happens and files are named differently

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2007-09-14 : 05:45:18

Two things that may help.

There is a script somewhere here which will restore based on what is in a given folder.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Restore+Full+and+all+TLogs+backups+based+on+files+in+disk+folder

The MSDB database stores a history of all backups - doesn't mean the file is still on disk, but might be the basis for generating the "ideal" script, and then worry about which files are missing perhaps?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54300#273265

Kristen

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-09-14 : 07:48:44

Thank for the reply.

It's funny that. I posted the thread you refered too! Heck knows where I found that script. With some alteration, it will do fine.

I do have a question though.

It uses print statements as such:

BEGIN
						PRINT 'RESTORE DATABASE [' + @NEWDBNAME + ']'
						PRINT 'FROM DISK = ''' + @BACKUPDIR + @BCKFNAME + ''''
						PRINT 'WITH MOVE ''' + @ORIGDBNAME +''' TO ''' + @NEWDEST + @NEWDBNAME + '.MDF'','
						PRINT '     MOVE ''' + @ORIGLGNAME +''' TO ''' + @NEWDEST + @NEWDBNAME + '_LOG.LDF'','
						IF @CURRENT < @MAXID PRINT '     NORECOVERY' 
						IF @CURRENT = @MAXID PRINT '     RECOVERY' 
					END
				ELSE --IF ITS NOT A BAK FILE I.E. A TRN FILE
					BEGIN
						PRINT 'RESTORE LOG [' + @NEWDBNAME + ']'
						PRINT '     FROM DISK = ''' + @BACKUPDIR + @BCKFNAME + ''''
						IF @CURRENT < @MAXID PRINT '     WITH NORECOVERY' 
						IF @CURRENT = @MAXID PRINT '     WITH RECOVERY -- STOPAT = ''YYYY-MM-DD UU:MM:SS.000'''
					END

I would normally assign a statement to a variable @sql and execute it.

Does this not need to be done?
Am I missing something?

Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

Kristen
Test

22859 Posts

Posted - 2007-09-14 : 08:42:49

"Am I missing something?"

Nah, don't think so. (Helps if Query Analyser is in Text Results mode, rather than Grid Mode

)

My preference is to "generate" a script which I can review, hand-edit, and then Execute when I am happy, possible bit-by-bit if necessary.

SET @sql = 'xxx' EXEC (@sql)

is a bit out-of-sight-out-of-mind for me!

Kristen

drewsalem
Constraint Violating Yak Guru

304 Posts

Posted - 2007-09-14 : 08:55:20

So how is his code executed if he's only using print statements?

Drew
---------------------
"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK."

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources