Introduction
On regular basis we need to restore the database, either will only schema of the database, or many times both data as well as schema.
Most of the applications (either Web application e.g. ASP.NET based apps, or Windows applications e.g. WPF based apps) requires 'reference data' such as drop-down data, types of users, etc. So what is the most optimum, reliable way of moving database either on separate database server, or on the same database server but with different database name? There are several ways to achieve this objective but my personal preference is to first create the database backup file i.e. 'my_backup.bak' using Microsoft SQL Server Management Studio, and then restoring that backup file to create new database.
This article focuses on restoring the backup file to create a new database. How to create the backup file will be covered in another article.
This article addresses below points:
1. How to check the name and path of the files (i.e. mdf, log, etc.) in the backup file.
2. How to move them to desired location, and if required rename them.
3. And finally, how to change the logical name of these files.
Lets see how we can achieve these objectives via T-SQL Script:
1. Check the name and path of the files.
Below command will check the contents of our backup file, in this case the name of the backup file is my_backup.bak. The output will show the logical as well as physical paths of the all the types of the files with their logical names, physical names and path, etc.
restore filelistonly from disk='D:\TempFolder\my_backup.bak';
go
2. Restore the database on desired location with required names.
Using output of above step, we can get the existing physical path and logical names of the files. In below command we using those, so if you restoring it on a new database-server then you can choose to keep both logical and physical name as it is, but if you are restoring the database on the same database-server and as understandably you need to rename the physical file names, or alternatively you may choose different folder.
In below example, I have renamed the physical file names as I am restoring a Test database to create a new Production database.
Note: In this case my database does not have any transaction data, except reference data, which will be same even on Production instance.
restore database [MyWebApp] from disk='D:\TempFolder\my_backup.bak'
with replace, recovery,
move N'MyWebAppTest' to 'C:\MSSQL\DATA\MyWebApp.mdf',
move N'MyWebAppTest_log' to 'C:\MSSQL\DATA\MyWebApp_log.ldf',
move N'MyWebAppTest_FS' to 'C:\MSSQL\DATA\MyWebApp_FS';
go
3. Now finally change the logical name of the files.
In this example we are renaming a existing Test database for Production database by removing the Test suffix from the names. So that is the reason I chose to even change the logical name, but this is not mandatory you may choose to leave the existing logical name without any issues.
Note: In my case I am also using the FileStream functionality of the SQL Server database, and that is the reason instead of two-physical files, I have three-physical files in my backup file. The last one is for the FileStream. As you might be aware FileStream functionality allows the application to store the flat files e.g. attachment files, such as PDF, MS Word, Image files etc. Almost every other application requires the functionality in the application, to allow user to upload / download flat files. If you want to know, how to implement this functionality then leave a comment below.
alter database [MyWebApp] modify file (name=N’MyWebAppTest’, newname=N’MyWebApp’);
go
alter database [MyWebApp] modify file (name=N’MyWebAppTest_log’, newname=N’MyWebApp_log’);
go
alter database [MyWebApp] modify file (name=N’MyWebAppTest_FS’, newname=N’MyWebApp_FS’);
go
Comments
Post a Comment