backup sql database

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.

Steps:

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.

Satalaj.













 

Tags:

The shrink operation is not duplicated on the mirror database when you use database mirroring in SQL Server 2005backup sql databaseSql Server Mirroringsql server agent backupDatabase mirroring is currently provided for evaluation purposes only and is not to be used in production environments To enable database mirroring for evaluation purposes use trace flag 1400 during startupLinq to SqlHTML5 With DataBaseMS SQL server shutdownConnection PoolingIIS backup Disaster action planasp net image upload

Author

My name is Satalaj, but people call me Sat. Here is my homepage: . I live in Pune, PN and work as a Software Engineer. I'm former MVP in ASP.net year 2010.
Disclaimer: Views or opinion expressed here are my personal research and it has nothing to do with my employer. You are free to use the code, ideas/hints in your projects. However, you should not copy and paste my original content to other web sites. Feel free to copy or extend the code.
If you want to fight with me, this website is not for you.
 

I'm Satalaj.