| Both sides previous revisionPrevious revisionNext revision | Previous revision |
| wiki:backuppc_mssql [2015/06/03 21:33] – jrdalrymple | wiki:backuppc_mssql [2018/09/27 02:29] (current) – external edit 127.0.0.1 |
|---|
| |
| - Create a folder somewhere on the server, I use a folder called "dbbackups" in the root of the C drive, it doesn't matter what it's called or where it is as long as there is enough free space to save the entirety of your SQL dumps there | - Create a folder somewhere on the server, I use a folder called "dbbackups" in the root of the C drive, it doesn't matter what it's called or where it is as long as there is enough free space to save the entirety of your SQL dumps there |
| - Change the permissions to be very restrictive on that folder, only the unprivileged windows user performing the backup from the SQL steps above needs access. | - Change the permissions to be very restrictive on that folder, only the unprivileged windows user **and the user that is running the SQL service** (find in services.msc) need access. |
| - We need 2 files in that folder\\ <file DOS backup_dbs.cmd> | - We need 2 files in that folder\\ <file DOS backup_dbs.cmd> |
| erase "c:\dbbackups\*.bak" | erase "c:\dbbackups\*.bak" |
| sqlcmd -E -i "c:\dbbackups\backupdbs.sql"</file><file DOS backupdbs.sql> | sqlcmd -E -i "c:\dbbackups\backupdbs.sql"</file>If you aren't using the default instance of SQL Server on your Windows host you may need to add something like<code>-S .\MYINSTANCE</code>to the sqlcmd line. Incidentally you may have more than one of the following .sql files and more than one sqlcmd to run if you're needing to backup multiple running SQL instances on one host.<file DOS backupdbs.sql> |
| BACKUP DATABASE [DATABASE_1] TO DISK = N'c:\dbbackups\DATABASE_1.bak' WITH NOFORMAT, NOINIT, NAME = N'DATABASE_1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 | BACKUP DATABASE [DATABASE_1] TO DISK = N'c:\dbbackups\DATABASE_1.bak' WITH NOFORMAT, NOINIT, NAME = N'DATABASE_1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 |
| BACKUP DATABASE [DATABASE_2] TO DISK = N'c:\dbbackups\DATABASE_2.bak' WITH NOFORMAT, NOINIT, NAME = N'DATABASE_2-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 | BACKUP DATABASE [DATABASE_2] TO DISK = N'c:\dbbackups\DATABASE_2.bak' WITH NOFORMAT, NOINIT, NAME = N'DATABASE_2-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 |
