Part 1 of this article series illustrated a simple and straightforward way of backing up a database and restoring it. Part 2 of this article series is going to illustrate how to restore a database from the backup file with a new name instead of overwriting the existing database.
There are situations where database administrators need to copy data from old backup files to the current database and there are situations where Database Administrators have to restore database from another server. In either case, you could use this part of the article series to accomplish that.
Let us assume that some 3rd party vendor provided a backup file to you and one of your business units, wants this database to be restored on a Development server. Let us also assume that the database backup file name is ‘Samtech_DB_Aug_23_2008.Bak’ and it is copied to D:\Backup folder as shown below. Refer Fig 1.0
Figure 1.0 Samtech_DB_Aug_23_2008.Bak
Step 1
The first step in restoring a backup is to find brief information about the database from the backup file. The minimum detail that is required to restore a database from a backup file is to find the logical of Data files ,log files, its Size and original locations. This could be found by executing the following SQL Statement as shown below. Refer Fig 1.1
| restore filelistonly from disk ='d:\backup\Samtech_DB_Aug_23_2008.Bak' |
This will provide the following information about the backup as shown below. Refer Figure 1.1
Figure 1.1 Results - Restore Filelistonly
From Figure 1.1 results, we could easily understand that there are three Data files and two log files for the database.
Step 2
The second step in the restore process is to find whether we have enough space to restore the database from the backup file. From Figure 1.1, we could see the size of the Data and Log files. However, the size shown in the result set is in byte. So let us calculate the size of the data files and log files in Megabytes.
Execute the following query as shown below.
set nocount on go select 2097152/(1024*1024) as DataFile1SizeinMB select 1048576/(1024*1024) as DataFile2SizeinMB select 1048576/(1024*1024) as DataFile3SizeinMB select 1048576/(1024*1024) as LogFile1SizeinMB select 1048576/(1024*1024) as LogFile2SizeinMB go |
This would give you the result similar to the screen shot as shown below. Refer Figure 1.2
Figure 1.2
From the result we could find that the total size of the database including data files and log files is around 6 Megabytes.
Step 3
The third step in the restore process is to find if we already have a database with name ‘Samtech_DB’. We could find that by executing the following transact SQL query as shown below.
| Select name from master.dbo.sysdatabases where name like '%Samtech%' |
This would give you the result similar to the screen shot as shown below. Refer Figure 1.3
Figure 1.3 Database name with prefix ‘Samtech’
Step 4
Let us assume that the standard of your company is to keep all the Data files in D:\Device\Data and all log files in D:\Device\Log folder. From Figure 1.1, we could see that the original location of the data and log files of the database backup ‘Samtech_DB_Aug_23_2008.Bak’ is D:\Data. From Figure 1.3, we could see that there is already a database name Samtech_DB and many other databases with prefix Samtech.
Let us assume that the Business unit does’t want you to overwrite any existing databases on the server.
Based on the information available from Step1, Step2 and step3, we could restore the database from the backup file now by executing the following query as shown below.
use master go Restore database Samtech_Aug23_2008 from disk ='D:\Backup\Samtech_DB_Aug_23_2008.Bak' with replace, move 'Samtech_DB_Data' to 'D:\Device\Data\Samtech_DB_Data_Aug23.mdf', move 'Samtech_DB_Data2' to 'D:\Device\Data\Samtech_DB_Data2_Aug23.mdf', move 'Samtech_DB_Data3' to 'D:\Device\Data\Samtech_DB_Data3_Aug23.mdf', move 'Samtech_DB_Log' to 'D:\Device\Log\Samtech_DB_Log_Aug23.ldf', move 'Samtech_DB_Log2' to 'D:\Device\Log\Samtech_DB_Log2_Aug23.ldf' |
This would give you the result similar to the screen shot as shown below. Refer Figure 1.4
Figure 1.4 Restore results
Note: It is always a good practice to name the physical data file and log file similar to the database name. From the Restore command you could see we are restoring the actual database Samtech_DB as Samtech_DB_Aug23_2008 and the phsical files are named accordingly. You could also see that we restored the physical data files to D:\Device\Data folder and Log files to D:\Device\Log folder. Refer Figure 1.5
Figure 1.5 Physical Data file and log file
Step 6
Next step is the post restore process. If users need to access the database, we need to map all the logins on the server to the users in the restored database.
This could be done by executing the following transact sql statement. This transact sql statement basically generates ‘sp_change_users_login’ command for every users in the sysusers table and executes that command.
use [Samtech_Aug23_2008]; go declare @LOGINNAME varchar(100) declare @CHANGECOMMAND varchar(100) declare USERCURSOR cursor for select name from sysusers where name not in ('public','dbo','guest','sys','Information_schema', 'db_owner', 'db_accessadmin', 'db_securityadmin', 'db_ddladmin', 'db_backupoperator', 'db_datareader', 'db_datawriter', 'db_denydatareader', 'db_denydatawriter') open USERCURSOR fetch next from USERCURSOR into @LOGINNAME while @@fetch_status=0 begin select @CHANGECOMMAND = 'exec sp_change_users_login ''Auto_Fix'', ''' +@LOGINNAME + '''' PRINT 'Executing the command '+@CHANGECOMMAND exec (@CHANGECOMMAND ) fetch next from USERCURSOR into @LOGINNAME end close USERCURSOR deallocate USERCURSOR |
This would give you the result similar to the screen shot as shown below ow. Refer Figure 1.6
Figure 1.6 Result screen from sp_change_users_login
Note: Sometimes your SQL Server may not have the same logins like in Vendor’s SQL Server. In that case, you may have to create logins.
Conclusion
As mentioned in the beginning of this article, it illustrated how to restore a database from its backup file with a new name instead of overwriting existing database. It also illustrated how to map the logins on the server with the users in the database.
