restore DB on SQL 2005 multiple files

Hello,

I have a SQL 2000 DB. Current tables in sql 2000 DB are in single file. I am planningto migrate the DB to sql 2005. I am going to partition the tables in sql 2005 and have multiple files. What is the best way to do this? Would backup/restore work? If I restore onto sql 2005 from sql 2000 backup, will the tables spread over different files automatically or not? Any ideas will be appreciated...

Thanks........

Using Backup/Restore may be the 'safest' method to migrate a database. Be sure to rebuild the indexes and update the statistics after restoring.

Once restored, you can add new files for the database.

Refer to this link for the steps to move tables to the new files.

http://sqljunkies.com/HowTo/B9F7F302-964A-4825-9246-6143A8681900.scuk

(And most likely, you really don't want tables to 'stripe' across multiple files.)

Restore Database

Dear All
I am have trouble restoring a database from a device called 1.bak. When I
try restoring the database it tells me that I need to be in single user mode.
To do this I go to the command prompt and type:
sqlservr -s BBC024579
where BBC024579 is the server name (and local pc name).
I get an error message saying that it cannot find the specified server
instance running.
I have tried this with the SQL Server Service running and not running.
I am using a Server 2000 developer edition on a local machine that will not
have anyone connected to it. Can someone offer me some help?
Thanks again.
Alastair
Hello,
Use the below command from Master database in query analyzer to restore the
database
ALTER DATABASE EAGLE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
Now issue the RESTORE database command. After the restore issue the below
command to make database multi user.
go
ALTER DATABASE EAGLE SET MULTI_USER
Thanks
Hari
"Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote
in message news:6B8EB502-98B3-4277-8C5A-0E1FD27E31F1@.microsoft.com...
> Dear All
> I am have trouble restoring a database from a device called 1.bak. When I
> try restoring the database it tells me that I need to be in single user
> mode.
> To do this I go to the command prompt and type:
> sqlservr -s BBC024579
> where BBC024579 is the server name (and local pc name).
> I get an error message saying that it cannot find the specified server
> instance running.
> I have tried this with the SQL Server Service running and not running.
> I am using a Server 2000 developer edition on a local machine that will
> not
> have anyone connected to it. Can someone offer me some help?
> Thanks again.
> Alastair
|||Hi Alastair
"Alastair MacFarlane" wrote:

> Dear All
> I am have trouble restoring a database from a device called 1.bak. When I
> try restoring the database it tells me that I need to be in single user mode.
Does the database already exist? If it does then you are probably getting
the message that other users are connected to the database, rather than it
needs to bin in single user mode. If this is the case check sp_who2 to see
what users are connected and use KILL for the given SPIDs or issue the
statements
USE dbname
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
USE master
RESTORE DATABASE myDatabase
FROM DISK = 'c:\1.bak'

> To do this I go to the command prompt and type:
> sqlservr -s BBC024579
> where BBC024579 is the server name (and local pc name).
If this is the default instance then you don't have to specify a name

> I get an error message saying that it cannot find the specified server
> instance running.
> I have tried this with the SQL Server Service running and not running.
> I am using a Server 2000 developer edition on a local machine that will not
> have anyone connected to it. Can someone offer me some help?
If you run NET START from a command prompt you should see SQL Server running
(or not)

> Thanks again.
> Alastair
John
|||John and Hari,
Thanks for the advise and I have now restored my database.
Thanks.
Alastair
"John Bell" wrote:

> Hi Alastair
> "Alastair MacFarlane" wrote:
>
> Does the database already exist? If it does then you are probably getting
> the message that other users are connected to the database, rather than it
> needs to bin in single user mode. If this is the case check sp_who2 to see
> what users are connected and use KILL for the given SPIDs or issue the
> statements
> USE dbname
> ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> USE master
> RESTORE DATABASE myDatabase
> FROM DISK = 'c:\1.bak'
>
> If this is the default instance then you don't have to specify a name
>
> If you run NET START from a command prompt you should see SQL Server running
> (or not)
>
> John

Restore Database

Dear All
I am have trouble restoring a database from a device called 1.bak. When I
try restoring the database it tells me that I need to be in single user mode
.
To do this I go to the command prompt and type:
sqlservr -s BBC024579
where BBC024579 is the server name (and local pc name).
I get an error message saying that it cannot find the specified server
instance running.
I have tried this with the SQL Server Service running and not running.
I am using a Server 2000 developer edition on a local machine that will not
have anyone connected to it. Can someone offer me some help?
Thanks again.
AlastairHello,
Use the below command from Master database in query analyzer to restore the
database
ALTER DATABASE EAGLE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
Now issue the RESTORE database command. After the restore issue the below
command to make database multi user.
go
ALTER DATABASE EAGLE SET MULTI_USER
Thanks
Hari
"Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote
in message news:6B8EB502-98B3-4277-8C5A-0E1FD27E31F1@.microsoft.com...
> Dear All
> I am have trouble restoring a database from a device called 1.bak. When I
> try restoring the database it tells me that I need to be in single user
> mode.
> To do this I go to the command prompt and type:
> sqlservr -s BBC024579
> where BBC024579 is the server name (and local pc name).
> I get an error message saying that it cannot find the specified server
> instance running.
> I have tried this with the SQL Server Service running and not running.
> I am using a Server 2000 developer edition on a local machine that will
> not
> have anyone connected to it. Can someone offer me some help?
> Thanks again.
> Alastair|||Hi Alastair
"Alastair MacFarlane" wrote:

> Dear All
> I am have trouble restoring a database from a device called 1.bak. When I
> try restoring the database it tells me that I need to be in single user mode.[/vbc
ol]
Does the database already exist? If it does then you are probably getting
the message that other users are connected to the database, rather than it
needs to bin in single user mode. If this is the case check sp_who2 to see
what users are connected and use KILL for the given SPIDs or issue the
statements
USE dbname
ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
USE master
RESTORE DATABASE myDatabase
FROM DISK = 'c:\1.bak'
[vbcol=seagreen]
> To do this I go to the command prompt and type:
> sqlservr -s BBC024579
> where BBC024579 is the server name (and local pc name).
If this is the default instance then you don't have to specify a name

> I get an error message saying that it cannot find the specified server
> instance running.
> I have tried this with the SQL Server Service running and not running.
> I am using a Server 2000 developer edition on a local machine that will no
t
> have anyone connected to it. Can someone offer me some help?
If you run NET START from a command prompt you should see SQL Server running
(or not)

> Thanks again.
> Alastair
John|||John and Hari,
Thanks for the advise and I have now restored my database.
Thanks.
Alastair
"John Bell" wrote:

> Hi Alastair
> "Alastair MacFarlane" wrote:
>
> Does the database already exist? If it does then you are probably getting
> the message that other users are connected to the database, rather than it
> needs to bin in single user mode. If this is the case check sp_who2 to see
> what users are connected and use KILL for the given SPIDs or issue the
> statements
> USE dbname
> ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> USE master
> RESTORE DATABASE myDatabase
> FROM DISK = 'c:\1.bak'
>
> If this is the default instance then you don't have to specify a name
>
> If you run NET START from a command prompt you should see SQL Server runni
ng
> (or not)
>
> John

restore database

hi,
i am facing one problem when i am going to restore backup file. there is one maasage is come (restore detabase must be used in single user mode when trying to restore the master database. restore database is terminating abnormally)

Quote:

Originally Posted by deepji2007

hi,
i am facing one problem when i am going to restore backup file. there is one maasage is come (restore detabase must be used in single user mode when trying to restore the master database. restore database is terminating abnormally)

The master database contains information about every other DB in the system.

Therefore in order to overwrite it, it has to be in a special mode where only one string of information could possibly be coming in.

To start SQL Server in single user mode, stop the SQL server and start it again using this command.

sqlservr -m

If this doesnt work, try browsing to the \Microsoft SQL Server\80\Tools\binn directory.

**note overwriting your master database can be very dangerous. Only do this if its completely necessary.|||hi,
i have stoped the SQL server but from where i give the commabd (sqlservr-m)|||

Quote:

Originally Posted by deepji2007

hi,
i have stoped the SQL server but from where i give the commabd (sqlservr-m)

From a command prompt type:

C:\> CD program files\microsoft sql server\80\tools\binn

C:\> sqlservr -m

restore backup (.bak) on another computer

We are running SQL Server 2000 on a single computer. We currently backup
once a week into .bak files which we keep on file. However, if this compute
r
were to fail we would have to reinstall software on a new machine. How can
we restore the old database files from the backups? I have installed SQL
Server on a test machine and found that you can't just restore. The new SQL
Server doesn't immediately recognize the backups from another computer.
Does anyone know how to do this?How do you try to do the restore and what is it that doesn't work?
Have you tried looking up the RESTORE command in BOL? In QA you can run :
RESTORE DATABASE YourDataBase FROM DISK =
'\\YourBackupLocation\YourBackupFile.BAK' WITH
RECOVERY, MOVE 'LogicalDataFileName' TO 'YourDatabasePath',
MOVE 'LogicalLogFileName' TO 'YourLogFilePath'
You can also do it from EM - right click on Databases ->All Task-> Restore
Database.
Regards
Steen
gl330k wrote:
> We are running SQL Server 2000 on a single computer. We currently
> backup once a week into .bak files which we keep on file. However,
> if this computer were to fail we would have to reinstall software on
> a new machine. How can we restore the old database files from the
> backups? I have installed SQL Server on a test machine and found
> that you can't just restore. The new SQL Server doesn't immediately
> recognize the backups from another computer.
> Does anyone know how to do this?|||Have a look at HOW TO: Move Databases Between Computers That Are Running SQL
Server
http://support.microsoft.com/defaul...kb;en-us;314546
"gl330k" wrote:

> We are running SQL Server 2000 on a single computer. We currently backup
> once a week into .bak files which we keep on file. However, if this compu
ter
> were to fail we would have to reinstall software on a new machine. How ca
n
> we restore the old database files from the backups? I have installed SQL
> Server on a test machine and found that you can't just restore. The new S
QL
> Server doesn't immediately recognize the backups from another computer.
> Does anyone know how to do this?|||I don't know much about SQL Server seeing as we only have one machine runnin
g
it and it is the foundation for a Solomon accounting program. I tried the
article you mentioned but it really didn't help me. The problem is that I
don't know where/what the "with restore" options are.
Here's what I just tried:
I have a database backup called woh.bak. I created a woh database in new
SQL Server 2000 (on the new machine). Then I backed it up as the name above
.
I copied the original backup over the new woh.bak and restored it from
there. Apparently there is data but I don't know how to verify yet if the
data is all accurate. (IOW - i still need to install Solomon.)
Does this sound like a recipe for disaster to anyone?
"Ana Mihalj" wrote:
[vbcol=seagreen]
> Have a look at HOW TO: Move Databases Between Computers That Are Running S
QL
> Server
> http://support.microsoft.com/defaul...kb;en-us;314546
> "gl330k" wrote:
>|||I'd assume that if you have some data in the database after the restore,
you'll have it all. A restore will either fail or succeed - it won't just
restore a part of the data. If you're not sure about it, I think the only
thing you can do is to look at the data from the accounting program and then
verify that it looks ok - I doubt that any of us can help you with
that...:-).
A good tip might be to read about BACKUP and RESTORE in Books On Line or the
links below. That might help you in the future.
BACKUP : http://msdn.microsoft.com/library/e..._ba-bz_35ww.asp
RESTORE: http://msdn.microsoft.com/library/e..._ra-rz_25rm.asp
Regards
Steen
gl330k wrote:[vbcol=seagreen]
> I don't know much about SQL Server seeing as we only have one machine
> running it and it is the foundation for a Solomon accounting program.
> I tried the article you mentioned but it really didn't help me. The
> problem is that I don't know where/what the "with restore" options
> are.
> Here's what I just tried:
> I have a database backup called woh.bak. I created a woh database in
> new SQL Server 2000 (on the new machine). Then I backed it up as the
> name above. I copied the original backup over the new woh.bak and
> restored it from there. Apparently there is data but I don't know
> how to verify yet if the data is all accurate. (IOW - i still need
> to install Solomon.)
> Does this sound like a recipe for disaster to anyone?
>
>
> "Ana Mihalj" wrote:
>

restore backup (.bak) on another computer

We are running SQL Server 2000 on a single computer. We currently backup
once a week into .bak files which we keep on file. However, if this computer
were to fail we would have to reinstall software on a new machine. How can
we restore the old database files from the backups? I have installed SQL
Server on a test machine and found that you can't just restore. The new SQL
Server doesn't immediately recognize the backups from another computer.
Does anyone know how to do this?
How do you try to do the restore and what is it that doesn't work?
Have you tried looking up the RESTORE command in BOL? In QA you can run :
RESTORE DATABASE YourDataBase FROM DISK =
'\\YourBackupLocation\YourBackupFile.BAK' WITH
RECOVERY, MOVE 'LogicalDataFileName' TO 'YourDatabasePath',
MOVE 'LogicalLogFileName' TO 'YourLogFilePath'
You can also do it from EM - right click on Databases ->All Task-> Restore
Database.
Regards
Steen
gl330k wrote:
> We are running SQL Server 2000 on a single computer. We currently
> backup once a week into .bak files which we keep on file. However,
> if this computer were to fail we would have to reinstall software on
> a new machine. How can we restore the old database files from the
> backups? I have installed SQL Server on a test machine and found
> that you can't just restore. The new SQL Server doesn't immediately
> recognize the backups from another computer.
> Does anyone know how to do this?
|||Have a look at HOW TO: Move Databases Between Computers That Are Running SQL
Server
http://support.microsoft.com/default...b;en-us;314546
"gl330k" wrote:

> We are running SQL Server 2000 on a single computer. We currently backup
> once a week into .bak files which we keep on file. However, if this computer
> were to fail we would have to reinstall software on a new machine. How can
> we restore the old database files from the backups? I have installed SQL
> Server on a test machine and found that you can't just restore. The new SQL
> Server doesn't immediately recognize the backups from another computer.
> Does anyone know how to do this?
|||I don't know much about SQL Server seeing as we only have one machine running
it and it is the foundation for a Solomon accounting program. I tried the
article you mentioned but it really didn't help me. The problem is that I
don't know where/what the "with restore" options are.
Here's what I just tried:
I have a database backup called woh.bak. I created a woh database in new
SQL Server 2000 (on the new machine). Then I backed it up as the name above.
I copied the original backup over the new woh.bak and restored it from
there. Apparently there is data but I don't know how to verify yet if the
data is all accurate. (IOW - i still need to install Solomon.)
Does this sound like a recipe for disaster to anyone?
"Ana Mihalj" wrote:
[vbcol=seagreen]
> Have a look at HOW TO: Move Databases Between Computers That Are Running SQL
> Server
> http://support.microsoft.com/default...b;en-us;314546
> "gl330k" wrote:
|||I'd assume that if you have some data in the database after the restore,
you'll have it all. A restore will either fail or succeed - it won't just
restore a part of the data. If you're not sure about it, I think the only
thing you can do is to look at the data from the accounting program and then
verify that it looks ok - I doubt that any of us can help you with
that...:-).
A good tip might be to read about BACKUP and RESTORE in Books On Line or the
links below. That might help you in the future.
BACKUP : http://msdn.microsoft.com/library/en...ba-bz_35ww.asp
RESTORE: http://msdn.microsoft.com/library/en...ra-rz_25rm.asp
Regards
Steen
gl330k wrote:[vbcol=seagreen]
> I don't know much about SQL Server seeing as we only have one machine
> running it and it is the foundation for a Solomon accounting program.
> I tried the article you mentioned but it really didn't help me. The
> problem is that I don't know where/what the "with restore" options
> are.
> Here's what I just tried:
> I have a database backup called woh.bak. I created a woh database in
> new SQL Server 2000 (on the new machine). Then I backed it up as the
> name above. I copied the original backup over the new woh.bak and
> restored it from there. Apparently there is data but I don't know
> how to verify yet if the data is all accurate. (IOW - i still need
> to install Solomon.)
> Does this sound like a recipe for disaster to anyone?
>
>
> "Ana Mihalj" wrote:

restore backup (.bak) on another computer

We are running SQL Server 2000 on a single computer. We currently backup
once a week into .bak files which we keep on file. However, if this computer
were to fail we would have to reinstall software on a new machine. How can
we restore the old database files from the backups? I have installed SQL
Server on a test machine and found that you can't just restore. The new SQL
Server doesn't immediately recognize the backups from another computer.
Does anyone know how to do this?How do you try to do the restore and what is it that doesn't work?
Have you tried looking up the RESTORE command in BOL? In QA you can run :
RESTORE DATABASE YourDataBase FROM DISK ='\\YourBackupLocation\YourBackupFile.BAK' WITH
RECOVERY, MOVE 'LogicalDataFileName' TO 'YourDatabasePath',
MOVE 'LogicalLogFileName' TO 'YourLogFilePath'
You can also do it from EM - right click on Databases ->All Task-> Restore
Database.
Regards
Steen
gl330k wrote:
> We are running SQL Server 2000 on a single computer. We currently
> backup once a week into .bak files which we keep on file. However,
> if this computer were to fail we would have to reinstall software on
> a new machine. How can we restore the old database files from the
> backups? I have installed SQL Server on a test machine and found
> that you can't just restore. The new SQL Server doesn't immediately
> recognize the backups from another computer.
> Does anyone know how to do this?|||Have a look at HOW TO: Move Databases Between Computers That Are Running SQL
Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
"gl330k" wrote:
> We are running SQL Server 2000 on a single computer. We currently backup
> once a week into .bak files which we keep on file. However, if this computer
> were to fail we would have to reinstall software on a new machine. How can
> we restore the old database files from the backups? I have installed SQL
> Server on a test machine and found that you can't just restore. The new SQL
> Server doesn't immediately recognize the backups from another computer.
> Does anyone know how to do this?|||I don't know much about SQL Server seeing as we only have one machine running
it and it is the foundation for a Solomon accounting program. I tried the
article you mentioned but it really didn't help me. The problem is that I
don't know where/what the "with restore" options are.
Here's what I just tried:
I have a database backup called woh.bak. I created a woh database in new
SQL Server 2000 (on the new machine). Then I backed it up as the name above.
I copied the original backup over the new woh.bak and restored it from
there. Apparently there is data but I don't know how to verify yet if the
data is all accurate. (IOW - i still need to install Solomon.)
Does this sound like a recipe for disaster to anyone?
"Ana Mihalj" wrote:
> Have a look at HOW TO: Move Databases Between Computers That Are Running SQL
> Server
> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
> "gl330k" wrote:
> > We are running SQL Server 2000 on a single computer. We currently backup
> > once a week into .bak files which we keep on file. However, if this computer
> > were to fail we would have to reinstall software on a new machine. How can
> > we restore the old database files from the backups? I have installed SQL
> > Server on a test machine and found that you can't just restore. The new SQL
> > Server doesn't immediately recognize the backups from another computer.
> >
> > Does anyone know how to do this?|||I'd assume that if you have some data in the database after the restore,
you'll have it all. A restore will either fail or succeed - it won't just
restore a part of the data. If you're not sure about it, I think the only
thing you can do is to look at the data from the accounting program and then
verify that it looks ok - I doubt that any of us can help you with
that...:-).
A good tip might be to read about BACKUP and RESTORE in Books On Line or the
links below. That might help you in the future.
BACKUP : http://msdn.microsoft.com/library/en-us/tsqlref/ts_ba-bz_35ww.asp
RESTORE: http://msdn.microsoft.com/library/en-us/tsqlref/ts_ra-rz_25rm.asp
Regards
Steen
gl330k wrote:
> I don't know much about SQL Server seeing as we only have one machine
> running it and it is the foundation for a Solomon accounting program.
> I tried the article you mentioned but it really didn't help me. The
> problem is that I don't know where/what the "with restore" options
> are.
> Here's what I just tried:
> I have a database backup called woh.bak. I created a woh database in
> new SQL Server 2000 (on the new machine). Then I backed it up as the
> name above. I copied the original backup over the new woh.bak and
> restored it from there. Apparently there is data but I don't know
> how to verify yet if the data is all accurate. (IOW - i still need
> to install Solomon.)
> Does this sound like a recipe for disaster to anyone?
>
>
> "Ana Mihalj" wrote:
>> Have a look at HOW TO: Move Databases Between Computers That Are
>> Running SQL Server
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
>> "gl330k" wrote:
>> We are running SQL Server 2000 on a single computer. We currently
>> backup once a week into .bak files which we keep on file. However,
>> if this computer were to fail we would have to reinstall software
>> on a new machine. How can we restore the old database files from
>> the backups? I have installed SQL Server on a test machine and
>> found that you can't just restore. The new SQL Server doesn't
>> immediately recognize the backups from another computer.
>> Does anyone know how to do this?

Restore a single table

I wanna restore a single table (example material) that I created on a
filegroup (example, sales). I have four filegroups.
I did a filegroup backup, but I don't want restore all filegroups, I want
restore only sales filegroup because it has my table.
I used :
CREATE TABLE material (id int) ON sales
BACKUP DATABASE Teste
FILE = 'TesteData1',
FILEGROUP = 'x',
FILE = 'TesteData2',
FILEGROUP = 'sales'
..
TO Teste1F
with init
GO
backup log Teste to Teste1L with noinit
GO
could I use this command :
restore DATABASE Teste
FILE = 'TesteData2',
FILEGROUP = 'Sales'
from Teste1F
with norecovery
GO
restore LOG teste from Teste1L
WITH recovery, file=1
But I used but this is not good for me.. the table disappear
thanks a lotPartial Database Restore Operations
http://msdn.microsoft.com/library/d... />
t_71f7.asp
AMB
"M?nica" wrote:

> I wanna restore a single table (example material) that I created on a
> filegroup (example, sales). I have four filegroups.
> I did a filegroup backup, but I don't want restore all filegroups, I want
> restore only sales filegroup because it has my table.
> I used :
> CREATE TABLE material (id int) ON sales
> BACKUP DATABASE Teste
> FILE = 'TesteData1',
> FILEGROUP = 'x',
> FILE = 'TesteData2',
> FILEGROUP = 'sales'
> ...
> TO Teste1F
> with init
> GO
> backup log Teste to Teste1L with noinit
> GO
> could I use this command :
> restore DATABASE Teste
> FILE = 'TesteData2',
> FILEGROUP = 'Sales'
> from Teste1F
> with norecovery
> GO
> restore LOG teste from Teste1L
> WITH recovery, file=1
> But I used but this is not good for me.. the table disappear
> thanks a lot

Restore a single table

I have online and offline backup of my SQL Server 2000 databases.
I want to restore a single table from this backups .
I cant restore as my full db with a new name because I dont have enough room
for it.
What's your offers?Hi
Since it was a SQL Server 6.5 feature , you cannot restore a single table in
SQL Server 7/2000
Do you have another server to restore the database?
"Banu_tr" <abuslu@.hotmail.com> wrote in message
news:4C761E38-1E6A-421A-A782-C66D07B3E2AE@.microsoft.com...
> I have online and offline backup of my SQL Server 2000 databases.
> I want to restore a single table from this backups .
> I cant restore as my full db with a new name because I dont have enough
room
> for it.
> What's your offers?|||SQL Server 2000 don't have a feature that allows you to restore a single
table.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Banu_tr" <abuslu@.hotmail.com> wrote in message
news:4C761E38-1E6A-421A-A782-C66D07B3E2AE@.microsoft.com...
> I have online and offline backup of my SQL Server 2000 databases.
> I want to restore a single table from this backups .
> I cant restore as my full db with a new name because I dont have enough
room
> for it.
> What's your offers?

Restore a single table

Hi,

I have a problem. For some reason I needed to restore a single (large) table that shares a filegroup with other tables. I have a full backup of the database. How can I restore my particular table?

-- Srinivas

Unfortunately this is not possible with builtin functions. There are onyl a few options. Restore the whole database to a new destination and copy the table to the final destination where you want to restore it. Use a third party tool which is able to extract only a single table from a backup media.

HTH, Jens Suessmeyer.

|||Thank you, Jens Suessmeyer.

Restore a single table

I wanna restore a single table (example material) that I created on a
filegroup (example, sales). I have four filegroups.
I did a filegroup backup, but I don't want restore all filegroups, I want
restore only sales filegroup because it has my table.
I used :
CREATE TABLE material (id int) ON sales
BACKUP DATABASE Teste
FILE = 'TesteData1',
FILEGROUP = 'x',
FILE = 'TesteData2',
FILEGROUP = 'sales'
...
TO Teste1F
with init
GO
backup log Teste to Teste1L with noinit
GO
could I use this command :
restore DATABASE Teste
FILE = 'TesteData2',
FILEGROUP = 'Sales'
from Teste1F
with norecovery
GO
restore LOG teste from Teste1L
WITH recovery, file=1
But I used but this is not good for me.. the table disappear
thanks a lot
Partial Database Restore Operations
http://msdn.microsoft.com/library/de...kprst_71f7.asp
AMB
"M?nica" wrote:

> I wanna restore a single table (example material) that I created on a
> filegroup (example, sales). I have four filegroups.
> I did a filegroup backup, but I don't want restore all filegroups, I want
> restore only sales filegroup because it has my table.
> I used :
> CREATE TABLE material (id int) ON sales
> BACKUP DATABASE Teste
> FILE = 'TesteData1',
> FILEGROUP = 'x',
> FILE = 'TesteData2',
> FILEGROUP = 'sales'
> ...
> TO Teste1F
> with init
> GO
> backup log Teste to Teste1L with noinit
> GO
> could I use this command :
> restore DATABASE Teste
> FILE = 'TesteData2',
> FILEGROUP = 'Sales'
> from Teste1F
> with norecovery
> GO
> restore LOG teste from Teste1L
> WITH recovery, file=1
> But I used but this is not good for me.. the table disappear
> thanks a lot

Restore a single table

I have online and offline backup of my SQL Server 2000 databases.
I want to restore a single table from this backups .
I cant restore as my full db with a new name because I dont have enough room
for it.
What's your offers?
Hi
Since it was a SQL Server 6.5 feature , you cannot restore a single table in
SQL Server 7/2000
Do you have another server to restore the database?
"Banu_tr" <abuslu@.hotmail.com> wrote in message
news:4C761E38-1E6A-421A-A782-C66D07B3E2AE@.microsoft.com...
> I have online and offline backup of my SQL Server 2000 databases.
> I want to restore a single table from this backups .
> I cant restore as my full db with a new name because I dont have enough
room
> for it.
> What's your offers?
|||SQL Server 2000 don't have a feature that allows you to restore a single
table.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Banu_tr" <abuslu@.hotmail.com> wrote in message
news:4C761E38-1E6A-421A-A782-C66D07B3E2AE@.microsoft.com...
> I have online and offline backup of my SQL Server 2000 databases.
> I want to restore a single table from this backups .
> I cant restore as my full db with a new name because I dont have enough
room
> for it.
> What's your offers?

Restore a single table

I have online and offline backup of my SQL Server 2000 databases.
I want to restore a single table from this backups .
I cant restore as my full db with a new name because I dont have enough room
for it.
What's your offers?Hi
Since it was a SQL Server 6.5 feature , you cannot restore a single table in
SQL Server 7/2000
Do you have another server to restore the database?
"Banu_tr" <abuslu@.hotmail.com> wrote in message
news:4C761E38-1E6A-421A-A782-C66D07B3E2AE@.microsoft.com...
> I have online and offline backup of my SQL Server 2000 databases.
> I want to restore a single table from this backups .
> I cant restore as my full db with a new name because I dont have enough
room
> for it.
> What's your offers?|||SQL Server 2000 don't have a feature that allows you to restore a single
table.
--
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Banu_tr" <abuslu@.hotmail.com> wrote in message
news:4C761E38-1E6A-421A-A782-C66D07B3E2AE@.microsoft.com...
> I have online and offline backup of my SQL Server 2000 databases.
> I want to restore a single table from this backups .
> I cant restore as my full db with a new name because I dont have enough
room
> for it.
> What's your offers?

Restore a single table

I wanna restore a single table (example material) that I created on a
filegroup (example, sales). I have four filegroups.
I did a filegroup backup, but I don't want restore all filegroups, I want
restore only sales filegroup because it has my table.
I used :
CREATE TABLE material (id int) ON sales
BACKUP DATABASE Teste
FILE = 'TesteData1',
FILEGROUP = 'x',
FILE = 'TesteData2',
FILEGROUP = 'sales'
...
TO Teste1F
with init
GO
backup log Teste to Teste1L with noinit
GO
could I use this command :
restore DATABASE Teste
FILE = 'TesteData2',
FILEGROUP = 'Sales'
from Teste1F
with norecovery
GO
restore LOG teste from Teste1L
WITH recovery, file=1
But I used but this is not good for me.. the table disappear
thanks a lotPartial Database Restore Operation
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_71f7.asp
AMB
"Mônica" wrote:
> I wanna restore a single table (example material) that I created on a
> filegroup (example, sales). I have four filegroups.
> I did a filegroup backup, but I don't want restore all filegroups, I want
> restore only sales filegroup because it has my table.
> I used :
> CREATE TABLE material (id int) ON sales
> BACKUP DATABASE Teste
> FILE = 'TesteData1',
> FILEGROUP = 'x',
> FILE = 'TesteData2',
> FILEGROUP = 'sales'
> ...
> TO Teste1F
> with init
> GO
> backup log Teste to Teste1L with noinit
> GO
> could I use this command :
> restore DATABASE Teste
> FILE = 'TesteData2',
> FILEGROUP = 'Sales'
> from Teste1F
> with norecovery
> GO
> restore LOG teste from Teste1L
> WITH recovery, file=1
> But I used but this is not good for me.. the table disappear
> thanks a lot

Restore a single file from the database

Hello,
We have running sql2000 and we want to use a database where users can store
files in it. My question is: is it possible to restore a single file from
that database?
Thanx in advance
Marco Gorissen
marco.gorissen@.icts.unimaas.nlHi,
You can use text data type with file name as a different column. Using this
you can retive a single file from the SQL table.
select textcolumn from table where filename='filename.txt'
Did I answer your question correctly ?
Thanks
Hari
MCDBA
"Marco Gorissen" <marco.gorissen@.icts.unimaas.nl> wrote in message
news:e0R3b2c1DHA.3216@.TK2MSFTNGP11.phx.gbl...
> Hello,
> We have running sql2000 and we want to use a database where users can
store
> files in it. My question is: is it possible to restore a single file from
> that database?
> Thanx in advance
> Marco Gorissen
> marco.gorissen@.icts.unimaas.nl
>

Restore a filegroup/file to a Point In Time (PIT)

Can i restore a single filegroup (or file) to a previous point in time (say 8am), but keep the rest of the database unchanged (say 10am).

Or, does the whole database have to be at the same point in time after a restore of a specific filegroup. Or is it that everything has to be the same PIT as the PRIMARY.

tia

I'm not positive if you can restore a file to a specific point in time, but the database can do it, look: http://msdn2.microsoft.com/en-us/library/ms186858.aspx

|||Have a look at PIECEMEAL restore in BOoks online 2005 and also KBA http://support.microsoft.com/kb/281122 fyi.

Restore a filegroup/file to a Point In Time (PIT)

Can i restore a single filegroup (or file) to a previous point in time (say 8am), but keep the rest of the database unchanged (say 10am).

Or, does the whole database have to be at the same point in time after a restore of a specific filegroup. Or is it that everything has to be the same PIT as the PRIMARY.

tia

I'm not positive if you can restore a file to a specific point in time, but the database can do it, look: http://msdn2.microsoft.com/en-us/library/ms186858.aspx

|||Have a look at PIECEMEAL restore in BOoks online 2005 and also KBA http://support.microsoft.com/kb/281122 fyi.

Restore a filegroup

Can i restore a single filegroup (or file) to a previous point in time (say 8am), but keep the rest of the database unchanged (say 10am).

Or, does the whole database have to be at the same point in time after a restore of a specific filegroup.

The entire database has to be at the same point in time after a restore (of a specific filegroup).

Thanks

Sherry

|||Thank you. From what i have found out, that is correct. Too bad about that though. I'm sure the product will improve in that area one day.

Restore a filegroup

Can i restore a single filegroup (or file) to a previous point in time (say 8am), but keep the rest of the database unchanged (say 10am).

Or, does the whole database have to be at the same point in time after a restore of a specific filegroup.

The entire database has to be at the same point in time after a restore (of a specific filegroup).

Thanks

Sherry

|||Thank you. From what i have found out, that is correct. Too bad about that though. I'm sure the product will improve in that area one day.