|
Execute the following
Microsoft SQL Server T-SQL example scripts in SSMS Query Editor to create and execute a stored procedure for backup up all databases.
-- SQL Server full database backup basic syntax
BACKUP DATABASE [AdventureWorks2008] TO DISK = N'F:\data\backup\aw8.bak'
GO
-- SQL Server full database backup with options
BACKUP DATABASE [AdventureWorks2008] TO DISK = N'F:\data\backup\aw8.bak'
WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2008-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
------------
-- SQL Server transaction log backup basic syntax
BACKUP LOG [AdventureWorks2008] TO DISK = N'F:\data\backup\aw8_20120201_1430.trn'
GO
------------
-- SQL Server differential backup basic syntax
BACKUP DATABASE [AdventureWorks2008] TO DISK = N'F:\data\backup\aw8.dif'
WITH DIFFERENTIAL
GO
------------
-- SQL Server backup all databases - T-SQL backup database
-- SQL cursor - T-SQL stored procedure
USE AdventureWorks;
GO
CREATE PROC sprocBackupAllDatabases
AS
BEGIN
DECLARE @DatabaseName SYSNAME,
@SQLCommand VARCHAR(1024)
DECLARE curDBName CURSOR FOR
SELECT [name]
FROM MASTER..sysdatabases
WHERE [name] NOT IN ('model','tempdb')
OPEN curDBName
FETCH curDBName
INTO @DatabaseName
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF databasepropertyex(@DatabaseName,'Status') = 'online'
BEGIN
SELECT @SQLCommand = 'backup database ' + @DatabaseName +
' to disk=''e:\data\backup\' + @DatabaseName + '.dmp'' with noinit'
PRINT @SQLCommand
EXECUTE( @SQLCommand)
END
FETCH curDBName
INTO @DatabaseName
END
CLOSE curDBName
DEALLOCATE curDBName
END
GO
-- Execute stored procedure
EXEC sprocBackupAllDatabases
GO
/* Partial messages
backup database ReportServer$SQL2008 to disk='e:\data\backup\ReportServer$SQL2008.dmp' with noinit
backup database ReportServer$SQL2008TempDB to disk='e:\data\backup\ReportServer$SQL2008TempDB.dmp' with noinit
backup database AdventureWorks to disk='e:\data\backup\AdventureWorks.dmp' with noinit
backup database AdventureWorksDW to disk='e:\data\backup\AdventureWorksDW.dmp' with noinit
backup database AdventureWorksLT to disk='e:\data\backup\AdventureWorksLT.dmp' with noinit
backup database AdventureWorks2008 to disk='e:\data\backup\AdventureWorks2008.dmp' with noinit
backup database AdventureWorksDW2008 to disk='e:\data\backup\AdventureWorksDW2008.dmp' with noinit
backup database AdventureWorksLT2008 to disk='e:\data\backup\AdventureWorksLT2008.dmp' with noinit
*/
Related articles:
Simple script to backup all SQL Server databases
Backup all databases on a server
http://www.sqlusa.com/bestpractices/lastdatabasebackupdate/
|
