Simple script to backup all SQL Server databases

ProblemSometimes things that seem complicated are much easier then you think and this is the power of using T-SQL to take care of repetitive tasks.  One of these tasks may be the need to backup all databases on your server.   This is not a big deal if you have a handful of databases, but I have seen several servers where there are 100+ databases on the same instance of SQL Server.  You could use Enterprise Manager to backup the databases or even use Maintenance Plans, but using T-SQL is a much simpler and faster approach.
SolutionWith the use of T-SQL you can generate your backup commands and with the use of cursors you can cursor through all of your databases to back them up one by one.  This is a very straight forward process and you only need a handful of commands to do this. 
Here is the script that will allow you to backup each database within your instance of SQL Server.  You will need to change the @path to the appropriate backup directory and each backup file will take on the name of "DBnameYYYDDMM.BAK".

DECLARE @name VARCHAR(50) -- database name  DECLARE @path VARCHAR(256) -- path for backup files  DECLARE @fileName VARCHAR(256) -- filename for backup  DECLARE @fileDate VARCHAR(20) -- used for file name SET @path = 'C:\Backup\'  SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) DECLARE db_cursor CURSOR FOR  SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')  OPEN db_cursor   FETCH NEXT FROM db_cursor INTO @name   WHILE @@FETCH_STATUS = 0   BEGIN          SET @fileName = @path + @name + '_' + @fileDate + '.BAK'         BACKUP DATABASE @name TO DISK = @fileName         FETCH NEXT FROM db_cursor INTO @name   END   CLOSE db_cursor   DEALLOCATE db_cursor

In this script we are bypassing the system databases, but these could easily be included as well.  You could also change this into a stored procedure and pass in a database name or if left NULL it backups all databases.  Any way you choose to use it, this script gives you the starting point to simply backup all of your databases.

Working with Stored Procedures in Sql server

Working with Stored Procedures

"a precompiled collection of SQL statements and optional control-of-flow statements stored under a name and processed as a unit". Stored procedures allow a  more flexibility offering capabilities such as conditional logic .Bandwidth and execution time are reduced because stored procedures are stored within the DBMS,.  because a single stored procedure can execute a complex set of SQL statements. 

 SQL Server pre-compiles stored procedures such that they execute optimally and  client developers are abstracted from complex designs. They would simply need to know the stored procedure's name and the type of data it returns. 
 
To add a new stored procedure to the Northwind Microsoft SQL database, perform these steps.

1. Load SQL Server Enterprise Manager, found in the Microsoft SQL Server program group.
2. Expand the tree "Microsoft SQL Servers," "SQL Server Group," and locate your database server. With SQL installed locally, the default server will be (LOCAL
3. Locate and expand the Student database. Select Stored Procedures. On the right side of the screen, you'll see any default stored procedures included with the student database.
4. Right-click anywhere on the right pane of the Enterprise Manager and select New Stored Procedure.
5.  

Simple  insert Stored Procedure

use studentcreate procedure studentinsertproc @stdid text,@subid text,@subname text,@mark int as insert into student (stdid ,subid,subname,mark)values(@stdid,@subid,@subname,@mark) studentinsertproc '10001','101','computer graphics',85 studentinsertproc '10002','101','computer graphics',65, studentinsertproc '10003','101','computer graphics',75 studentinsertproc '10004','101','computer graphics',55 studentinsertproc '10005','101','computer graphics',85 studentinsertproc '10006','101','computer graphics',87 select * from student

5)select and press F5