Re: Database copy

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

Marcos (marcos_rodas@xxxxxxxxxxx) 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, 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: % Disk Time
    ... Avg and current Disk queues instead. ... > I have separate drives each SQL Server database: ... > then your SQL Server ... >> Is not a very reliable indicator of an IO bottleneck. ...
    (microsoft.public.sqlserver.server)
  • Re: Server stops responding
    ... you may want to track your database with SQL Server Profiler to see if some processes takes longer then they should take. ... Physical Disk: % Disk Time (seperately for your OS and database disks, ...
    (microsoft.public.sqlserver.clients)
  • Re: 2 million records
    ... first point, its not a datawarehousing question, but a general SQL Server ... question & Web Developper question. ... second is not relevant to fine tune your database. ... Look at the avg disk queue length perf counter, ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: SQL Server Issues ::: ( how to avoid this issues )
    ... The file sizehave all the info you need for DISK REINIT. ... Log pages where SQL Server expects data pages is automatically handled over time. ... Or just transfer the stuff into a new database. ... >>> You guys are thinking just in the higher level of IT security policies... ...
    (microsoft.public.sqlserver.server)
  • Re: Create database fails because of lack of disk space...
    ... I've never seen things like that so just shooting in the ... Is it possible that SQL server gets really funky and doesn't read OS ... causing SQL server to still think the disk drive is full? ... > CREATE DATABASE fails ("CREATE DATABASE failed. ...
    (microsoft.public.sqlserver.server)