Restore two individual tables from massive DB into new DB

I need to pull two individual tables out of a very large DB into a new
DB for testing. I know this is probably easy for a more experienced
DBA, but I'm still learning.
Thanks.
Probably best to transfer them from one to the other using DTS (SQL 2000) or
SSIS (SQL 2005) package during off hours.
How many rows in the tables that you need to move?
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Matt" <matthewsatkins@.gmail.com> wrote in message
news:c19a7ff0-c2f2-452a-9841-853cd393c917@.l1g2000hsa.googlegroups.com...
>I need to pull two individual tables out of a very large DB into a new
> DB for testing. I know this is probably easy for a more experienced
> DBA, but I'm still learning.
> Thanks.

Restore two individual tables from massive DB into new DB

I need to pull two individual tables out of a very large DB into a new
DB for testing. I know this is probably easy for a more experienced
DBA, but I'm still learning.
Thanks.Probably best to transfer them from one to the other using DTS (SQL 2000) or
SSIS (SQL 2005) package during off hours.
How many rows in the tables that you need to move?
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Matt" <matthewsatkins@.gmail.com> wrote in message
news:c19a7ff0-c2f2-452a-9841-853cd393c917@.l1g2000hsa.googlegroups.com...
>I need to pull two individual tables out of a very large DB into a new
> DB for testing. I know this is probably easy for a more experienced
> DBA, but I'm still learning.
> Thanks.

Restore SQL 2000 Tables from Transaction Log Files

I inadvertently deleted related records from one table when I deleted anothe
r table. I am not sure how often the server admin backups the data or how h
e is doing it. I thought that I read somewhere that one could do that from
the log files. Can someone
please help me with this?
Thanks,Hi,
Steps to recover your table in the database, (Hope you are using FULL
Recovery Model)
1. Perform a transaction log backup in your current database
Backup log dbname to disk='drivename\txlog_final.bak'
2. Restore the FULL database backup into a new database
Restore database Newdbname from disk='physicaldrive\filename.bak' with
move 'logical_data_filename' to 'new_data_physicalfile.mdf',
move 'logical_log_filename' to 'new_log_physicalfile.ldf',
with NORECOVERY
3. Restore the subsequent TX log files in sequential order till the last
transaction log file in which log backup is taken
Restore log newdbname from disk='physicaldrive\txlogfile1.bak' with
norecovery
COntinue the step 3 for all transaction log files , but do not restore
the last transaction log file.
4. For the Last Transaction log file , use RECOVERY option
Restore log newdbname from disk='physicaldrive\txlog_final.bak' with
RECOVERY,STOPAT = 'Feb 26, 2004 10:00 AM'
This will revover the new database till 'Feb 26, 2004 10:00 AM'
Thanks
Hari
MCDBA
"Stephanie" <anonymous@.discussions.microsoft.com> wrote in message
news:77CEFC43-E1B9-454D-97BA-A4B32825D8E8@.microsoft.com...
> I inadvertently deleted related records from one table when I deleted
another table. I am not sure how often the server admin backups the data or
how he is doing it. I thought that I read somewhere that one could do that
from the log files. Can someone please help me with this?
> Thanks,|||Lumigent offers a tool called LogReader that is useful in finding what
happened.
Russell Fields
"Stephanie" <anonymous@.discussions.microsoft.com> wrote in message
news:77CEFC43-E1B9-454D-97BA-A4B32825D8E8@.microsoft.com...
> I inadvertently deleted related records from one table when I deleted
another table. I am not sure how often the server admin backups the data or
how he is doing it. I thought that I read somewhere that one could do that
from the log files. Can someone please help me with this?
> Thanks,

Restore SQL 2000 Tables from Transaction Log Files

I inadvertently deleted related records from one table when I deleted another table. I am not sure how often the server admin backups the data or how he is doing it. I thought that I read somewhere that one could do that from the log files. Can someone please help me with this
Thanks,Hi,
Steps to recover your table in the database, (Hope you are using FULL
Recovery Model)
1. Perform a transaction log backup in your current database
Backup log dbname to disk='drivename\txlog_final.bak'
2. Restore the FULL database backup into a new database
Restore database Newdbname from disk='physicaldrive\filename.bak' with
move 'logical_data_filename' to 'new_data_physicalfile.mdf',
move 'logical_log_filename' to 'new_log_physicalfile.ldf',
with NORECOVERY
3. Restore the subsequent TX log files in sequential order till the last
transaction log file in which log backup is taken
Restore log newdbname from disk='physicaldrive\txlogfile1.bak' with
norecovery
COntinue the step 3 for all transaction log files , but do not restore
the last transaction log file.
4. For the Last Transaction log file , use RECOVERY option
Restore log newdbname from disk='physicaldrive\txlog_final.bak' with
RECOVERY,STOPAT = 'Feb 26, 2004 10:00 AM'
This will revover the new database till 'Feb 26, 2004 10:00 AM'
Thanks
Hari
MCDBA
"Stephanie" <anonymous@.discussions.microsoft.com> wrote in message
news:77CEFC43-E1B9-454D-97BA-A4B32825D8E8@.microsoft.com...
> I inadvertently deleted related records from one table when I deleted
another table. I am not sure how often the server admin backups the data or
how he is doing it. I thought that I read somewhere that one could do that
from the log files. Can someone please help me with this?
> Thanks,|||Lumigent offers a tool called LogReader that is useful in finding what
happened.
Russell Fields
"Stephanie" <anonymous@.discussions.microsoft.com> wrote in message
news:77CEFC43-E1B9-454D-97BA-A4B32825D8E8@.microsoft.com...
> I inadvertently deleted related records from one table when I deleted
another table. I am not sure how often the server admin backups the data or
how he is doing it. I thought that I read somewhere that one could do that
from the log files. Can someone please help me with this?
> Thanks,

Restore selective tables?

HI,
Is it possible in SQL Server to restore Databases partially?
In the sense, I want to backup just 2 tables from a database & restore them, instead of having to take a backup of the entire database & restore the entire database.

One way to do would be to export data & then importing data for only those selective tables, but I dont want to do that, bcause that would probably take more time & can be error prone.

Thanks
Raman.I think this can be done by putting the tables in their own filegroup which can be backed up by itself.|||The short answer is no.

How would you do a partial RESTORE anyway?

Is the other database on the same box?

you could use bcp...|||You can create a seperate filegroup and create selective tables on that. Then individually backup that filegroup from rest of the database. Plus backup the transaction log.
When you need to restore those restore the filegroup backup and the transaction log. However, it is unlike any other db system and I would recommend doing a complete backup, restoring it to another server and then restore/transfer selected table|||BOL implies that you can add filegroups to an existing DB but cannot move tables from one filegroup to another. I guess you would have to export the data, drop the existing tables, create new tables on a new filegroup and then import the data to the new tables.

Backup in EM has an option for backing up files/filegroups doesn't it?

Restore ReportServer Database

Accidentally the key tables were deleted from our ReportServer SQL Database.

We tried copying the tables over from the ReportServer TempDB, but this then broke the encryption key (which we did not have access to the backup of).

We then ran rsconfig -c to restructure the db connection and generate a new key. But unfortunately our old data was encrypted by the old key.

We are also getting the following error on the http://localhost/Reports page:

The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is 'Unknown'. The expected version is 'C.0.6.51'. To continue, update the version of the report server database and verify access rights. (rsInvalidReportServerDatabase)

The recomended fix for this error is to reinstall Reporting Services from scratch.

We were hoping that there maybe another way around this, preferably away we can just restore from he TempDB.

Thanks for your attention.

The tempdb is not a copy of the main report server database. It contains a different set of data. The main database stores report defintitions, data sources, security settings, schedules, etc. The tempdb stores dynamic data , such as report snapshot that are current being viewed. You can't restore one from the other.

If you don't have a backup of the encryption key, you will need to delete all encrypted content in the database via the config tool. If you have lost your entire report server database (which sounds like it is the case) and don't have a backup, you will need to rebuild it from scratch.

Restore Question

I am trying the restore process.
Here is the test case that i am performing:
1) Full backup Nwind Databse at 5pm
2) Add some tables and after that did transaction log backup at 5.10pm
Try to initiate failure by renaming the .mdf.
Restored full with NORECOVERY followed by trans log with RECOVER. it worked.
My question is, after 5.10pm, i did changes without saving and renamed the
..mdf again. I wanted to try A Point in Time backup. After i renamed the .mdf,
i cant perform trans log backup using the BACKUP LOG statement(it gives error
that the database is not online. But, hey, i just renamed the .mdf and the
..ldy is perfectly alright). From my understanding, the changes,after 5.10pm,
are recorded in trans log and if i can save that, i can get all the data
back. So, how do i do that?Or did i overlook sth?
TIA
"How to restore to the point of failure (Transact-SQL)"
http://msdn.microsoft.com/library/en...asp?frame=true
Cristian Lefter, SQL Server MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:0F275721-28D6-4FB1-94BB-C407DD114D92@.microsoft.com...
>I am trying the restore process.
> Here is the test case that i am performing:
> 1) Full backup Nwind Databse at 5pm
> 2) Add some tables and after that did transaction log backup at 5.10pm
> Try to initiate failure by renaming the .mdf.
> Restored full with NORECOVERY followed by trans log with RECOVER. it
> worked.
> My question is, after 5.10pm, i did changes without saving and renamed the
> .mdf again. I wanted to try A Point in Time backup. After i renamed the
> .mdf,
> i cant perform trans log backup using the BACKUP LOG statement(it gives
> error
> that the database is not online. But, hey, i just renamed the .mdf and the
> .ldy is perfectly alright). From my understanding, the changes,after
> 5.10pm,
> are recorded in trans log and if i can save that, i can get all the data
> back. So, how do i do that?Or did i overlook sth?
> TIA
|||Rupart
You can only restore from a backup of the transaction log. You can not
restore direct from the transaction log.
In a real situation where you are going to have to either go to your DR
solution (If you have one) or do a restore, it's always a good idea to see if
you can perform a final transaction log backup before proceding. In your
case, renaming the .mdf, you won't be able to, but sometimes you can. This
will allow you to get as up to date as possible. In your scenario there is no
way for you to restore the database to any point after 5:10pm.
Regards
John
"Cristian Lefter" wrote:

> "How to restore to the point of failure (Transact-SQL)"
> http://msdn.microsoft.com/library/en...asp?frame=true
> Cristian Lefter, SQL Server MVP
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:0F275721-28D6-4FB1-94BB-C407DD114D92@.microsoft.com...
>
>
|||> After i renamed the .mdf,
> i cant perform trans log backup using the BACKUP LOG
This is what the NO_TRUNCATE option for the BAKUP LOG command is for. It will allow you do exactly
that. You might want to check out:
http://www.karaszi.com/SQLServer/inf...eral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:0F275721-28D6-4FB1-94BB-C407DD114D92@.microsoft.com...
>I am trying the restore process.
> Here is the test case that i am performing:
> 1) Full backup Nwind Databse at 5pm
> 2) Add some tables and after that did transaction log backup at 5.10pm
> Try to initiate failure by renaming the .mdf.
> Restored full with NORECOVERY followed by trans log with RECOVER. it worked.
> My question is, after 5.10pm, i did changes without saving and renamed the
> .mdf again. I wanted to try A Point in Time backup. After i renamed the .mdf,
> i cant perform trans log backup using the BACKUP LOG statement(it gives error
> that the database is not online. But, hey, i just renamed the .mdf and the
> .ldy is perfectly alright). From my understanding, the changes,after 5.10pm,
> are recorded in trans log and if i can save that, i can get all the data
> back. So, how do i do that?Or did i overlook sth?
> TIA
|||cool..
thx guys..let me check it out
"Cristian Lefter" wrote:

> "How to restore to the point of failure (Transact-SQL)"
> http://msdn.microsoft.com/library/en...asp?frame=true
> Cristian Lefter, SQL Server MVP
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:0F275721-28D6-4FB1-94BB-C407DD114D92@.microsoft.com...
>
>

Restore Question

I am trying the restore process.
Here is the test case that i am performing:
1) Full backup Nwind Databse at 5pm
2) Add some tables and after that did transaction log backup at 5.10pm
Try to initiate failure by renaming the .mdf.
Restored full with NORECOVERY followed by trans log with RECOVER. it worked.
My question is, after 5.10pm, i did changes without saving and renamed the
.mdf again. I wanted to try A Point in Time backup. After i renamed the .mdf,
i cant perform trans log backup using the BACKUP LOG statement(it gives error
that the database is not online. But, hey, i just renamed the .mdf and the
.ldy is perfectly alright). From my understanding, the changes,after 5.10pm,
are recorded in trans log and if i can save that, i can get all the data
back. So, how do i do that?Or did i overlook sth?
TIA"How to restore to the point of failure (Transact-SQL)"
http://msdn.microsoft.com/library/en-us/howtosql/ht_7_backpc_5a61.asp?frame=true
Cristian Lefter, SQL Server MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:0F275721-28D6-4FB1-94BB-C407DD114D92@.microsoft.com...
>I am trying the restore process.
> Here is the test case that i am performing:
> 1) Full backup Nwind Databse at 5pm
> 2) Add some tables and after that did transaction log backup at 5.10pm
> Try to initiate failure by renaming the .mdf.
> Restored full with NORECOVERY followed by trans log with RECOVER. it
> worked.
> My question is, after 5.10pm, i did changes without saving and renamed the
> .mdf again. I wanted to try A Point in Time backup. After i renamed the
> .mdf,
> i cant perform trans log backup using the BACKUP LOG statement(it gives
> error
> that the database is not online. But, hey, i just renamed the .mdf and the
> .ldy is perfectly alright). From my understanding, the changes,after
> 5.10pm,
> are recorded in trans log and if i can save that, i can get all the data
> back. So, how do i do that?Or did i overlook sth?
> TIA|||Rupart
You can only restore from a backup of the transaction log. You can not
restore direct from the transaction log.
In a real situation where you are going to have to either go to your DR
solution (If you have one) or do a restore, it's always a good idea to see if
you can perform a final transaction log backup before proceding. In your
case, renaming the .mdf, you won't be able to, but sometimes you can. This
will allow you to get as up to date as possible. In your scenario there is no
way for you to restore the database to any point after 5:10pm.
Regards
John
"Cristian Lefter" wrote:
> "How to restore to the point of failure (Transact-SQL)"
> http://msdn.microsoft.com/library/en-us/howtosql/ht_7_backpc_5a61.asp?frame=true
> Cristian Lefter, SQL Server MVP
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:0F275721-28D6-4FB1-94BB-C407DD114D92@.microsoft.com...
> >I am trying the restore process.
> > Here is the test case that i am performing:
> > 1) Full backup Nwind Databse at 5pm
> > 2) Add some tables and after that did transaction log backup at 5.10pm
> > Try to initiate failure by renaming the .mdf.
> > Restored full with NORECOVERY followed by trans log with RECOVER. it
> > worked.
> >
> > My question is, after 5.10pm, i did changes without saving and renamed the
> > .mdf again. I wanted to try A Point in Time backup. After i renamed the
> > .mdf,
> > i cant perform trans log backup using the BACKUP LOG statement(it gives
> > error
> > that the database is not online. But, hey, i just renamed the .mdf and the
> > .ldy is perfectly alright). From my understanding, the changes,after
> > 5.10pm,
> > are recorded in trans log and if i can save that, i can get all the data
> > back. So, how do i do that?Or did i overlook sth?
> >
> > TIA
>
>|||> After i renamed the .mdf,
> i cant perform trans log backup using the BACKUP LOG
This is what the NO_TRUNCATE option for the BAKUP LOG command is for. It will allow you do exactly
that. You might want to check out:
http://www.karaszi.com/SQLServer/info_restore_log_several_times.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:0F275721-28D6-4FB1-94BB-C407DD114D92@.microsoft.com...
>I am trying the restore process.
> Here is the test case that i am performing:
> 1) Full backup Nwind Databse at 5pm
> 2) Add some tables and after that did transaction log backup at 5.10pm
> Try to initiate failure by renaming the .mdf.
> Restored full with NORECOVERY followed by trans log with RECOVER. it worked.
> My question is, after 5.10pm, i did changes without saving and renamed the
> .mdf again. I wanted to try A Point in Time backup. After i renamed the .mdf,
> i cant perform trans log backup using the BACKUP LOG statement(it gives error
> that the database is not online. But, hey, i just renamed the .mdf and the
> .ldy is perfectly alright). From my understanding, the changes,after 5.10pm,
> are recorded in trans log and if i can save that, i can get all the data
> back. So, how do i do that?Or did i overlook sth?
> TIA|||cool..
thx guys..let me check it out
"Cristian Lefter" wrote:
> "How to restore to the point of failure (Transact-SQL)"
> http://msdn.microsoft.com/library/en-us/howtosql/ht_7_backpc_5a61.asp?frame=true
> Cristian Lefter, SQL Server MVP
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:0F275721-28D6-4FB1-94BB-C407DD114D92@.microsoft.com...
> >I am trying the restore process.
> > Here is the test case that i am performing:
> > 1) Full backup Nwind Databse at 5pm
> > 2) Add some tables and after that did transaction log backup at 5.10pm
> > Try to initiate failure by renaming the .mdf.
> > Restored full with NORECOVERY followed by trans log with RECOVER. it
> > worked.
> >
> > My question is, after 5.10pm, i did changes without saving and renamed the
> > .mdf again. I wanted to try A Point in Time backup. After i renamed the
> > .mdf,
> > i cant perform trans log backup using the BACKUP LOG statement(it gives
> > error
> > that the database is not online. But, hey, i just renamed the .mdf and the
> > .ldy is perfectly alright). From my understanding, the changes,after
> > 5.10pm,
> > are recorded in trans log and if i can save that, i can get all the data
> > back. So, how do i do that?Or did i overlook sth?
> >
> > TIA
>
>

Restore Question

I am trying the restore process.
Here is the test case that i am performing:
1) Full backup Nwind Databse at 5pm
2) Add some tables and after that did transaction log backup at 5.10pm
Try to initiate failure by renaming the .mdf.
Restored full with NORECOVERY followed by trans log with RECOVER. it worked.
My question is, after 5.10pm, i did changes without saving and renamed the
.mdf again. I wanted to try A Point in Time backup. After i renamed the .md
f,
i cant perform trans log backup using the BACKUP LOG statement(it gives erro
r
that the database is not online. But, hey, i just renamed the .mdf and the
.ldy is perfectly alright). From my understanding, the changes,after 5.10pm
,
are recorded in trans log and if i can save that, i can get all the data
back. So, how do i do that?Or did i overlook sth?
TIA"How to restore to the point of failure (Transact-SQL)"
[url]http://msdn.microsoft.com/library/en-us/howtosql/ht_7_backpc_5a61.asp?frame=true[/
url]
Cristian Lefter, SQL Server MVP
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:0F275721-28D6-4FB1-94BB-C407DD114D92@.microsoft.com...
>I am trying the restore process.
> Here is the test case that i am performing:
> 1) Full backup Nwind Databse at 5pm
> 2) Add some tables and after that did transaction log backup at 5.10pm
> Try to initiate failure by renaming the .mdf.
> Restored full with NORECOVERY followed by trans log with RECOVER. it
> worked.
> My question is, after 5.10pm, i did changes without saving and renamed the
> .mdf again. I wanted to try A Point in Time backup. After i renamed the
> .mdf,
> i cant perform trans log backup using the BACKUP LOG statement(it gives
> error
> that the database is not online. But, hey, i just renamed the .mdf and the
> .ldy is perfectly alright). From my understanding, the changes,after
> 5.10pm,
> are recorded in trans log and if i can save that, i can get all the data
> back. So, how do i do that?Or did i overlook sth?
> TIA|||Rupart
You can only restore from a backup of the transaction log. You can not
restore direct from the transaction log.
In a real situation where you are going to have to either go to your DR
solution (If you have one) or do a restore, it's always a good idea to see i
f
you can perform a final transaction log backup before proceding. In your
case, renaming the .mdf, you won't be able to, but sometimes you can. This
will allow you to get as up to date as possible. In your scenario there is n
o
way for you to restore the database to any point after 5:10pm.
Regards
John
"Cristian Lefter" wrote:

> "How to restore to the point of failure (Transact-SQL)"
> http://msdn.microsoft.com/library/e...me=true

> Cristian Lefter, SQL Server MVP
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:0F275721-28D6-4FB1-94BB-C407DD114D92@.microsoft.com...
>
>|||> After i renamed the .mdf,
> i cant perform trans log backup using the BACKUP LOG
This is what the NO_TRUNCATE option for the BAKUP LOG command is for. It wil
l allow you do exactly
that. You might want to check out:
http://www.karaszi.com/SQLServer/in...veral_times.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"rupart" <rupart@.discussions.microsoft.com> wrote in message
news:0F275721-28D6-4FB1-94BB-C407DD114D92@.microsoft.com...
>I am trying the restore process.
> Here is the test case that i am performing:
> 1) Full backup Nwind Databse at 5pm
> 2) Add some tables and after that did transaction log backup at 5.10pm
> Try to initiate failure by renaming the .mdf.
> Restored full with NORECOVERY followed by trans log with RECOVER. it worke
d.
> My question is, after 5.10pm, i did changes without saving and renamed the
> .mdf again. I wanted to try A Point in Time backup. After i renamed the .m
df,
> i cant perform trans log backup using the BACKUP LOG statement(it gives er
ror
> that the database is not online. But, hey, i just renamed the .mdf and the
> .ldy is perfectly alright). From my understanding, the changes,after 5.10p
m,
> are recorded in trans log and if i can save that, i can get all the data
> back. So, how do i do that?Or did i overlook sth?
> TIA|||cool..
thx guys..let me check it out
"Cristian Lefter" wrote:

> "How to restore to the point of failure (Transact-SQL)"
> http://msdn.microsoft.com/library/e...me=true

> Cristian Lefter, SQL Server MVP
> "rupart" <rupart@.discussions.microsoft.com> wrote in message
> news:0F275721-28D6-4FB1-94BB-C407DD114D92@.microsoft.com...
>
>

Restore puts copy of user tables in master

Environment is SQL Server 2000 64 bit.
I restore from a script 'my' database, this works fine. However, all
the tables are also found in master, no data though.
Anyone experienced this?"Emille378" <dishonty@.seidata.com> wrote in message
news:d25b2692.0405050650.854b85c@.posting.google.co m...
> Environment is SQL Server 2000 64 bit.
> I restore from a script 'my' database, this works fine. However, all
> the tables are also found in master, no data though.
> Anyone experienced this?

Can you show us the script?

Restore puts copy of user tables in master

Environment is SQL Server 2000 64 bit.
I restore from a script 'my' database, this works fine. However, all
the tables are also found in master, no data though.
Anyone experienced this?"Emille378" <dishonty@.seidata.com> wrote in message
news:d25b2692.0405050650.854b85c@.posting.google.co m...
> Environment is SQL Server 2000 64 bit.
> I restore from a script 'my' database, this works fine. However, all
> the tables are also found in master, no data though.
> Anyone experienced this?

Can you show us the script?|||"Greg D. Moore \(Strider\)" <mooregr_deleteth1s@.greenms.com> wrote in message news:<%fgmc.160405$M3.149305@.twister.nyroc.rr.com>...
> "Emille378" <dishonty@.seidata.com> wrote in message
> news:d25b2692.0405050650.854b85c@.posting.google.co m...
> > Environment is SQL Server 2000 64 bit.
> > I restore from a script 'my' database, this works fine. However, all
> > the tables are also found in master, no data though.
> > Anyone experienced this?
> Can you show us the script?

RESTORE DATABASE XX
FROM DISK = 'g:\XX_db.BAK'
WITH STATS = 10, REPLACE,
MOVE 'XX_data' TO 'h:\Program Files\Microsoft SQL
Server\MSSQL\Data\XX_Data.mdf',
MOVE 'XX_log' TO 'g:\logs\XX_Log.ldf',
MOVE 'XX_Indx' TO 'h:\Program Files\Microsoft SQL
Server\MSSQL\Data\XX_Indx_Data.NDF'

Restore problems.

Hi,

I need to restore a database from a server to another, but all tables are not dbo's tables.

I first create the user on the destination instance, and after restoring datas, I try to change rights for this user.

I can't delete or create new rights for this user because it own tables.

How can I restore my users rights database ?If you don't mind to change the owner to DBO (which is how it should have been) you can use sp_changeobjectowner procedure, without having to create the user.|||At this point, I'd probably use sp_change_users_login (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ca-cz_8qzy.asp).

-PatP|||sp_change_users_login will map SID's, but will not allow to do what the poster asked for.|||At least as I read the question, the poster created a login named "foo" because there was a user in the database they were restoring that was already named "foo". If that is the case, and they were expecting their login to have access to the database, then sp_change_users_login would fix the SID values, giving the "foo" login on this server equivalent permissions in this database to what the "foo" login on the original server had.

Once the poster fixes the underlying SID problem, they can then grant or revoke user permissions as they see fit.

Do you think that I missed something?

-PatP|||As I said earlier, if you use sp_changeobjectowner, you don't even need to create the "foo" login to begin with.|||Thanks for all opf us.
I succeed in my problem with sp_changeobjectowner procedure.|||RDJabarov is the man

Restore only few tables of the database

HI
I'm designing a database. This database will be installed in a few servers.
Some of the tables in the database the user can't change only once a month
the "center" should send their data to all clients. No connectivity between
installations (Replication isn't relevant). What we thougth to do is one of
the 2:
1. To have 2 databases, one will have all the tables and the other only the
"from center" tables, and once a month the center will send a backup of it
to be restore all over. the poblem is that we won't be able to perform data
integrity with Foreign key this way.
2. The other idea was to backup/restore only part of the database, maybe
depents on a schema or filegroup or so.
Is their a way to perform the 2nd way'?
Thanks"Chedva" <chedvag@.matrix-it.co.il> wrote in message
news:eiHxSsXHHHA.3952@.TK2MSFTNGP02.phx.gbl...
> HI
> I'm designing a database. This database will be installed in a few
> servers.
> Some of the tables in the database the user can't change only once a month
> the "center" should send their data to all clients. No connectivity
> between
> installations (Replication isn't relevant). What we thougth to do is one
> of
> the 2:
> 1. To have 2 databases, one will have all the tables and the other only
> the
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform
> data
> integrity with Foreign key this way.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
> Is their a way to perform the 2nd way'?
Can't you write something into your software to get this via the web and
update the tables? I don't think either of the options you've suggested are
the best way to go and you should not need to have 2 databases on the
client's machines.

> Thanks
>|||> 1. To have 2 databases, one will have all the tables and the other only the">
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform dat
a
> integrity with Foreign key this way.
This is an option. And the downside you already know. You can consider using
triggers to maintain
referential integrity, but I'd opt for option 3 below instead.

> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
This *might* be doable assuming SQL Server 2005. In 2005, you would have to
separate these tables to
its own filegroup and set that filegroup to read only. You can now ship a fi
legroup backup and have
them restore that filegroup backup. However, I doubt it will work for you as
you probably do
structural changes for these tables and structural changes affects the syste
m tables which are in
the primary filegroup. In the end, the problem is that SQL Server won't let
you restore a database
so different parts of the database are from different points in time. This w
ould be an advanced
usage of the backup/restore features of SQL Server 2005, so you need to make
sure that you are very
familiar with backup/restore. I would *not* even consider this route.
3. Ship script files containing CREATE TABLE, ALTER TABLE etc that your clie
nts execute using OSQL,
SQLCMD or through your own app. This is the, IMO, most robust solution and w
ill give you the least
problems in the end.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chedva" <chedvag@.matrix-it.co.il> wrote in message news:eiHxSsXHHHA.3952@.TK2MSFTNGP02.phx.g
bl...
> HI
> I'm designing a database. This database will be installed in a few servers
.
> Some of the tables in the database the user can't change only once a month
> the "center" should send their data to all clients. No connectivity betwee
n
> installations (Replication isn't relevant). What we thougth to do is one o
f
> the 2:
> 1. To have 2 databases, one will have all the tables and the other only th
e
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform dat
a
> integrity with Foreign key this way.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
> Is their a way to perform the 2nd way'?
> Thanks
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:emjgcQdHHHA.3468@.TK2MSFTNGP04.phx.gbl...
> 3. Ship script files containing CREATE TABLE, ALTER TABLE etc that your
> clients execute using OSQL, SQLCMD or through your own app. This is the,
> IMO, most robust solution and will give you the least problems in the end.
Don't ALL apps of this nature (distributed to X no of clients) do this
anyway? Surely the time comes to update the structure of some tables and the
client surely doesn't do it themselves. We store a database version no and
run upgrades based on this version no. We also drop all stored procs, views,
indexes, functions, primary keys etc and recreate them. This last step is
probably not suitable for very large databases but works very well for us. I
find we very rarely do a release without some db change.
Michael

Restore only few tables of the database

HI
I'm designing a database. This database will be installed in a few servers.
Some of the tables in the database the user can't change only once a month
the "center" should send their data to all clients. No connectivity between
installations (Replication isn't relevant). What we thougth to do is one of
the 2:
1. To have 2 databases, one will have all the tables and the other only the
"from center" tables, and once a month the center will send a backup of it
to be restore all over. the poblem is that we won't be able to perform data
integrity with Foreign key this way.
2. The other idea was to backup/restore only part of the database, maybe
depents on a schema or filegroup or so.
Is their a way to perform the 2nd way'?
Thanks"Chedva" <chedvag@.matrix-it.co.il> wrote in message
news:eiHxSsXHHHA.3952@.TK2MSFTNGP02.phx.gbl...
> HI
> I'm designing a database. This database will be installed in a few
> servers.
> Some of the tables in the database the user can't change only once a month
> the "center" should send their data to all clients. No connectivity
> between
> installations (Replication isn't relevant). What we thougth to do is one
> of
> the 2:
> 1. To have 2 databases, one will have all the tables and the other only
> the
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform
> data
> integrity with Foreign key this way.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
> Is their a way to perform the 2nd way'?
Can't you write something into your software to get this via the web and
update the tables? I don't think either of the options you've suggested are
the best way to go and you should not need to have 2 databases on the
client's machines.
> Thanks
>|||> 1. To have 2 databases, one will have all the tables and the other only the
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform data
> integrity with Foreign key this way.
This is an option. And the downside you already know. You can consider using triggers to maintain
referential integrity, but I'd opt for option 3 below instead.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
This *might* be doable assuming SQL Server 2005. In 2005, you would have to separate these tables to
its own filegroup and set that filegroup to read only. You can now ship a filegroup backup and have
them restore that filegroup backup. However, I doubt it will work for you as you probably do
structural changes for these tables and structural changes affects the system tables which are in
the primary filegroup. In the end, the problem is that SQL Server won't let you restore a database
so different parts of the database are from different points in time. This would be an advanced
usage of the backup/restore features of SQL Server 2005, so you need to make sure that you are very
familiar with backup/restore. I would *not* even consider this route.
3. Ship script files containing CREATE TABLE, ALTER TABLE etc that your clients execute using OSQL,
SQLCMD or through your own app. This is the, IMO, most robust solution and will give you the least
problems in the end.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chedva" <chedvag@.matrix-it.co.il> wrote in message news:eiHxSsXHHHA.3952@.TK2MSFTNGP02.phx.gbl...
> HI
> I'm designing a database. This database will be installed in a few servers.
> Some of the tables in the database the user can't change only once a month
> the "center" should send their data to all clients. No connectivity between
> installations (Replication isn't relevant). What we thougth to do is one of
> the 2:
> 1. To have 2 databases, one will have all the tables and the other only the
> "from center" tables, and once a month the center will send a backup of it
> to be restore all over. the poblem is that we won't be able to perform data
> integrity with Foreign key this way.
> 2. The other idea was to backup/restore only part of the database, maybe
> depents on a schema or filegroup or so.
> Is their a way to perform the 2nd way'?
> Thanks
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:emjgcQdHHHA.3468@.TK2MSFTNGP04.phx.gbl...
> 3. Ship script files containing CREATE TABLE, ALTER TABLE etc that your
> clients execute using OSQL, SQLCMD or through your own app. This is the,
> IMO, most robust solution and will give you the least problems in the end.
Don't ALL apps of this nature (distributed to X no of clients) do this
anyway? Surely the time comes to update the structure of some tables and the
client surely doesn't do it themselves. We store a database version no and
run upgrades based on this version no. We also drop all stored procs, views,
indexes, functions, primary keys etc and recreate them. This last step is
probably not suitable for very large databases but works very well for us. I
find we very rarely do a release without some db change.
Michael

Restore of Log to Standby Server

We are maintaining a stand-by database server by doing a daily manual restore of logs. On weekends we do Reindexing of the tables for performance boost. some of these reindexing jobs take upto 10 Hrs
We have found that the restore of the logs taken in the source server after the reindexing job takes as much time it took to do the reindexing.
Typically 15 transaction log sessions for a day takes 40 minutes to restore. However the restore of the tranlog that was taken after a 10 Hr long reindexing job takes as much time(10 hrs) to do the restore also.
The size of the tranlog does not justify this time frame. ( Size of tran logs after reindex is more than other logs but is less than 3 GB)
I would like to know what exactly is happening during the restore of the tran log. Is it doing the reindexing in the destination system as well?
The destination server (standby) is in non recovered mode all the time.
The interesting thing is that I would only take 8 Hrs to do a full restore of the database. We are using ArcServ software for backup and restore.

We are on SQLServer Enterprise 7.0 sp4 on NT 4 Enterprise on source and destination.
We run SAP R/3 on a MSCS Clustered env.
The source database size is 260 GB

Any Comments?
Appreciate your replies

Cyriac

ATypically, Reindex or Reorg databases(either thru a maintenance plan or not) will generate large log files. The reason is that SQL would have to record everything in the log and rewrite them back to the .mdf file with the fill factor you specified. When you restore the db using the log file generated by the reorg process, it may take the same amount of time as the reorg. There are a couple ways we tried and saved time. One is to only do the log backup after the reorg so instead of restore from multiple log files you restore from only one. This saves about 15% runtime. The other way was to restore from the complete database backup after the reorg. As you have mentioned, it cut down about 20%.