How to restore to a different database in sql server

Question

I have a backup of Database1 from a week ago  The backup is done weekly in the scheduler and I get a  bak file  Now I want to fiddle with some data so I need to restore it to a different database - Database2   I have seen this question  Restore SQL Server database in same pc with different name and the recommended step is to rename the original db  but I am out of that option as I am in the production server and I cant really do it   Is there any other way of restoring it to Database2  or atleast  how do I browse through the data of that  bak file   thanks   ps  the second answer from the above link looked promising but it keeps terminating with error         Restore Filelist is terminating abnormally

User · Accepted Answer

You can create a new db then use the  Restore Wizard  enabling the Overwrite option or   View the content   RESTORE FILELISTONLY FROM DISK  c  your bak    note the logical names of the  mdf  amp   ldf from the results  then   RESTORE DATABASE MyTempCopy FROM DISK  c  your bak  WITH     MOVE  LogicalNameForTheMDF  TO  c  MyTempCopy mdf      MOVE  LogicalNameForTheLDF  TO  c  MyTempCopy log ldf    To create the database MyTempCopy with the contents of your bak   Example  restores a backup of a db called  creditline  to  MyTempCopy    RESTORE FILELISTONLY FROM DISK  e  mssql backup creditline bak    gt LogicalName  gt --------------  gt CreditLine  gt CreditLine log  RESTORE DATABASE MyTempCopy FROM DISK  e  mssql backup creditline bak  WITH     MOVE  CreditLine  TO  e  mssql MyTempCopy mdf      MOVE  CreditLine log  TO  e  mssql MyTempCopy log ldf    gt RESTORE DATABASE successfully processed 186 pages in 0 010 seconds  144 970 MB sec

User · Answer

Actually  there is no need to restore the database in native SQL Server terms  since you  want to fiddle with some data  and  browse through the data of that  bak file   You can use ApexSQL Restore      a SQL Server tool that attaches both native and natively compressed SQL database backups and transaction log backups as live databases  accessible via SQL Server Management Studio  Visual Studio or any other third-party tool  It allows attaching single or multiple full  differential and transaction log backups  Moreover  I think that you can do the job while the tool is in fully functional trial mode  14 days   Disclaimer  I work as a Product Support Engineer at ApexSQL

User · Answer

Here s what I ve cobbled together from various posts to copy a database using backup and restore with move to fix the physical location and additional sql to fix the logical name          Creates  or resets  a Database to a copy of the template database using backup and restore        Usage  Update the  NewDatabase value to the database name to create or reset       DECLARE  NewDatabase SYSNAME    new db    -- Set up USE tempdb   DECLARE  TemplateBackups SYSNAME    TemplateBackups   DECLARE  TemplateDatabase SYSNAME    template db   DECLARE  TemplateDatabaseLog SYSNAME    TemplateDatabase     log    -- Create a backup of the template database BACKUP DATABASE  TemplateDatabase TO DISK    TemplateBackups WITH CHECKSUM  COPY ONLY  FORMAT  INIT  STATS   100   -- Get the backup file list as a table variable DECLARE  BackupFiles TABLE LogicalName nvarchar 128  PhysicalName nvarchar 260  Type char 1  FileGroupName nvarchar 128  Size numeric 20 0  MaxSize numeric 20 0  FileId tinyint CreateLSN numeric 25 0  DropLSN numeric 25  0  UniqueID uniqueidentifier ReadOnlyLSN numeric 25 0  ReadWriteLSN numeric 25 0  BackupSizeInBytes bigint SourceBlockSize int FileGroupId int LogGroupGUID uniqueidentifier DifferentialBaseLSN numeric 25 0  DifferentialBaseGUID uniqueidentifier IsReadOnly bit IsPresent bit TDEThumbprint varbinary 32    INSERT  BackupFiles EXEC  RESTORE FILELISTONLY FROM DISK          TemplateBackups           -- Create  the backup file list as a table variable DECLARE  NewDatabaseData VARCHAR MAX   DECLARE  NewDatabaseLog VARCHAR MAX    SELECT  NewDatabaseData   PhysicalName FROM  BackupFiles WHERE Type    D   SELECT  NewDatabaseLog   PhysicalName FROM  BackupFiles WHERE Type    L    SET  NewDatabaseData   REPLACE  NewDatabaseData   TemplateDatabase   NewDatabase   SET  NewDatabaseLog   REPLACE  NewDatabaseLog   TemplateDatabase   NewDatabase    RESTORE DATABASE  NewDatabase FROM DISK    TemplateBackups WITH CHECKSUM  RECOVERY  REPLACE  STATS   100     MOVE  TemplateDatabase TO  NewDatabaseData     MOVE  TemplateDatabaseLog TO  NewDatabaseLog   -- Change Logical File Name DECLARE  SQL SCRIPT VARCHAR MAX        ALTER DATABASE   NewDatabase   SET SINGLE USER WITH ROLLBACK IMMEDIATE      ALTER DATABASE   NewDatabase   MODIFY FILE  NAME N   TemplateDatabase     NEWNAME N   NewDatabase          ALTER DATABASE   NewDatabase   MODIFY FILE  NAME N   TemplateDatabase  log    NEWNAME N   NewDatabase  log         ALTER DATABASE   NewDatabase   SET MULTI USER WITH ROLLBACK IMMEDIATE      SELECT name AS logical name  physical name FROM SYS MASTER FILES WHERE database id   DB ID N   NewDatabase         SET  SQL SCRIPT   REPLACE  SQL SCRIPT    TemplateDatabase     TemplateDatabase   SET  SQL SCRIPT   REPLACE  SQL SCRIPT    NewDatabase     NewDatabase   EXECUTE   SQL SCRIPT

User · Answer

For SQL Server 2012  using Sql Server Management Studio  I found these steps from the Microsoft page useful to restore to a different database file and name   ref  http   technet microsoft com en-us library ms175510 aspx   Note steps 4 and 7 are important to set so as not to overwrite the existing database        To restore a database to a new location  and optionally rename the database         Connect to the appropriate instance of the SQL Server Database Engine  and then in Object Explorer  click the server name to expand   the server tree    Right-click Databases  and then click Restore Database  The Restore Database dialog box opens    On the General page  use the Source section to specify the source and location of the backup sets to restore  Select one of the   following options          Database         Select the database to restore from the drop-down list  The list contains only databases that have been backed up according to the   msdb backup history       Note If the backup is taken from a different server  the destination server will not have the backup history information   for the specified database  In this case  select Device to manually   specify the file or device to restore       Device         Click the browse       button to open the Select backup devices dialog box  In the Backup media type box  select one of   the listed device types  To select one or more devices for the   Backup media box  click Add  After you add the devices you want to the Backup media list box  click OK to return to the   General page  In the Source  Device  Database list box  select the name of the database which should be restored       Note   This list is only available when Device is selected  Only databases that have backups on the selected device will   be available          In the Destination section  the Database box is automatically populated with the name of the database to be restored    To change the name of the database  enter the new name in the   Database box    In the Restore to box  leave the default as To the last backup taken or click on Timeline to access the Backup   Timeline dialog box to manually select a point in time to stop the   recovery action     In the Backup sets to restore grid  select the backups to restore  This grid displays the backups available for the specified   location  By default  a recovery plan is suggested  To override the   suggested recovery plan  you can change the selections in the grid    Backups that depend on the restoration of an earlier backup are   automatically deselected when the earlier backup is deselected    To specify the new location of the database files  select the Files page  and then click Relocate all files to folder  Provide a new location for the Data file folder and Log file   folder  Alternatively you  can keep the same folders and just rename the database and log file names

User · Answer

I have the same error as this topic when I restore a new database using an old database   using  bak  gives the same error  I Changed the name of old database by name of new database  same this picture   It worked

User · Answer

make a copy from your database with  copy database  option with different name backup new copied database restore it

User · Answer

It is actually a bit simpler than restoring to the same server   Basically  you just walk through the  Restore Database  options   Here is a tutorial for you   http   www techrepublic com blog window-on-windows how-do-i-restore-a-sql-server-database-to-a-new-server 454  Especially since this is a non-production restore  you can feel comfortable just trying it out without worrying about the details too much   Just put your SQL files where you want them on your new server and give it whatever name you want and you are good to go

User · Answer

Here is how to restore a backup as an additional db with a unique db name   For SQL 2005 this works very quickly  I am sure newer versions will work the same   First  you don t have to take your original db offline  But for safety sake  I like to  In my example  I am going to mount a clone of my  billing  database and it will be named  billingclone    1  Make a good backup of the billing database  2  For safety  I took the original offline as follows   3  Open a new Query window    IMPORTANT  Keep this query window open until you are all done  You need to restore the db from this window   Now enter the following code   -- 1  free up all USER databases USE master  GO -- 2  kick all other users out  ALTER DATABASE billing SET SINGLE USER WITH ROLLBACK IMMEDIATE  GO -- 3  prevent sessions from re-establishing connection  ALTER DATABASE billing SET OFFLINE    3  Next  in Management Studio  rt click Databases in Object Explorer  choose  Restore Database   4  enter new name in  To Database  field  I E  billingclone  5  In Source for Restore  click  From Device  and click the     navigate button  6  Click Add and navigate to your backup  7  Put a checkmark next to Restore  Select the backup sets to restore   8  next select the OPTIONS page in upper LH corner  9  Now edit the database file names in RESTORE AS  Do this for both the db and the log  I E  billingclone mdf and billingclone log ldf  10  now hit OK and wait for the task to complete   11  Hit refresh in your Object Explorer and you will see your new db  12  Now you can put your billing db back online  Use the same query window you used to take billing offline  Use this command   -- 1  free up all USER databases USE master  GO -- 2  restore access to all users  ALTER DATABASE billing SET MULTI USER WITH ROLLBACK IMMEDIATE GO -- 3  put the db back online  ALTER DATABASE billing SET ONLINE    done

User · Answer

SQL Server 2008 R2   For an existing database that you wish to  restore  from a backup of a different database follow these steps    From the toolbar  click the Activity Monitor button  Click processes   Filter by the database you want to restore   Kill all running processes by right clicking on each process and selecting  kill process   Right click on the database you wish to restore  and select Tasks-- Restore-- From Database  Select the  From Device   radio button  Select     and choose the backup file of the other database you wish to restore from  Select the backup set you wish to restore from by selecting the check box to the left of the backup set  Select  Options   Select Overwrite the existing database  WITH REPLACE  Important  Change the  Restore As  Rows Data file name to the file name of the existing database you wish to overwrite or just give it a new name  Do the same with the log file file name  Verify from the Activity Monitor Screen that no new processes were spawned   If they were  kill them  Click OK

User · Answer

If no database exists I use the following code   ALTER PROCEDURE  dbo   RestoreBackupToNewDB                pathToBackup  varchar 500  --where to take backup from           pathToRestoreFolder  varchar 500   -- where to put the restored db files            newDBName varchar 100      AS     BEGIN              SET NOCOUNT ON             DECLARE  fileListTable TABLE                LogicalName            NVARCHAR 128                PhysicalName           NVARCHAR 260                Type                   CHAR 1                FileGroupName          NVARCHAR 128                Size                   NUMERIC 20 0                MaxSize                NUMERIC 20 0                FileID                 BIGINT               CreateLSN              NUMERIC 25 0                DropLSN                NUMERIC 25 0                UniqueID               UNIQUEIDENTIFIER               ReadOnlyLSN            NUMERIC 25 0                ReadWriteLSN           NUMERIC 25 0                BackupSizeInBytes      BIGINT               SourceBlockSize        INT               FileGroupID            INT               LogGroupGUID           UNIQUEIDENTIFIER               DifferentialBaseLSN    NUMERIC 25 0                DifferentialBaseGUID   UNIQUEIDENTIFIER               IsReadOnly             BIT               IsPresent              BIT               TDEThumbprint          VARBINARY 32  -- remove this column if using SQL 2005                           INSERT INTO  fileListTable EXEC  RESTORE FILELISTONLY FROM DISK        pathToBackup                   DECLARE  restoreDatabaseFilePath NVARCHAR 500              DECLARE  restoreLogFilePath NVARCHAR 500              DECLARE  databaseLogicName NVARCHAR 500              DECLARE  logLogicName NVARCHAR 500              DECLARE  pathSalt uniqueidentifier   NEWID                SET  databaseLogicName    SELECT LogicalName FROM  fileListTable WHERE  Type   D                SET  logLogicName    SELECT LogicalName FROM  fileListTable WHERE  Type   L                          SET  restoreDatabaseFilePath   pathToRestoreFolder    databaseLogicName   convert nvarchar 50    pathSalt      mdf              SET  restoreLogFilePath   pathToRestoreFolder    logLogicName   convert nvarchar 50    pathSalt      ldf               RESTORE DATABASE  newDBName FROM DISK  pathToBackup                  WITH                 MOVE  databaseLogicName TO  restoreDatabaseFilePath                 MOVE  logLogicName TO  restoreLogFilePath              SET NOCOUNT OFF     END

[sql-server-2008] How to restore to a different database in sql server?

Examples related to sql-server-2008

Examples related to backup

Examples related to restore