Skip to content

Instantly share code, notes, and snippets.

@nkostic
Created November 29, 2012 00:26
Show Gist options
  • Save nkostic/4165829 to your computer and use it in GitHub Desktop.
Save nkostic/4165829 to your computer and use it in GitHub Desktop.
MSSQL Database Insatance Backup sql part
-- =============================================
-- Author: Nenad Kostic
-- Create date: 11/29/2012
-- Description: Backup Instance
-- Big Thanks : Dragan Zdravkovic
-- =============================================
USE MASTER
DECLARE
@filepath nvarchar(500)
,@servername nvarchar(100)
,@currentdatabasename varchar(100)
,@databasename as nvarchar (100)
,@filenamepath as nvarchar (500)
,@backupname nvarchar (100)
SELECT @servername = CAST(SERVERPROPERTY('InstanceName') as nvarchar(128))
SET @filepath = 'D:MyDBBackups\' + @servername + '\'
DECLARE
DATABASENAMES CURSOR
FOR
SELECT
NAME
FROM sysdatabases
WHERE name not in('master', 'tempdb', 'msdb','model')
OPEN DATABASENAMES
FETCH NEXT FROM DATABASENAMES
INTO @currentdatabasename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SET @databasename = CAST(Year(getdate()) as nvarchar)
+ RIGHT('00'+ CAST(MONTH(getdate()) as nvarchar), 2)
+ RIGHT('00'+ CAST(DAY(getdate()) as nvarchar),2)
SET @backupname = @currentdatabasename + '-Full Database Backup'
SET @filenamepath = @filepath + @currentdatabasename + @databasename + '.bak'
BACKUP DATABASE @currentdatabasename TO DISK = @filenamepath WITH FORMAT,
INIT, NAME = @backupname, SKIP, NOREWIND, NOUNLOAD, STATS = 10
FETCH NEXT FROM DATABASENAMES
INTO @currentdatabasename
END
CLOSE DATABASENAMES
DEALLOCATE DATABASENAMES
@nkostic
Copy link
Author

nkostic commented Nov 29, 2012

Set The @filepath to shoot on your desired location, and make sure that you have that folder. You need as well to have folder with your instance name in this folder. (Hence: Instance name = folder name in folder D:MyDBBackups)

@nkostic
Copy link
Author

nkostic commented Nov 29, 2012

Use mssql_db_backup.bat file to invoke this script.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment