When restoring a full database backup into an existing database, WITH
REPLACE is used to overwrite the existing target database. This
suggests that the physical path and file name for the data and log
files of the target database will be overwritten. However, error
message 1834 indicates that an explicit MOVE needs to be done of the
physical data and log files, eventhough those are already known.
How do you restore a backup into an existing database, automatically
using the target's physical files and avoiding MOVE?You can do a RESTORE FILEISTONLY FROM DISK='your backup file' to see where
the database files will go. If they are not going to where you want to place
them, you must use the MOVE option regardless where the database files of the
currently existing database may be placed.
Linchi
"j.roumimper@.gmail.com" wrote:
> When restoring a full database backup into an existing database, WITH
> REPLACE is used to overwrite the existing target database. This
> suggests that the physical path and file name for the data and log
> files of the target database will be overwritten. However, error
> message 1834 indicates that an explicit MOVE needs to be done of the
> physical data and log files, eventhough those are already known.
> How do you restore a backup into an existing database, automatically
> using the target's physical files and avoiding MOVE?
>
Showing posts with label target. Show all posts
Showing posts with label target. Show all posts
Sunday, March 11, 2012
Database restore with REPLACE avoiding MOVE
When restoring a full database backup into an existing database, WITH
REPLACE is used to overwrite the existing target database. This
suggests that the physical path and file name for the data and log
files of the target database will be overwritten. However, error
message 1834 indicates that an explicit MOVE needs to be done of the
physical data and log files, eventhough those are already known.
How do you restore a backup into an existing database, automatically
using the target's physical files and avoiding MOVE?
You can do a RESTORE FILEISTONLY FROM DISK='your backup file' to see where
the database files will go. If they are not going to where you want to place
them, you must use the MOVE option regardless where the database files of the
currently existing database may be placed.
Linchi
"j.roumimper@.gmail.com" wrote:
> When restoring a full database backup into an existing database, WITH
> REPLACE is used to overwrite the existing target database. This
> suggests that the physical path and file name for the data and log
> files of the target database will be overwritten. However, error
> message 1834 indicates that an explicit MOVE needs to be done of the
> physical data and log files, eventhough those are already known.
> How do you restore a backup into an existing database, automatically
> using the target's physical files and avoiding MOVE?
>
REPLACE is used to overwrite the existing target database. This
suggests that the physical path and file name for the data and log
files of the target database will be overwritten. However, error
message 1834 indicates that an explicit MOVE needs to be done of the
physical data and log files, eventhough those are already known.
How do you restore a backup into an existing database, automatically
using the target's physical files and avoiding MOVE?
You can do a RESTORE FILEISTONLY FROM DISK='your backup file' to see where
the database files will go. If they are not going to where you want to place
them, you must use the MOVE option regardless where the database files of the
currently existing database may be placed.
Linchi
"j.roumimper@.gmail.com" wrote:
> When restoring a full database backup into an existing database, WITH
> REPLACE is used to overwrite the existing target database. This
> suggests that the physical path and file name for the data and log
> files of the target database will be overwritten. However, error
> message 1834 indicates that an explicit MOVE needs to be done of the
> physical data and log files, eventhough those are already known.
> How do you restore a backup into an existing database, automatically
> using the target's physical files and avoiding MOVE?
>
Database restore with REPLACE avoiding MOVE
When restoring a full database backup into an existing database, WITH
REPLACE is used to overwrite the existing target database. This
suggests that the physical path and file name for the data and log
files of the target database will be overwritten. However, error
message 1834 indicates that an explicit MOVE needs to be done of the
physical data and log files, eventhough those are already known.
How do you restore a backup into an existing database, automatically
using the target's physical files and avoiding MOVE?You can do a RESTORE FILEISTONLY FROM DISK='your backup file' to see where
the database files will go. If they are not going to where you want to place
them, you must use the MOVE option regardless where the database files of th
e
currently existing database may be placed.
Linchi
"j.roumimper@.gmail.com" wrote:
> When restoring a full database backup into an existing database, WITH
> REPLACE is used to overwrite the existing target database. This
> suggests that the physical path and file name for the data and log
> files of the target database will be overwritten. However, error
> message 1834 indicates that an explicit MOVE needs to be done of the
> physical data and log files, eventhough those are already known.
> How do you restore a backup into an existing database, automatically
> using the target's physical files and avoiding MOVE?
>
REPLACE is used to overwrite the existing target database. This
suggests that the physical path and file name for the data and log
files of the target database will be overwritten. However, error
message 1834 indicates that an explicit MOVE needs to be done of the
physical data and log files, eventhough those are already known.
How do you restore a backup into an existing database, automatically
using the target's physical files and avoiding MOVE?You can do a RESTORE FILEISTONLY FROM DISK='your backup file' to see where
the database files will go. If they are not going to where you want to place
them, you must use the MOVE option regardless where the database files of th
e
currently existing database may be placed.
Linchi
"j.roumimper@.gmail.com" wrote:
> When restoring a full database backup into an existing database, WITH
> REPLACE is used to overwrite the existing target database. This
> suggests that the physical path and file name for the data and log
> files of the target database will be overwritten. However, error
> message 1834 indicates that an explicit MOVE needs to be done of the
> physical data and log files, eventhough those are already known.
> How do you restore a backup into an existing database, automatically
> using the target's physical files and avoiding MOVE?
>
Friday, February 17, 2012
Database Permissions
We have a system at work that copies (using DTS) over databases from
one SQL Server box to another every night. The copying process drops
each target object and then recreates them. On the 2nd SQL Server box
I have an account setup that is supposed to only have access to run
queries on the databases that get copied over every night. However,
because the DTS packages are dropping the objects first we are losing
the table level permissions for this user, so this user can't access
these databases the next day. Is there a way to automate resetting
these permissions on each table in the databases? Perhaps I should be
using replication as opposed to DTS packages for copying over entire
databases? If I used replication, would this avoid losing the
permissions that I need?
one SQL Server box to another every night. The copying process drops
each target object and then recreates them. On the 2nd SQL Server box
I have an account setup that is supposed to only have access to run
queries on the databases that get copied over every night. However,
because the DTS packages are dropping the objects first we are losing
the table level permissions for this user, so this user can't access
these databases the next day. Is there a way to automate resetting
these permissions on each table in the databases? Perhaps I should be
using replication as opposed to DTS packages for copying over entire
databases? If I used replication, would this avoid losing the
permissions that I need?
Thanks,
Jeffjeffpuro@.yahoo.com (Jeff) wrote in message news:<7851a310.0401151322.4b8cf2e7@.posting.google.com>...
> We have a system at work that copies (using DTS) over databases from
> one SQL Server box to another every night. The copying process drops
> each target object and then recreates them. On the 2nd SQL Server box
> I have an account setup that is supposed to only have access to run
> queries on the databases that get copied over every night. However,
> because the DTS packages are dropping the objects first we are losing
> the table level permissions for this user, so this user can't access
> these databases the next day. Is there a way to automate resetting
> these permissions on each table in the databases? Perhaps I should be
> using replication as opposed to DTS packages for copying over entire
> databases? If I used replication, would this avoid losing the
> permissions that I need?
> Thanks,
> Jeff
You could script the table permissions, then execute that script after
copying the objects. Although it's not clear from your post why you
always drop and recreate the objects - DTS can copy only the data, or
you can put it in staging tables first, then insert into the final
production tables if you have some mapping logic. Replication would be
an option (perhaps snapshot replication), but it can be complex.
Simon
Subscribe to:
Posts (Atom)