Stored Proc to create script of Database
-
I need to create a stored procedure that will automatically script all databases to separate files, but I'm not having any luck. The only way I can do this is manually. We want to be able to save the file to a drive that is backed up and then be able to update it periodically by scheduling a stored proc. Any ideas?
-
SQL Server 2000 ships with a scripting utility which can be run from a job.
Here is sample code we run nightly for an "ever-changing" test database:
declare @command varchar(1000)
declare @texttime varchar(10)
SET @texttime = CONVERT(varchar, GETDATE(), 102)
SET @command = '"c:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe"'
SET @command = @command + ' /s <servername> /d <Databasename> /I /F c:\temp\' + @texttime + ' /r'
PRINT @command
exec master..xp_cmdshell @command
It will generate a separate script for each database-object-type in a folder
named yyyy.mm.dd
-
I tried the command exactly like this:
declare @command varchar(1000)
declare @texttime varchar(10)
SET @texttime = CONVERT(varchar, GETDATE(), 102)
SET @command = '"d:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe"'
SET @command = @command + ' /s <servername> /d <Databasename> /I /F J:\DB Scripts\' + @texttime + ' /r'
PRINT @command
exec master..xp_cmdshell @command
d: is the only place I found the scptxfr.exe file. J:\ is where I want the files to go. Is there something else I need to change?
I receive the output:
1 The system cannot find the file specified.
2 NULL
I'm wondering if this is different because I am on a cluster system and there are 2 instances running.
-
replace with your server name (SELECT @@servername)
and with the db you want to create thes cripts for (SELECT DB_Name())
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply