[dba-SQLServer] Backup/Restore Question

Billy Pang tuxedo_man at hotmail.com
Thu Aug 18 16:40:54 CDT 2005


you want a copy of the same db on the same server without affecting the 
existing db, right?

the following code restores a db from backup file; the new user db will use 
the physical db file names you give to it....

RESTORE DATABASE [b] FROM
DISK = N'c:\the_backup_file_name_of_a_db.bak' WITH  FILE = 1,
NOUNLOAD ,  STATS = 10,  RECOVERY ,
MOVE N'a_Data' TO N'c:\Program Files\Microsoft SQL 
Server\MSSQL\data\b_data.mdf',
MOVE N'a_Log' TO N'c:\Program Files\Microsoft SQL 
Server\MSSQL\data\b_log.ldf'

the most important part is the "RESTORE DATABASE [b]" since this will tell 
you the name of the db you are trying to restore.  that is, if it is 
"RESTORE DATABASE [a]", you will overwriting the existing database "a" 
(which is something you don't want).  the last two lines with the MOVE 
option indicates the new physical file names to use for the logical data 
files that are already present in the backed up db.  To be thorough, look up 
"restore" in BOL to make sure you understand before proceeding.

hth

Billy


>From: "Clay Passick" <Clay.Passick at minneapolis.edu>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: <dba-sqlserver at databaseadvisors.com>
>Subject: [dba-SQLServer] Backup/Restore Question
>Date: Thu, 18 Aug 2005 11:43:36 -0500
>
>Hi to all
>
>I am trying to restore a database from a server file backup.  I do not
>have a backup through SQL Server as it is a database in development and
>not production at this point.   Is there a way to take the restored from
>backup files of database A
>(A_data.mdf and A_log.ldf) and rename those files as B_data.mdf and
>B_log.ldf and restore them to a newly created database B.  I don't want
>to restore over this database in development just retrieve a table or
>two.  Everything I have tried at this point either errors out or looks
>like it will overwrite the original database because it knows that
>originally it was database A.
>
>Clay
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>





More information about the dba-SQLServer mailing list