Backup All Sql Server Databases With a Script
I have some code that runs a nightly backup. Up until today, I was not backing up my developement Sql Server Databases.
I am running Sql Server 2005 Developer Edition which is a full version of Sql Server with limited licence.
I am also running Sql Server 2008 Express Edition.
I know that I can turn on Sql Agent and create a maintenance plan that would backup all of the datbase on the Sql Server 2005 instance.
However, the Express Edition does not come with Sql Server Agent so I can't use that to backup my 2008 databases.
I decided to modify my backup code to backup all my Sql Server databases.
First thing is to create the .sql file with TSQL commands. Here it is:
--------------------------
--VARIABLE TO HOLD DB INFO
--------------------------
DECLARE @DatabaseName VARCHAR(255)
DECLARE @DatabaseBak varchar(500)
DECLARE @RootBackupFolder varchar(500)
set @RootBackupFolder = 'E:\Docs\MsSqlBak\'
---------------------
--DEFINE THE CURSOR
---------------------
DECLARE DatabaseCursor CURSOR FOR
SELECT
name
FROM
master.dbo.sysdatabases
WHERE
name NOT IN ('master','model','msdb','tempdb','distrbution')
ORDER BY 1
---------------------------------------
--OPEN CURSOR, GET THE FIRST DATABASE
--------------------------------------
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @DatabaseName
------------------------
--LOOP THROUGH DATABASES
------------------------
WHILE @@FETCH_STATUS = 0
BEGIN
--------------------
--WRITE TO CONSOLE
--------------------
PRINT '--------------------------------------------------------------'
PRINT 'BACKING UP DATABASE ' + @DatabaseName
PRINT '---------------------------------------------------------------'
------------
--BACKUP DB
------------
set @DatabaseBak = @RootBackupFolder + @DatabaseName + '.bak';
BACKUP DATABASE @DatabaseName
TO DISK = @DatabaseBak
WITH INIT
PRINT ''
------------
--GET NEXT
----------
FETCH NEXT FROM DatabaseCursor INTO @DatabaseName
END
------------
--CLEAN UP
-----------
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
This type of backup is just a full backup that will overwrite the previous backup.
Then in my console application that does the backup, I put this function:
static void BackupSql(string strServer)
{
//*****************
//WRITE TO CONSOLE
//*****************
Console.WriteLine("Backing up SERVER: " + strServer);
Console.WriteLine();
//***********************************************************************
//BUILD THE PROCESS INFO, RUN SQLCMD.EXE
//PASSING IN THE PATH TO THE FILENAME AND THE NAME OF THE SERVER INSTANCE
//***********************************************************************
string strScriptFile = ConfigurationManager.AppSettings["dbScriptFile"];
string strSqlCmdExe = "SqlCmd.exe" ;
string strArg1 = "-S \"" + strServer + "\"";
string strArg2 = " -i " + strScriptFile;
ProcessStartInfo info =
new ProcessStartInfo(strSqlCmdExe, strArg1 + strArg2);
info.UseShellExecute = false;
Process p = new Process();
p.StartInfo = info;
p.Start();
p.WaitForExit();
}
A few notes:
-
I have an App.config file that contains a dbScriptFile key which contains the fully qualified path to the file I created above.
-
I call this procedure twice, once for each Sql Server instance, passing in the name of the instance.
-
I could not get the code to work until I put quotes around the Server name as you see in the code above.
-
If you don't read in the path to your script file from the App.config file, you will have to make sure to escape the backslashses as in:
strScriptFile = "C:\\SqlBackupFile.sql
or
strScriptFile = @"C:\SqlBackupFile.sql"
-
If your Servername contains backslashes, then you will have to escape those backslashes as well with one of the two techniques noted above.
I hope that helps.