The steps below detail how to backup and restore your SQL database using SQL Server Management Studio.
📌Note:
The tasks below are usually done by IT or an experienced system administrator.
All screenshots below are taken from SQL Server Management Studio 2017. Your version might vary slightly.
Backup a Database in SQL Server Management Studio
Open SQL Management studio.
Enter your Server Name.
Login using Windows or SQL authentication.
Expand on the Databases folder.
Right click the aps_dsql database.
Select Tasks, Back Up.
Ensure that the source database is aps_dsql (your APS database).
Ensure the Back up to field value is Disk.
Click Add.
Click Ellipsis to select a backup location.
Find the location you wish to save the backup file.
Click OK.
The backup will execute.
Click Ok on the success message. Your backup has completed successfully and can be found in the folder location you specified during your backup.
Restore a Database in SQL Server Management Studio
Step 1 - Restore the database
Open SQL Management studio.
Enter your Server name.
Login using Windows or SQL authentication.
Select the Databases folder and click the + sign.
Find your APS database (usually called aps_dsql) then right-click on it and select Delete. 📌Note:
make sure you have a reliable backup before doing this.
Confirm that no-one is logged into APS.
Tick both boxes at the bottom of the Delete Object dialogue box then press OK.
Click New database.
Enter the new database name eg: aps_dsql.
Click OK.
Right click on the new database.
Select Tasks, Restore, database.
Select the source as Device.
Click the Ellipsis.
Click Add.
Browse to the backup of the database.
Select the backup.
Click OK.
Click OK.
In the Backup sets to restore table, tick the item to restore, then select Options.
Tick to Overwrite the existing database (WITH REPLACE), and click OK.
Select Files, ensure the file paths are correct, then click OK.
The restore will commence and you can follow the progress of the restore.
Click OK when the restore successfully completes.
Step 2 - Security settings (Optional)
Once the database is restored check that the admin user exists in the server security.
Below steps take you through how to setup the admin user.
Go to Security, Logins.
Right-click and select New login.
Enter the details for the Admin user.
Select Server Roles.
Tick the Sysadmin role and click OK.
The Admin user is now set up correctly.
Once the user ‘admin’ has been created, you can now assign the restored database to the user admin of the server.
Click New Query.
Type in SP_ChangeDBOwner admin into the query window. Ensure that you are running the script against the aps_dsql .
Click the ! to execute the query.