Need stored procedure in sql 2008 which scripts all the database

Question

Post reply

Need stored procedure in sql 2008 which scripts all the database

rj529

Ten Centuries

Points: 1194
More actions
November 22, 2012 at 3:40 am

#285081

We used the below procedure to script all the Databases in sql server 2000
CREATE Procedure dbObjectBackup
as
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256), @path1 varchar(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @sql VARCHAR(250)
Declare @ServerName Varchar(25)
Declare @exeFile varchar(200)
set @ServerName = 'SQLINST1'
set @exeFile = 'D:\Progra~1\Micros~1\MSSQL\Upgrade\scptxfr.exe'
SET @path = 'D:\ScriptBackup\'
SET @path1 = '\\IP\All_Backup_21102008\IP1\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('msdb','tempdb','Pubs', 'Northwind')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = 'exec master..xp_cmdshell ''' + @exeFile + ' /s ' + @ServerName + ' /I /d ' + @Name + ' /f ' + @path + @Name + '_' + @fileDate + '.sql'''
exec (@sql)
set @sql = 'exec master..xp_cmdshell ''' + @exeFile + ' /s ' + @ServerName + ' /I /d ' + @Name + ' /f ' + @path1 + @Name + '_' + @fileDate + '.sql'''
-- exec (@sql)
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
I need a procedure like this in sql server 2008 which script the whole database with its tables,views,etc..
Thanks in advance.

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

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

Gazareth One Orange Chip Points: 27737 More actions · Answer 1

Have a look at Powershell:

http://www.simple-talk.com/sql/database-administration/automated-script-generation-with-powershell-and-smo/

rj529 Ten Centuries Points: 1194 More actions · Answer 2

In our servers, Server team don't allow us to use Powershell. We have only limited access. So only we are using these kinds of SP's to meet our requirement. Please provide us in SP manner.

Thanks in advance.

Gazareth One Orange Chip Points: 27737 More actions · Answer 3

Well I think it relies on the Script/Scripter method/object from SMO.

You can access it through other ways than Powershell.

Don't know of a way to do it from within SQL.

WWDMark Hall of Fame Points: 3157 More actions · Answer 4

rajkiran.panchagiri (11/22/2012)

In our servers, Server team don't allow us to use Powershell. We have only limited access.

That's quite interesting, is there a reason that they don't allow powershell as this can solve a multitude of issues?

MCITP
MCTS - E-Business Card
Twitter: WWDMark

Try not! Do or do not, there is no try

email: info@weekendwebdesign.co.uk
Personal Website: http://markallen.co.uk/
Business Website: https://www.weekendwebdesign.co.uk