Restore dbase but leave 1 table the same

I have 2 servers and am migrating data occasionally from one server to the
other. There are several tables but one of the tables I do not want to be
updated by the restore. Just wondering if there is an easy way to do this?
Also the destination server has an additional stored procedure so would like
this procedure to be retained with the dbase restore.
thanks.
--
Paul G
Software engineer.There are many potential approaches, the choice of which depends on factors
such as the size of the database, the size of the table in question, etc.
If the table is smallish, you can preserve a copy in a different database
before the restore. After the database restore, repopulate the table back
from the saved copy. Same can be done to the stored procedure.
Linchi
"Paul" wrote:

> I have 2 servers and am migrating data occasionally from one server to the
> other. There are several tables but one of the tables I do not want to be
> updated by the restore. Just wondering if there is an easy way to do this
?
> Also the destination server has an additional stored procedure so would li
ke
> this procedure to be retained with the dbase restore.
> thanks.
> --
> Paul G
> Software engineer.|||thanks for the response. I forgot to mention that the schema is a bit
different between the two tables, destination table has 2 additional columns
.
I have used the create script file for stored procedures and tables out of
the server explorer in .net. Guess there is no way to script to file a tabl
e
and the data that is in it.
--
Paul G
Software engineer.
"Linchi Shea" wrote:
[vbcol=seagreen]
> There are many potential approaches, the choice of which depends on factor
s
> such as the size of the database, the size of the table in question, etc.
> If the table is smallish, you can preserve a copy in a different database
> before the restore. After the database restore, repopulate the table back
> from the saved copy. Same can be done to the stored procedure.
> Linchi
> "Paul" wrote:
>|||> Guess there is no way to script to file a table
> and the data that is in it.
You could always use DMO/SMO to script the table and bcp to export the data.
Linchi
"Paul" wrote:
[vbcol=seagreen]
> thanks for the response. I forgot to mention that the schema is a bit
> different between the two tables, destination table has 2 additional colum
ns.
> I have used the create script file for stored procedures and tables out o
f
> the server explorer in .net. Guess there is no way to script to file a ta
ble
> and the data that is in it.
> --
> Paul G
> Software engineer.
>
> "Linchi Shea" wrote:
>|||use DTS...
Jay
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:8A97BB5B-3F3E-419E-917F-19B16A050400@.microsoft.com...
>I have 2 servers and am migrating data occasionally from one server to the
> other. There are several tables but one of the tables I do not want to be
> updated by the restore. Just wondering if there is an easy way to do
> this?
> Also the destination server has an additional stored procedure so would
> like
> this procedure to be retained with the dbase restore.
> thanks.
> --
> Paul G
> Software engineer.

Restore dbase but leave 1 table the same

I have 2 servers and am migrating data occasionally from one server to the
other. There are several tables but one of the tables I do not want to be
updated by the restore. Just wondering if there is an easy way to do this?
Also the destination server has an additional stored procedure so would like
this procedure to be retained with the dbase restore.
thanks.
--
Paul G
Software engineer.There are many potential approaches, the choice of which depends on factors
such as the size of the database, the size of the table in question, etc.
If the table is smallish, you can preserve a copy in a different database
before the restore. After the database restore, repopulate the table back
from the saved copy. Same can be done to the stored procedure.
Linchi
"Paul" wrote:
> I have 2 servers and am migrating data occasionally from one server to the
> other. There are several tables but one of the tables I do not want to be
> updated by the restore. Just wondering if there is an easy way to do this?
> Also the destination server has an additional stored procedure so would like
> this procedure to be retained with the dbase restore.
> thanks.
> --
> Paul G
> Software engineer.|||thanks for the response. I forgot to mention that the schema is a bit
different between the two tables, destination table has 2 additional columns.
I have used the create script file for stored procedures and tables out of
the server explorer in .net. Guess there is no way to script to file a table
and the data that is in it.
--
Paul G
Software engineer.
"Linchi Shea" wrote:
> There are many potential approaches, the choice of which depends on factors
> such as the size of the database, the size of the table in question, etc.
> If the table is smallish, you can preserve a copy in a different database
> before the restore. After the database restore, repopulate the table back
> from the saved copy. Same can be done to the stored procedure.
> Linchi
> "Paul" wrote:
> > I have 2 servers and am migrating data occasionally from one server to the
> > other. There are several tables but one of the tables I do not want to be
> > updated by the restore. Just wondering if there is an easy way to do this?
> > Also the destination server has an additional stored procedure so would like
> > this procedure to be retained with the dbase restore.
> > thanks.
> > --
> > Paul G
> > Software engineer.|||> Guess there is no way to script to file a table
> and the data that is in it.
You could always use DMO/SMO to script the table and bcp to export the data.
Linchi
"Paul" wrote:
> thanks for the response. I forgot to mention that the schema is a bit
> different between the two tables, destination table has 2 additional columns.
> I have used the create script file for stored procedures and tables out of
> the server explorer in .net. Guess there is no way to script to file a table
> and the data that is in it.
> --
> Paul G
> Software engineer.
>
> "Linchi Shea" wrote:
> > There are many potential approaches, the choice of which depends on factors
> > such as the size of the database, the size of the table in question, etc.
> >
> > If the table is smallish, you can preserve a copy in a different database
> > before the restore. After the database restore, repopulate the table back
> > from the saved copy. Same can be done to the stored procedure.
> >
> > Linchi
> >
> > "Paul" wrote:
> >
> > > I have 2 servers and am migrating data occasionally from one server to the
> > > other. There are several tables but one of the tables I do not want to be
> > > updated by the restore. Just wondering if there is an easy way to do this?
> > > Also the destination server has an additional stored procedure so would like
> > > this procedure to be retained with the dbase restore.
> > > thanks.
> > > --
> > > Paul G
> > > Software engineer.|||use DTS...
Jay
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:8A97BB5B-3F3E-419E-917F-19B16A050400@.microsoft.com...
>I have 2 servers and am migrating data occasionally from one server to the
> other. There are several tables but one of the tables I do not want to be
> updated by the restore. Just wondering if there is an easy way to do
> this?
> Also the destination server has an additional stored procedure so would
> like
> this procedure to be retained with the dbase restore.
> thanks.
> --
> Paul G
> Software engineer.

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 databse SQL7 to SQL2000

Hi
I am trying to restore a SQL7 database to an SQL2000 server. The
restore only manages to restore the tables. It does not restore the
data. What am I doing wong ? Any help will be appreciated.RESTORE does give you an image of the database. Perhaps you by mistake restored the wrong backup?
Can you do SELECT COUNT(*) FROM tblname on some of the table to determine the number of rows in
them?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Arian" <arian@.rocketmail.com> wrote in message
news:1108737187.461333.17340@.l41g2000cwc.googlegroups.com...
> Hi
> I am trying to restore a SQL7 database to an SQL2000 server. The
> restore only manages to restore the tables. It does not restore the
> data. What am I doing wong ? Any help will be appreciated.
>|||I got the backup from a client. Since I only have one backup that
could'nt be the problem.
On count I get 0 on most of the tables.
The database size was 798mb on the sql 7 server and the backup is 109
mb and the restored db is 798. So the size of the db matches.|||Hi Tibor
Managed to sort it. I did the restore thru query analyzer with the
following syntax and it worked fine.
RESTORE DATABASE mydb FROM disk='d:\mssql7\backup\mydb_db.bak'
WITH MOVE 'mydbdata' TO 'd:\mssql7\data\mydbdata.mdf',
MOVE 'mydblog' TO 'd:\mssql7\data\mydblog.ldf',
REPLACE
go

Restore databse SQL7 to SQL2000

Hi
I am trying to restore a SQL7 database to an SQL2000 server. The
restore only manages to restore the tables. It does not restore the
data. What am I doing wong ? Any help will be appreciated.
RESTORE does give you an image of the database. Perhaps you by mistake restored the wrong backup?
Can you do SELECT COUNT(*) FROM tblname on some of the table to determine the number of rows in
them?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Arian" <arian@.rocketmail.com> wrote in message
news:1108737187.461333.17340@.l41g2000cwc.googlegro ups.com...
> Hi
> I am trying to restore a SQL7 database to an SQL2000 server. The
> restore only manages to restore the tables. It does not restore the
> data. What am I doing wong ? Any help will be appreciated.
>
|||I got the backup from a client. Since I only have one backup that
could'nt be the problem.
On count I get 0 on most of the tables.
The database size was 798mb on the sql 7 server and the backup is 109
mb and the restored db is 798. So the size of the db matches.
|||Hi Tibor
Managed to sort it. I did the restore thru query analyzer with the
following syntax and it worked fine.
RESTORE DATABASE mydb FROM disk='d:\mssql7\backup\mydb_db.bak'
WITH MOVE 'mydbdata' TO 'd:\mssql7\data\mydbdata.mdf',
MOVE 'mydblog' TO 'd:\mssql7\data\mydblog.ldf',
REPLACE
go

Restore databse SQL7 to SQL2000

Hi
I am trying to restore a SQL7 database to an SQL2000 server. The
restore only manages to restore the tables. It does not restore the
data. What am I doing wong ? Any help will be appreciated.RESTORE does give you an image of the database. Perhaps you by mistake resto
red the wrong backup?
Can you do SELECT COUNT(*) FROM tblname on some of the table to determine th
e number of rows in
them?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Arian" <arian@.rocketmail.com> wrote in message
news:1108737187.461333.17340@.l41g2000cwc.googlegroups.com...
> Hi
> I am trying to restore a SQL7 database to an SQL2000 server. The
> restore only manages to restore the tables. It does not restore the
> data. What am I doing wong ? Any help will be appreciated.
>|||I got the backup from a client. Since I only have one backup that
could'nt be the problem.
On count I get 0 on most of the tables.
The database size was 798mb on the sql 7 server and the backup is 109
mb and the restored db is 798. So the size of the db matches.|||Hi Tibor
Managed to sort it. I did the restore thru query analyzer with the
following syntax and it worked fine.
RESTORE DATABASE mydb FROM disk='d:\mssql7\backup\mydb_db.bak'
WITH MOVE 'mydbdata' TO 'd:\mssql7\data\mydbdata.mdf',
MOVE 'mydblog' TO 'd:\mssql7\data\mydblog.ldf',
REPLACE
go

restore database without some tables records

Hi There,
I'm using following script to restore
Restore Database adminsys_ex_avc from disk =
'\\ppml31\DBBkups\fromPPML0112\adminsys_
ex_avc.bak' with replace
is it possible can i ignore the some table on restore, for e.g audit_log
table and i don't want to use any logs
Thanks
GaneshActually yes
drop database test
go
create database test
GO
alter database test set recovery full
go
create table test..test(id int identity)
create table test..test1(id int identity)
insert test..test default values
insert test..test1 default values
backup database test to disk = 'c:\db.bak' WITH INIT
insert test..test default values
insert test..test1 default values
backup log test to disk = 'c:\log.bak'WITH INIT
insert test..test default values
insert test..test1 default values
backup log test to disk = 'c:\log.bak' WITH NOINIT
insert test..test default values
drop table test..test1
backup log test to disk = 'c:\log.bak' WITH NOINIT
GO
RESTORE DATABASE test FROM disk = 'c:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'c:\log.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'c:\log.bak' WITH FILE = 2, norecovery
RESTORE LOG test FROM disk = 'c:\log.bak' WITH FILE = 3, recovery
GO
select * from test..test
select * from test..test1
--Server: Msg 208, Level 16, State 1, Line 1
--Invalid object name 'test..test1'.
"Ganesh" <gsganesh@.yahoo.com> wrote in message
news:6D4A5AFB-50AD-4E93-889D-2169F92BBB82@.microsoft.com...
> Hi There,
> I'm using following script to restore
> Restore Database adminsys_ex_avc from disk =
> '\\ppml31\DBBkups\fromPPML0112\adminsys_
ex_avc.bak' with replace
> is it possible can i ignore the some table on restore, for e.g audit_log
> table and i don't want to use any logs
> --
> Thanks
> Ganesh|||No,
You can not avoid selected tables.
You can do one thing. Move all unrequired tables to one filegroup which
should not be primary.
Then use partial restore and do not restore selected filegroup.
Look at BOL Partial Database Restore Operations.
Best way is just restore database and delete unwanted tables.
Regards
Amish Shah.

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

I've backed-up a database with empty tables and then restored it to a new
database. Everything restored OK, apart from the tables, which are not
created. I'd expect it to create the tables, even though they are empty.
Am I doing something wrong?
Possibly ownership problems and orphaned users. Check out sp_change_users_login in Book sOnline.
Also check if the database owner is orphaned.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"HB" <HB@.discussions.microsoft.com> wrote in message
news:C98EAFC0-13F8-41F8-825D-1F25CA2D7E56@.microsoft.com...
> I've backed-up a database with empty tables and then restored it to a new
> database. Everything restored OK, apart from the tables, which are not
> created. I'd expect it to create the tables, even though they are empty.
> Am I doing something wrong?

Restore

I've backed-up a database with empty tables and then restored it to a new
database. Everything restored OK, apart from the tables, which are not
created. I'd expect it to create the tables, even though they are empty.
Am I doing something wrong?Possibly ownership problems and orphaned users. Check out sp_change_users_login in Book sOnline.
Also check if the database owner is orphaned.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"HB" <HB@.discussions.microsoft.com> wrote in message
news:C98EAFC0-13F8-41F8-825D-1F25CA2D7E56@.microsoft.com...
> I've backed-up a database with empty tables and then restored it to a new
> database. Everything restored OK, apart from the tables, which are not
> created. I'd expect it to create the tables, even though they are empty.
> Am I doing something wrong?