Wednesday 16 May 2012

SQL Backup and Restore.. any version

GUI simplified and short.....

Backup Database:
in this example citrix CAG database (qdc-ctx-aac) is explained.
Rt. click from the DB name to be backed up. select tasks>>backup
select destination disk. click Add button to select destination on disk.
(location C:\CitrixLive\BKP\QDC-CTX-AAC.bak)
under options verify the required selections. under general selection make sure the backup type is Full. and then click ok.
Restoration  to a different SQL Server from SQL 2005 to SQL 2008 or Denali (SQL 2012)
(same or different OS versions- 32 or 64bit doesn't really matter)
select source for restore.. from device.. click add.. select the location.. (in our case it is C:\CitrixLive\BKP\QDC-CTX-AAC.bak) and click ok.
select restore backup set
select the auto-listed database or provide a new name of the database you need to restore with.. in our case qdc-ctx-aac.
select options page
select restore options
and check the box Overwrite the existing database (with replace) just to make sure you are overwriting a database if that exists, if not no need to select the check box.
restore the database file as:
make sure the data is restored to the write folder; the volume where you actual have space.. can any other drive where you have space. avoid keeping database in C Drive (OS drive). for better performance make Data (.mdf) and Log file (.ldf) in independent drives (if you have more partitioned volumes).
click ok.
migration of database part is almost done from SQL 2005 to SQL 2008 or to SQL 2012.
finally Rt. click and select properties of the particular DB.
select Options page.. make sure recovery model is set to Full... and select the compatibility level is set to SQL Server 2008(100)
 
and then click OK.
setting the permissions is important for the restored database. Existing authorized user will be automatically inherited. if you need to change the permission to a different user, at this instance you need to do it to avoid CAG communication errors.
in our case we gave dB ownership privilege.
that's it.
now you can go back to reconfigure CAG DB move.

0 comments: