backup sql database
In this post, I will explain scenarios of taking backup of entire SQL server data bases and restorng it on other servers.
You want to migrate all Data bases, Jobs and Linked server configuration to other upgraded machine.
1. set same user name and password which was used for old server.
2. Stop old as well as new SQL servers.
3. Copy folder C:\Program Files\Microsoft SQL Server\MSSQL.1 and paste it to new upgraded SQL server at same location.
4. Copy all data bases and paste it into new server with same folder structure as of old sql server.
Now, you have same copy of Data base, jobs and linked servers.
If your server is being accessd by other servers as a linked server, you need to interchange the IP addresses.
Start new SQL server and try to test connection of linked servers.
You will come up with below error
" An error occurred during encryption "
To overcome this, you need to start the old SQL server and take a backup of master key by firing below command
BACKUP MASTER KEY TO FILE = 'c:\old.bak'
ENCRYPTION BY PASSWORD = 'sd092735kjn$&adsg';
copy old.bak to new server on c: drive. Now, you need to restore this key in your new SQL server.
Fire below query to use new master key for your new sQL server.
RESTORE MASTER KEY
FROM FILE = 'c:\old.bak'
DECRYPTION BY PASSWORD = '3dH85Hhk003#GHkf02597gheij04'
ENCRYPTION BY PASSWORD = '259087M#MyjkFkjhywiyedfgGDFD';
Now, you can start the Jobs running of new server.
msdb holds the information of jobs created on sql server.