"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns99E2EC87C6BB7Yazorman@127.0.0.1...
> msnews (marcos_ro***@hotmail.com) writes:
>> I like to use the copy database tool to copy 15 databases from a
>> production SQL 2005 64 Bit to a test SQL 2005 64 Bit system. Is there a
>> documentation specific about how to do it?
>
> I've never tried it, but it as appear that the Copy Database Wizard
> permits you to copy many databases at a time, alhtough you always start
> it from the context of a specific database.
>
> Be sure to use the attach/detach method. The SMO method is very
> unreliable.
>
> Many people would use BACKUP/RESTORE directly rather than dealing
> with the wizard.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns99E3D4EF9DF4Yazorman@127.0.0.1...
> msnews (marcos_ro***@hotmail.com) writes:
>> I like to use BACKUP/RESTORE but without bringing any users (from source
>> backup database).
>
> The easiest would be to drop the users when you have restored the datbase.
>
> SELECT 'DROP USER ' + name FROM sys.database_principals
> WHERE name NOT IN ('dbo', 'guest', 'sys', 'INFORMATION_SCHEMA')
>  AND type_desc = 'SQL_USER'
>
> If any user but dbo owns objects, for instance schemas, you will hit some
> snags, and you will need to decide what to do in that case.
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns99E3EF2927139Yazorman@127.0.0.1...
> Marcos (marcos_ro***@hotmail.com) writes:
>> I guess I'll need a script to restore the database then drop the users.
>> Are this the commands I need to restore the database?
>>
>> RESTORE DATABASE database_name
>> FROM DISK = 'C:\Backups\database_name.BAK'
>> WITH NORECOVERY
>
> Depends. Only use WITH NORECOVERY if you intend to also restore
> transaction logs. Which you usually don't want when you copy a database.
>
> Also, when you copy a database to different locations or different
> names, SQL Server can be picky about details, leading to this
> command:
>
>
>   RESTORE DATABASE db
>   FROM DISK = 'backuppath'
>   WITH MOVE 'logicalname1' TO 'phyiscalpath1',
>        MOVE 'logicalname2' TO 'physicalpath2',
>        REPLACE
>
> The logical names are the internal names of the database files, and
> you can find then with sp_helpdb to RESTORE FILELISTONLY.
>
> physicalpath1 is the place for the files of the restored database.
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

"Marcos" <marcos_ro***@hotmail.com> wrote in message
news:esAmjfUJIHA.1204@TK2MSFTNGP03.phx.gbl...
>I tried restoring from a copy of a backup but it wouldn't recognize it as
>being a backup file. I guess I'll need to do the WITH MOVE command.
>
> "Erland Sommarskog" <esq***@sommarskog.se> wrote in message
> news:Xns99E3EF2927139Yazorman@127.0.0.1...
>> Marcos (marcos_ro***@hotmail.com) writes:
>>> I guess I'll need a script to restore the database then drop the users.
>>> Are this the commands I need to restore the database?
>>>
>>> RESTORE DATABASE database_name
>>> FROM DISK = 'C:\Backups\database_name.BAK'
>>> WITH NORECOVERY
>>
>> Depends. Only use WITH NORECOVERY if you intend to also restore
>> transaction logs. Which you usually don't want when you copy a database.
>>
>> Also, when you copy a database to different locations or different
>> names, SQL Server can be picky about details, leading to this
>> command:
>>
>>
>>   RESTORE DATABASE db
>>   FROM DISK = 'backuppath'
>>   WITH MOVE 'logicalname1' TO 'phyiscalpath1',
>>        MOVE 'logicalname2' TO 'physicalpath2',
>>        REPLACE
>>
>> The logical names are the internal names of the database files, and
>> you can find then with sp_helpdb to RESTORE FILELISTONLY.
>>
>> physicalpath1 is the place for the files of the restored database.
>>
>>
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>>
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
>