Create a SQL Database Backup
While regular backup and maintenance of SQL servers should be a policy already in place at setup and install, sometimes situations call for creating a backup of a database on the fly.
There are multiple ways to do this but here are a few examples of how to create and restore a database backup.
- Create a folder labeled with a name and date ie: Backup_110717 in directory D:\SQL_DSP<XX>\ (‘XX’ stands for DSP instance number)
- Log in to the SQL instance that the backups will be taken from in SQL Server Management Studio.
- Open a new query window
- Use this format to create on the fly database backups:
USE <databasename>;
GO
BACKUP DATABASE <databasename>
TO DISK = '<D:\SQL_Backup\FolderName\databasename>_backup.bak>'
WITH FORMAT,
MEDIANAME = '<Databasename>_backup',
NAME = 'Full Backup of <Databasename>';
GO
<> is to indicate as a template and should be removed when used. The disk location can be anywhere SQL server has access to.
An alternative method if needed is to take a backup of a mass number of databases.
Steps 1-3 are the same but instead using this t-SQL
SELECT --name,
'BACKUP DATABASE ' +
QUOTENAME(name) +
' TO DISK = ''<D:\SQL Server\>' +
name +
'.bak'' WITH NOFORMAT, NOINIT, NAME = ''' +
name +
'-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10' +
CHAR(10) +
CHAR(13) +
' GO ' +
CHAR(10) +
CHAR(13)
FROM
sys.databases
WHERE
database_id > 4 AND
name NOT IN 'dgSAP'
- This will provide code for all the databases specified when executed.
- Copy the results, open a new query window and execute.
Restore from a Backup
- Ensure all users are logged off the system
- Pause the SQL databases or take them off line if possible (Not doing this may cause the restore process to fail if database is in use)
- Use the SQL below to restore specified backup.
- Ensure databases are back online when completed.
RESTORE DATABASE <Database>
FROM DISK = 'D:\SQL_DSP<XX\FolderName\Database_backup>.bak'
WITH
REPLACE, -- Overwrite DB - if one exists
RECOVERY, -- Use if NO more files to recover, database will be set ready to use
STATS = 10, -- Show progress (every 10%)
MOVE '<Database>' TO 'D:\SQL_DSPXX\MSSQL12.DSPXX\MSSQL\DATA\<Database>.mdf',
MOVE '<Database_log>' TO 'D:\SQL_DSPXX\MSSQL12.DSPXX\MSSQL\DATA\<Database_log>.ldf'
Comments
0 comments