|
|
Posted by Plamen Ratchev on 05/17/07 03:36
"Steve" <tinker123@gmail.com> wrote in message
news:1179367139.362553.256200@n59g2000hsh.googlegroups.com...
> On May 16, 2:22 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
>> Normally a backup or the production database and restore to test works
>> best.
>> In SQL Server 2005 I would do a copy-only backup.
>>
>> Here is the syntax for copy-only full backup:
>>
>> BACKUP DATABASE production_db_name TO <backup_device> . WITH COPY_ONLY .
>>
>> Note that SSMS does not support copy-only backup, you have to run a
>> script.
>
> Why is it called a copy only backup?
It is called copy-only backup because because it is independent of the
sequence of the regular SQL Server backups (so it just makes a copy, not
affecting other backups that are scheduled for the same database). When you
perform regular backups (full/differencial/transaction log) there is a
specific sequence that has to be followed when restoring data. Also, the
copy-only backup doesn't truncate the transaction log.
The essential thing to know is that a copy-only backup does not affect your
overall backup and restore procedures for the database.
If all that sounds confusing, you can read more in the SQL Server Books
OnLine about backups.
>
> Is there an option for non-admins?
Yes, I believe only members of the sysadmin fixed server role and the
db_owner and db_backupoperator fixed database roles have BACKUP DATABASE
permission by default.
An alternative option is to use the Copy Database Wizard in SSMS (right
click a database, select Tasks, Copy Database...). You can use it to
transfer, move, or copy a database from an SQL Server 2000 or SQL Server
2005 instance to an instance of SQL Server 2005. Just make sure to select
the SMO method (not detach-and-attach) to keep your source database online.
>
> My test database has a different name ( but identical schema ), once
> copy-only backed up, and restored would my admin just then change the
> name ( after dropping the old test database? ).
>
You can restore over the existing database, that way keeping the name (or
drop first the test database and then restore under the same name). Based on
your security model, your administrator may need to map the database user
and SQL Server login accounts (using the system stored procedure
sp_change_users_login).
HTH,
Plamen Ratchev
http://www.SQLStudio.com
Navigation:
[Reply to this message]
|