Re: Database copy

Tech-Archive recommends: Fix windows errors by optimizing your registry

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

"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns99E3D4EF9DF4Yazorman@xxxxxxxxxxxx
msnews (marcos_rodas@xxxxxxxxxxx) 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, esquel@xxxxxxxxxxxxx

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


.

Relevant Pages

  • Re: unable to start SQL Service ... error code 3417
    ... How about just create a new instance and restore databases to the new instance? ... I wouldn't go with restoring master from another instance. ... The supported route is to rebuild your master database, start SQL Server in single user mode, ...
    (microsoft.public.sqlserver.server)
  • Re: SQL 2000 SP4 on 2003 Server - Single User Mode
    ... I found the section where you list the filename to restore to. ... rename the SQL Server the same as what is listed below Courthouse? ... Once you've created your database (simply right click on the Database ...
    (microsoft.public.sqlserver.setup)
  • Re: Moving databases from SQL 7 to SQL 2000 - More Questions!
    ... want to run sp_updatestats after the restore or attach as well. ... >>There is a lot more that can go wrong with the wizard ... > SQL Server Databases ... >>Issues When a Database Is Moved Between SQL Servers ...
    (microsoft.public.sqlserver.setup)
  • Re: HELP!!! restore database - left in loading state?? PLEASE H
    ... RESTORE DATABASE WITH RECOVERY ... Columnist, SQL Server Professional ...
    (microsoft.public.sqlserver.tools)
  • Re: Restore Master DB to New Server
    ... the user database will be suspect. ... Just drop them and then do the restore. ... Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.tools)