Step 1: Database Location
- Right click Database, select Properties
- Files Page
- Note Path and File Names of the database and log files
Step 2: Detach Database
- Right click Database, select Tasks, select Detach
- Click OK on the following screen:
Depending on SQL Server version, the database detachment process will vary.
SQL Server 2012 the screen disappears after clicking OK.
Newer version of SQL Server prompt location to Save the Database and Log File. If applicable, select any new folder location to save the files and note this file location for Step 4.
Step 3: Kill Active Connections
This step needs to be done only if there are Active Connections preventing the database from being detached.
New Query: sp_who returns all connections to the database engine. Note the spid related to the database to be detached.
Execute Query: kill [spid]
Result will kill any process id. Run the command above as many times as necessary to force close any connections to the database.
Repeat Step 2 to detach the database now that the connections are closed.
Step 4: Upload Files
Browse to the location of the detached Database and Log files.
Upload to a secure site of choice, such as FTPS, DropBox, or other file sharing service. Provide file access instructions to Scanco.
Note: If files were exported from Step #2, select the new file location the database and log file was detached to instead.
SQL Backups Interfere with SQL Logs
SQL Backups will delete parts of the log file that are necessary to troubleshoot problems with transactions. Thus, it is critical the above steps to detach database are taken during the following time:
- AFTER the issues have been encountered or noticed
- BEFORE the next backup takes place
This typically means end of business day after hours.