Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Bread and Butter of SQL Server DBA - Part 2

By MAK,

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

Backup File

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

Logical Backup

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

File data

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

Names

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

Restore results

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

New files

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

Results

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.

Total article views: 7010 | Views in the last 30 days: 1
 
Related Articles
FORUM

Backup/restore

Backup/restore

FORUM

Restore from Backup with Replicated Database

Restore from Backup with Replicated Database

FORUM

database backup and restore in sql server 2005

database backup and restore using java

SCRIPT

Backup & Restoration Script

This script provides very useful information about database backup and restoration.

FORUM

Backup FileGroup and Restore on Secondary Database

Backup FileGroup and Restore on Secondary Database with same filegroups

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones