get table from backup - 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 2005 Forums

SQL Server Administration (2005)

get table from backup

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-04-25 : 09:41:48

i have a backup from yesterday

is there any easy way to restore just one table from the backup?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-25 : 09:56:20

Simple way would be to restore the database on the test server under different name and then use SSIS to transfer data for desired table.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-04-25 : 11:05:37

so how do i do that?
i should create the second db and then right click and do restore table?

when i tried to do this -- i got an error that i'm restoring the wrong db --

the backup set holds a backup other then the existing database
i thought you could not transfer data from one to another in sql 2005.
but i guess i could select from one table into the old one???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-25 : 13:13:59

You will need to go to the options page to select new files. The restore command without the with move option would try to use the same MDF and LDF file names that were used on the source database.

Also, you do not need to create the database first. You can do a restore without it being there already.

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-04-25 : 13:19:46

so please help me -- how do i restore it to a new database?
with what options??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-25 : 13:21:31

Use the same screen that you did before when you got the error, but this time after you are done selecting the file, go to the option page. You get to it on the main restore window, the link is on the left-hand side of the screen.

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-04-25 : 13:36:37

so how do I change the name -- i tried changing the name of the backup file to restore to but that gave me the same error

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-25 : 13:42:31

You are missing my point. You change either the location of the MDFs and LDFs or you can change the file names. You still need to reference the same backup file. Have you gone to the Options page yet?

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

Kristen
Test

22859 Posts

Posted - 2007-04-25 : 13:46:27

This looks to be part of the same problem:

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-04-25 : 13:52:31

yes can you please help me -- how can i get the backup to a new db -- I tried chaning the name of the file (.mdf and .ldf) but it returned the same error

Kristen
Test

22859 Posts

Posted - 2007-04-25 : 13:54:47

For a worked example of restore syntax see:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE%20syntax%20/%20example

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-25 : 13:57:14

Then let's try the restore command instead of the GUI since I can't see what you are selecting.

Run restore filelistonly to get the file names. Post the results here.

RESTORE FILELISTONLY
FROM DEVICE = 'E:\Backup\SomeBackupFile.BAK'

Please put your path and filename in there.

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-04-25 : 14:09:41

is filelistonly the new db?
otherwise where do i put the new db name -- i do not want to overwrite the old one as it was updated since then - I just want to get one table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-25 : 14:11:14

restore filelistonly is a command. The command doesn't do anything except show you what's in the backup. We need the output in order to help you restore.

Please lookup the command in SQL Server Books Online for more details.

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-04-25 : 14:18:29

Msg 155, Level 15, State 1, Line 1
'DEVICE' is not a recognized Device Type option.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-04-25 : 14:26:51

i'm looking online

RESTORE DATABASE Northwind
FROM DISK = 'C:\Backups\Northwind_Full_20060307.BAK'
WITH NORECOVERY

how would i change this to restore the db to a db with a new name?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-25 : 14:37:41

I had a typo in my restore filelistonly. Here you go:

RESTORE FILELISTONLY
FROM DISK = 'C:\Backups\Northwind_Full_20060307.BAK'

Post the output.

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-04-25 : 14:58:22

tusers C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tusers.mdf D PRIMARY 14680064 35184372080640 1 0 0 A1D5A228-316D-478C-8155-8C9E68C63492 0 0 10354688 512 1 NULL 0 00000000-0000-0000-0000-000000000000 0 1
tusers_log C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tusers_log.ldf L NULL 13959168 2199023255552 2 0 0 ABDC990C-8F86-45AF-8A26-A26E5F6CA5FA 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-25 : 15:24:29

RESTORE DATABASE tusers_temp
FROM DISK = 'C:\Backups\Northwind_Full_20060307.BAK'
WITH MOVE 'tusers' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tusers_temp.mdf',
MOVE 'tusers_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tusers_temp_log.ldf',
RECOVERY

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-04-25 : 15:40:13

thanks that sort of worked but restored the table and it has too little records to be correct - maybe the backup was not a full backup -- is that possible

i guess it's basically lost ...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-25 : 15:44:15

It is definitely a full backup as the command would be different if it weren't. If it were a differential backup, then we'd also have WITH DIFFERENTIAL in there. If it were a transaction log backup, then it would be using RESTORE LOG instead.

Don't you have older backups?

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources