SQL Server database restore from one server to another server
Introduction
Restoring a SQL Server database from one server to another is a common administrative task that ensures business continuity, supports migrations, or facilitates setting up a testing environment. This document provides a comprehensive, step-by-step guide for database administrators (DBAs) to successfully move a SQL Server database from one instance to another while preserving users, roles, and permissions. By following this process, you can ensure the integrity and usability of the restored database on the target server.
1 Title/ Description of the Process
Move a SQL Server database from one server to another.
To provide a step-by-step guide for moving a database from one SQL Server instance to another.
This process applies to any SQL Server database requiring relocation between servers.
| Role Name | Responsibility | ||
|
Execute the database move process as per this guideline. |
- SQL Server Management Studio (SSMS) installed.
- Credentials with administrative rights.
- Sufficient storage space on the destination server
The following steps will help to move a single database from one server to another server
1,1
– Take a full backup of the database
– Log in to the server itself and make a new folder to move the backup
– Remove any existing paths and add the new location to move the backups
-Based on the database size consider taking a compress backup
1.2
Take login and permission backup
Database à Security à users à R + Click on username à script to
–
-On the new query window the script will be available to create a login
– Take the all the logins script for the database which will help while restore the database on the new server and to create logins
1.3
-Taking the permission of the database
-Use the below query to take the permission backup
set nocount on
go
select cast(‘if (select user_id(”’ + name + ”’)) is null
exec sp_adduser [‘ + name + ‘]’ as varchar(120)) as [– Add users script]
from sysusers
where islogin = 1
and isntname = 0
and name not in (‘dbo’,’guest’)
union
select cast(‘exec sp_grantdbaccess [‘ + name + ‘]’ as varchar(120)) as [– Add users script]
from sysusers
where islogin = 1
and isntname = 1
and name not in (‘dbo’)
select distinct ‘if (select user_id(”’ + cast(user_name(groupuid) as varchar(50))+ ”’)) is null
exec sp_addrole [‘ + cast(user_name(groupuid) as varchar(50)) + ‘]’ as [– Add roles script]
from sysmembers
where groupuid > 16399
select ‘exec sp_addrolemember [‘ +
cast(user_name(groupuid) as varchar(50)) + ‘], [‘ +
cast (user_name(memberuid) as varchar(50)) + ‘]’ as [– Add role members script]
from sysmembers
where user_name(memberuid) not in (‘dbo’,’guest’)
Output will be like –
– Copy the output after the restore the database this has to be run
1.4
-Move the backup to the target server and restore the database
-Move the backup via share network folder or network link
– R + click on the database à restore database
-Select the path where the backup has been stored
-Proceed with restore the database
1.5
-Once the database is restored
-R+click on the database à New Query
-First create the login one which taken from the old server by running the script of the login
-After creating the login run the permission which was taken from the old server and apply it to the new server
Conclusion
By following this guide, DBAs can efficiently restore a SQL Server database to a new server while retaining all necessary configurations, users, and permissions. This process not only ensures minimal downtime but also maintains the functionality and security of the database on the destination server. Regular testing of this procedure is recommended to ensure smooth execution during actual migrations or restores.
