how to automate script backup of all objects in a database

Question

how to automate script backup of all objects in a database

shanila_minnu

Hall of Fame

Points: 3465
More actions
August 13, 2009 at 3:59 am

#134762

how can we automate the scripting of objects in a server.i found out one code in the following link.
http://codeidol.com/sql/guide-to-sql-server/Administrative-Stored-Procedures/sp_generate_script/
but this is the script for only one specified database. i need to get the script of all the database in the server .can any one help me

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 1

Look up the command sp_msforeachdb. It's considered to be an undocumented command, but it's been around for a very long time with no signs of going away. You can use that to walk through every database in the system.

But, I'm not crazy about it because you can't readily exclude databases. Another way would be to use a cursor. Yes, I said a cursor. You can select the names of databases from sys.databases and join that against an custom table that lists excluded database names (for example). Then you walk through the cursor building dynamic SQL for the backups.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 2

SET NOCOUNT ON

-- declare all variables

DECLARE @sTableName SYSNAME

DECLARE @sSQL sql_variant

DECLARE @sSQL1 Varchar(220)

DECLARE @iRowCount INT

DECLARE @t_TableNames_Temp TABLE

(table_name SYSNAME)

INSERT @t_TableNames_Temp

SELECT name

FROM master..sysdatabases where name not in('northwind','pubs')

ORDER BY name

--Getting row count from table

SELECT @iRowCount = COUNT(*) FROM @t_TableNames_Temp

WHILE @iRowCount > 0

BEGIN

SELECT @sTableName = table_name from @t_TableNames_Temp

SELECT @sSQL ='Backup Database '+@sTableName+' TO DISK=''D:\'+

@sTableName+'.bak'''+' WITH INIT,NOFORMAT,STATS=10'

SELECT @sSQL1 =convert(varchar(220),@SSQL)

PRINT @ssql1

EXEC (@SSQL1)

DELETE FROM @t_TableNames_Temp WHERE @sTableName = table_name

SELECT @iRowCount = @iRowCount - 1

END

SET NOCOUNT OFF

GO

MJ

Sarab_SQLChamp Hall of Fame Points: 3882 More actions · Answer 3

You can also use sp_msforeachdb and still exclude some DBs, heres a script for the same:

EXEC sp_msforeachdb '

IF ''?'' IN ( ''db1'', ''db2'', ''db3'', ''db4'' )

SELECT [ABC], [123]

FROM [?].[dbo].[tabledata]

WHERE FieldName = ''QWERTY'''

Hope it will help you.

Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek

hi_abhay78 SSCertifiable Points: 6265 More actions · Answer 4

This is almost similar :

---------------------------

Use Master

DECLARE @name VARCHAR(50) -- database name

DECLARE @path VARCHAR(256) -- path for backup files

DECLARE @fileName NVARCHAR(256) -- filename for backup

DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

SELECT name,flag=0 into #tempbackup FROM master.dbo.sysdatabases WHERE name NOT IN ('master','model','msdb','tempdb')

set rowcount 1

WHILE (exists(SELECT * FROM #tempbackup WHERE flag=0))

BEGIN

Select @name=name from #tempbackup WHERE flag=0

SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

print @fileName

BACKUP DATABASE @name TO DISK = @fileName

Update #tempbackup set flag=1 WHERE flag=0 and name=@name

END

set rowcount 0

drop table #tempbackup

Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)