Back up and Restore database - SQL Server Forums

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)

Back up and Restore database

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-03-30 : 08:45:12

Lisa writes "I have been testing my back up and restore and it seems pretty straight forward. However there is another issue that keeps bugging me.

3) Transaction log (back up the record of all the changes made to db)

Test 1
I created a db called 'TestDB'
I created 1 table called 'Contacts' that has 1 row.
I did a full back up (back up the entire database) for 'TestDB'

Ok, now, I want to test to see how the back up/restore work, so

I deleted this 'TestDB'
Then I did a restore and it restored my table 'Contacts' with 1 record. Great.

Test2
So, now in 'TestDB' database, I still have 1 table and 1 row so
I added another row into this table (so now I have 2 rows).

I want to see if I delete the 'Contacts' table this time and do a restore, would I be able to retreive the new row I just added or not. So,
I deleted the 'Contacts' table without doing any backup.
Then I did a restore and it restored ONLY the the first row.
So, the new row (the new change) I just created is not restored.

My question is: Do you have to back up your db every single time you make a change to your db in order to save the new changes?

I posted this message on the other forum and AjarnMark did give me a quick response. However, he asked me to discuss this question in this forum. So, below is his response for my question. However, I did exactly like he asked, I still didn't get that new row restored.

Here is his responses:
********************************************************
# re: Test your Backups! 3/25/2004 1:44 PM AjarnMark
Lisa, these types of questions are best posed in a discussion forum like http://www.sqlteam.com. But I'll give you a quick response here.

In short, the answer is yes. If something happens to corrupt your database since your last backup, one of the first things you need to do is a TRANSACTION LOG backup (assuming your database is in FULL RECOVERY mode and not SIMPLE). Then you can restore your full backup to get everything up to that point, and then restore your Transaction Log up to the point where everything is okay.
*******************************************************

And here is what I have done (based on his response).
I created a db called, 'TestDB'
I created a table called, 'Contacts' and added 1 row into it
I created a FULL backup
My db is in FULL recovery mode
I then deleted 'Contacts' table
I restored the Full back up and 'Contacts' table is restored successfully.
I then added a new row to 'Contacts' table
Then I deleted the 'Contacts' table

Ok, here is the tricky part. I did a TRANSACTION LOG back up. Then I restored my FULL backup.
Then I restored my Transaction Log
Then I refreshed my db and 'Contacts' table is GONE.
?????????????????????????

I tested many times and I still got the same results.

Does someone know what the prolems are???

Thank you very much in advance.

Lisa."

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-30 : 10:06:55

I created a FULL backup
My db is in FULL recovery mode
I then deleted 'Contacts' table
I restored the Full back up and 'Contacts' table is restored successfully.
I then added a new row to 'Contacts' table
Then I deleted the 'Contacts' table

Ok, here is the tricky part. I did a TRANSACTION LOG back up. Then I restored my FULL backup.
Then I restored my Transaction Log
Then I refreshed my db and 'Contacts' table is GONE.

You did the Transaction Log backup "after" you deleted the table. The transaction log restore will roll all changes you have made since your full backup. If you deleted the table then make a transaction log backup. The restore of the transaction log backup will also delete the table. Make sense?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-30 : 12:32:18

Yes. If you do a transaction log backup after the second row is added, that row would be there on the restore. If you do one after the table is deleted, the table wouldn't be there at all on the restore.

Most places do a full backup once a day, then transaction log backups every 15 minutes or so. That way you can roll back in 15 minute increments. Make sense?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-30 : 12:35:24

Please do not post multiple times:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=33797

You needed to have performed a backup log after the second row was added.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-30 : 12:56:51

When you use AskSQLTeam, it doesn't get posted right away. I don't think that you can go back and delete the other one.

It's very rare that we even hear back from AskSQLTeam posters that we have answered their question or not.

Let us know if you need more help. We can continue on in this thread.

Tara

MuadDBA

628 Posts

Posted - 2004-03-30 : 15:20:35

I beleive there are tables in the msdb database that have your history in there. But (and no offense intended) at your level of experience, I wouldn't recommend messing around with the system tables just yet. Don't do the restore through the GUI, do it via query analyzer, and just input the commands you need and the files you want to restore from. Beleive me, you will be happier in the future if you get used to doing this now.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-30 : 15:21:53

I have a stored procedure for that:



----------------------------------------------------------------------------------------------------
-- OBJECT NAME	        : isp_DeleteBackupHistory
--
-- AUTHOR               : Tara Duggan
-- DATE			: October 14, 2003
--
-- INPUTS		: @DaysToRetain
-- OUTPUTS		: None
-- DEPENDENCIES	        : msdb..restorefile, msdb..restorefilegroup, msdb..restorehistory
--			  msdb..backupfile, msdb..backupset, msdb..backupmediafamily, and 
--			  msdb..backupmediaset
--
-- DESCRIPTION	        : This stored procedure deletes backup history.
--
-- EXAMPLES (optional)  : EXEC isp_DeleteBackupHistory 14
--
-- MODIFICATION HISTORY :
----------------------------------------------------------------------------------------------------
-- 
----------------------------------------------------------------------------------------------------
CREATE PROC isp_DeleteBackupHistory
(@DaysToRetain INT)
AS

SET NOCOUNT ON

DELETE FROM msdb..restorefile
FROM msdb..restorefile rf
INNER JOIN msdb..restorehistory rh ON rf.restore_history_id = rh.restore_history_id
INNER JOIN msdb..backupset bs on rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)

DELETE FROM msdb..restorefilegroup
FROM msdb..restorefilegroup rfg
INNER JOIN msdb..restorehistory rh ON rfg.restore_history_id = rh.restore_history_id
INNER JOIN msdb..backupset bs on rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)

DELETE FROM msdb..restorehistory
FROM msdb..restorehistory rh
INNER JOIN msdb..backupset bs on rh.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)

DELETE FROM msdb..backupfile
FROM msdb..backupfile bf
INNER JOIN msdb..backupset bs on bf.backup_set_id = bs.backup_set_id
WHERE bs.backup_finish_date < (GETDATE() - @DaysToRetain)

SELECT media_set_id, backup_finish_date
INTO #Temp 
FROM msdb..backupset
WHERE backup_finish_date < (GETDATE() - @DaysToRetain)

DELETE FROM msdb..backupset
WHERE backup_finish_date < (GETDATE() - @DaysToRetain)

DELETE FROM msdb..backupmediafamily
FROM msdb..backupmediafamily bmf
INNER JOIN msdb..backupmediaset bms ON bmf.media_set_id = bms.media_set_id
INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id

DELETE FROM msdb..backupmediaset
FROM msdb..backupmediaset bms
INNER JOIN #Temp t ON bms.media_set_id = t.media_set_id

DROP TABLE #Temp

SET NOCOUNT OFF


GO

I believe that there is also a system stored procedure that does something very similar.

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-30 : 18:40:31

EM can be quicker for some things than Query Analyzer, but you're really not learning anything about what's going on and have no control over how it's happening.

If you learn QA, you have "exact" control over what's happening. Also, once you build your script library, you will find you can do things as fast or faster in QA. I spend a lot of time cutting, pasting, and modifying things. I'm sure Tara and a lot of other people on here do also.

There's also a lot of things I don't have to do anymore at all. I have about 200 million scripts to do them for me. lol

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

MuadDBA

628 Posts

Posted - 2004-03-31 : 09:40:59

missinglct, check out some of the posts on AjarnMark's SQL Server weblog here at SQLTeam, he has some very good discussions about why you should use QA instead of EM, especially because of really really stupid things that EM does in the background without telling you.

missinglct
Yak Posting Veteran

75 Posts

Posted - 2004-03-31 : 14:52:49

Ok, I read this stuff from BOL.
I know I can create a 'Sales db' without adding this command.
****************
USE master
GO
****************
Does anyone know the purpose of using this command (USE master and GO)????? Thx.

***********************************************8

USE master
GO
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\saledat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'c:\program files\microsoft sql server\mssql\data\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO

***********************************************8

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-31 : 14:57:46

From BOL:

USE
Changes the database context to the specified database.

----

It's the same thing as changing the database in Query Analzyer from the drop down list.

From BOL:

GO
Signals the end of a batch of Transact-SQL statements to the Microsoft® SQL Server™ utilities.

----

For scripts, you should always put which database you are going to use. It's good practice in case you accidentally execute a script in the wrong database.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-31 : 16:11:31

No, it doesn't make a difference for that particular statement. But let's say you want to add a table to the Northwind database. Rather than selecting which database in Query Analyzer, you script it out completely:

USE Northwind
GO

CREATE TABLE Table1
(
Column1 INT
)
GO

So if you don't put USE Northwind and you didn't select Northwind in Query Analyzer and your default database is master, then it'll add the object to master even though you wanted to add it to Northwind. So get in the habit of adding USE statement for deployment scripts.

Tara

missinglct
Yak Posting Veteran

75 Posts

Posted - 2004-03-31 : 16:25:22

Thx Tara, I got it this time.

BTW, I was learning how to use commands for creating tables....so much fun, really. I am learning the simple ones first. BOL has scripts for creating the 'Employee' table.
I cut and paste this stuff straight from BOL.

Take a look at the 3rd line (emp_id empid)
I thought it should be the data type (int or whatever) after the fieldname (emp_id) insteads of empid. It doesn't make any sense though. Is it a typo? I am afraid so but that cannot be because BOL is supposed to be accurated. Help.

/* ************************* employee table **************** */
CREATE TABLE employee
(
emp_id empid
CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED
CONSTRAINT CK_emp_id CHECK (emp_id LIKE
'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or
emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'),
/* Each employee ID consists of three characters that
represent the employee's initials, followed by a five
digit number ranging from 10000 through 99999 and then the
employee's gender (M or F). A (hyphen) - is acceptable
for the middle initial. */
fname varchar(20) NOT NULL,
minit char(1) NULL,
lname varchar(30) NOT NULL,
job_id smallint NOT NULL
DEFAULT 1
/* Entry job_id for new hires. */
REFERENCES jobs(job_id),
job_lvl tinyint
DEFAULT 10,
/* Entry job_lvl for new hires. */
pub_id char(4) NOT NULL
DEFAULT ('9952')
REFERENCES publishers(pub_id),
/* By default, the Parent Company Publisher is the company
to whom each employee reports. */
hire_date datetime NOT NULL
DEFAULT (getdate())
/* By default, the current system date is entered. */
)

*********************************************

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-31 : 16:29:13

Empid is a user defined data type in the pubs database. empid points to char(9). I don't use user defined data types, haven't needed to. But you should definitely read up on them.

Tara

missinglct
Yak Posting Veteran

75 Posts

Posted - 2004-03-31 : 16:31:05

Thx Tara.

missinglct
Yak Posting Veteran

75 Posts

Posted - 2004-03-31 : 16:44:50

This example creates 1 user-defined data type, empid in Sales db. Cool.

USE Sales
EXEC sp_addtype empid, 'char(9)', 'NOT NULL'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources