Can anyone point me to the right direction with the stored procedure on making a backup of the database. I am not looking for a scheduled backup. I'm looking for when the stored procedure get executed, the backup start right away. I believe it also require a username and password as well.
Thanks...
I am trying to modify a script that to back databsae in t-sql.I put it in a stored procedure with one paramenter dbname.I would like if a dbname is passed when calling the sproc, it will only backup this database, if no paramenter is entered, the dbname is null, then backup all user databases- no system databases.But I have difficulty to define this two situations in code.
Below is the sproc:
CREATE PROCEDURE [dbo].[BackupDB]
@dbname varchar(50)
AS
BEGIN
[code]...
Have a server running 14 production databases, 12 of which are replicated (transactional replication) to a second server for reporting. Distributor on same machine as publishers. All set to 'SyncWithBackup' = true, distribution set to Full recovery mode, log backups every 30 minutes, full backup nightly. This generally runs just fine.
Occasionally, the process 'hangs' indefinitely (has gone 12 hours or more before being caught) and I need to stop the backup job, stop one or more of the log reader agents, and restart everything, and it proceeds just fine. Annoying, but not fatal, and not very often.
This time, no matter what, the backup job hangs when it runs. This is true whether it is the FULL backup or just a Transaction Log backup. It hangs on the stored procedure sp_msrepl_backup_start, at the point where it is attempting to update the table 'MSrepl_backup_lsns'. When it is hung like this, all of the log reader agent jobs are also hung, blocked by this stored proc. I've tried stopping ALL of the log reader agents prior to starting the backup, but the backup process still hangs up at the same spot and never ends.
I can run the select statement in that SP that gathers the data for the databases 'manually' in a query window and it finishes in about 10 seconds. It actually seems to be hung up on the 'UPDATE' statement. When it is hung, I cannot SELECT from MSrepl_backup_lsns unless I append WITH (NOLOCK) to the statement. Nothing else I can find indicates that there is anything else locking that table. DBCC OPENTRAN shows that there is a lock on that table held by that stored proc -- but I can't see any reason why it won't update the table (17 total records) and move on.
As I said, normally this runs just fine. Totally baffled by what may be causing this at this time.
There is a need to make a backup of a database to URL. how to do this.
View 2 Replies View RelatedHi
In my table i have a datetime field
now i want to delete all records there are more than 1 hour old
can someone help me with this
Alvin
Can DTS make a call to a stored procedure on an AS/400 and accept data from that call. I need to access the AS/400 through OLE/DB for AS/400, execute the call to a stored procedure (the AS/400 stored procedure gets the data from DB2/400, executes some business logic, then presents the record set), and grab the record set returned and dump it into a SQL 7.0 table.
View 1 Replies View RelatedI have a bulk insert script. I have a stored procedure. I saved the stored procedure as a .sql file in another folder too. I have another program that can "run external program" and the only files it will run is a .bat or .exe. I want my other program to be able to trigger the stored procedure to run. I think this means I need a .bat file.
Here is my stored procedure:
USE [EricaTraining]
GO
/****** Object: StoredProcedure [dbo].[LoadDailyAdjReport] Script Date: 03/29/2013 10:56:42 ******/
SET ANSI_NULLS ON
GO
[code]....
Hi,
I have created a stored procedure that will read the content of the text files of a particular folder. I need to make the stored procedure to run daily so that it will read the new files that is present in that folder. I have written a stored procedure to make the process of reading the file. But i need to know how to make the stored procedure to run daily so that it will automatically read all the files. I have got the information that it can be made possible using dts package. As i dont have any knowledge about dts package can anyone help me how to make this possible.
Thanks in advance for any help.
Regards,
Sangeetha
Basically, I'm working on a stored procedure which will retrieve data based on study parameter passed. The datasource is 'Views'. The name of the view is same for every study except that there is corresponding study name included. For example the views names are something like this for study abc 'v_abc_form' and for study def 'v_def_form'.
Below is the select statement I'm trying to use by declaring @study variable but not able to succeed. I'm not sure how to make the table name dynamic.
Select C1, C2, C3
From v_@study_form
Hello.
I need to quickly make this proc compatible with SQL 2005 and am struggling. I have alot of catching up to do.
Basically, it checks for Foreign Key dependencies in a database. There might be a better way to do this in SQL 2005 but for know I really need to get this working. Any help is verry much appreciated!
--------------------------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER Procedure aes.Check_Dependent_Rows_Exist
(@RowID int,
@has_rows int OUTPUT
)
AS
BEGIN
DECLARE @Colname varchar(200), @Tablename varchar(200)
DECLARE @cnt int
DECLARE @temp_row int
DECLARE @owner varchar(25)
DECLARE @ownerid int
DECLARE @lstrSql nvarchar(2000)
-- #1: declare cursor for maximum performance
DECLARE lcur CURSOR LOCAL FORWARD_ONLY KEYSET READ_ONLY FOR
SELECT syscolumns.Name, OBJECT_NAME(fkeyid) AS FkeyTableName
FROM sysreferences
INNER JOIN syscolumns ON sysreferences.fkeyid=syscolumns.id AND fkey1=syscolumns.colid
WHERE OBJECT_NAME(rkeyid)= 'customer'
OPEN lcur
CREATE TABLE #Temp (DependentRows int)
-- #2: only return a bit indicating if dependant rows exist or not
SET @has_rows = 0
FETCH NEXT FROM lcur INTO @Colname,@Tablename
WHILE @@FETCH_STATUS = 0
BEGIN
SET @temp_row = 0
SELECT @ownerid = uid from sysobjects where name = @Tablename
SELECT @owner = [name] from sysusers where uid = @ownerid
SET @lstrSql= 'insert into #Temp Select DependentRows = Count(' + @Colname + ') from ' + @owner + '.' + @TableName + ' where ' +
@Colname + ' =' + CAST(@RowID AS VARCHAR(16)) + ''
--print @lstrSql
EXEC (@lstrSql)
SELECT @temp_row = ISNULL(DependentRows,0) FROM #Temp
IF @temp_row > 0
BEGIN
-- #3: stop processing as soon as dependant rows are found to exist
SET @has_rows = 1
BREAK
END
FETCH NEXT FROM lcur INTO @Colname,@TableName
END
deallocate lcur
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-----------------------------------------------------------------------
error
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.order_detail'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.invoice_header'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.order_header'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.payment'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.cash_on_account'.
(1 row(s) affected)
Cannot grant, deny, or revoke permissions to sa, dbo, information_schema, sys, or yourself.
hi,
I need to convert a stored procedure in to a SSIS package, do any body have an idea on this. thanks in advance
I need im my aplication to meke a "Cursor" in a execution of a stored procedure.
For sample:
In a table with a report definition I have the "Fileds, From, Group, Order " clausulas and I need make a cursor with a contents of this fileds.
How can I do ???
My code:
Declare @idRelat int, @cmd_FROM nvarchar(1024), @cmd_Det nvarchar(50)
SELECT @idRelat = idRelat, @cmd_Det = cmd_DET
FROM Relatórios WHERE Nome = @p_Relat
Declare @Tot_Col smallint, @Tot_Lin smallint, @Campos smallint,
@Aux_Select nvarchar(1024), @Aux_Group nvarchar(1024), @Aux_Order nvarchar(1024)
Select @Tot_Col = 0
Select @Tot_Lin = 0
Select @Campos = 0
Select @Aux_Select = "SELECT " + @cmd_DET + "AS Soma"
Select @Aux_Group = "GROUP BY "
Select @Aux_Order = "ORDER BY "
Declare @a_Local char(1), @a_Linha smallint, @a_Campo nvarchar(50)
Declare cur_Aux insensitive cursor for
SELECT Local, Linha, Campo
From Relatórios_Margens
WHERE (idRelat = @idRelat)
ORDER BY Local, Linha
Open cur_Aux
Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo
While @@FETCH_status = 0 begin
If @a_Local = "C"
Select @Tot_Col = @Tot_Col + 1
Else
Select @Tot_Lin = @Tot_Lin + 1
Select @Campos = @Campos + 1
If @Aux_Group <> "GROUP BY " begin
Select @Aux_Group = @Aux_Group + ", "
If @Aux_Order <> "ORDER BY " begin
Select @Aux_Order = @Aux_Order + ", "
Select @Aux_Select = sSelect + ", " + @a_Campo + " AS Campo" + @Campos
Select @Aux_Group = @Aux_Group + @a_Campo
Select @Aux_Order = @Aux_Order + @a_Campo
Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo
End
Select @Aux_Select = @Aux_Select
-- <<<< MONTA COMANDO SQL
Select @Aux_Select = @Aux_Select + " " + @cmd_FROM + " " + @p_Filtro + " " + @Aux_Group + " " + @Aux_Order
Declare @Cursor_Aux cursor
Set @Cursor_Aux = cursor for @Aux_Select
Open @Cursor_Aux
Not working !!!!
Hi ~
I made simple stored procedure that is to update user information following as...
ALTER PROCEDURE UpdateUserProfile( @user_id uniqueidentifier, @user_firstname nvarchar(50), @user_lastname nvarchar(50), @user_birth nvarchar(20), @user_gender nvarchar(20) )
AS
UPDATE user_profile SET user_firstname = @user_firstname, user_lastname = @user_lastname, user_birth = @user_birth, user_gender = @user_gender WHERE user_id = @user_id RETURN
When I tried to save this procedure, I faced on "Invalid Object : UpdateUserProfile" error message.
What's the problem ?
Hallo !I have a Table with a column "ordernumber"ordernumberA12A45A77A88Is it possible to create a stored procedure which makes a string of these column ?Result: string = ('A12','A45','A77','A88')Thanks !aaapaul
View 3 Replies View RelatedI would like SQL Server 2000 to distinguish between uppercase and lowercase letters, but only within a single stored procedure. Also, at the end of the sp, I want the original collation to be restored. How will I implement this in my sp?
View 3 Replies View RelatedIs it possible to execute a stored procedure in one database, which thenitself executes a stored procedure from another database? We have decide tosplit our data into a tree structure (DB1) and data blobs (DB2) (we areusing MSDE and we have a 2gb limit with each DB so we've done it this wayfor that reason). I would like to, say, execute a stored procedure in DB1,passing in the data blob and other details, DB1 will create a tree node inDB1 and then add the blob record to DB2. DB1 will wrap in a transaction ofcourse, as will DB2 when it adds the blob. Is this possible?
View 1 Replies View Related
Hi there
I just want to create Stored Procedure which will do following thing
i) Create a text file, which will have a Schema Script(including all the constraint & others, for a database(by passing Parameter)
ii) the same file must also have, all the data from the selected database tables,in again plain text format (could be CSV, or Tab SV, etc...)
I mean to say, the file should be act as the full backup of database, in plain text.
The same need to be done in the reverse order,this SP will do following things
i)Open the file created by first SP, create new database, with same name as backup in SP one
ii) create the tables, by reading the Schema script.
iii) Insert all the data in corrsponding tables.
For EX:
--creating backup
sp_CreateDatbaseBackup('DatabaseName')
--Restoring
sp_RestoreDatabaseBackup('FileName')
I know abou the Backup & restore in SQL Server 2005, but they gives me backup as .bkp file which is not editable. I too cant use the SSIS(just only SPs)
I want an editable full Backup and Restore.
Please help me in creating these SPs or any other idea, solution ?
OR
any other FREE third party tool which will make the Full database backup & restore in one go & in plain text file.
Regards,
Thanks.
Gurpreet S. Gill
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Thanks in advance
Please Any one give me the Solution for taking DataBase Backup using stored procedures.
Thanks in Advance for Reply
Please give me the Solution....
Hello,
I created a test stored procedure in MS SQL 2000. When the problem in my app was fixed, I deleted this test stored procedure. But the backup job thinks this procedure still exist and so backup job would failed. How can I fix this problem?
Thank in advance for your assistance.
--------------------------------------------------------------------------------
hi
i found the procedure to take database backup as
CREATE Procedure Backup_Database
(
@dbName varchar(100),
@Path varchar(200) = '' OUTPUT
)
As
Begin
Declare @Now varchar(100)
if Isnull(@dbName,'') = ''
Begin
RaisError('No Database Name specified while running the procedure',16,1)
return
End
if isnull(@path,'') = ''
Begin
RaisError('No file Name specified while running the procedure',16,1)
return
End
-- Make the filename
SELECT @Now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), '-', ''), ' ', ''), ':', '')
Select @path = @Path + '' + @dbName + '_' + @Now + '.Bak'
-- Take the backup of the database at the specified folder
Backup Database @dbName
To Disk = @Path
With Init
if @@Error <> 0
Begin
RaisError('Error Occurred while taking the Backup, Please check Error Log for Details',16,1)
return
End
Print 'Backup Taken Successfully at ' + @path
-- RETURN @path
End
GO
and iam executing procs as follows
exec Backup_Database 'Malathi','C:/New Folder (2)'
above executaion gives error as follows
Cannot open backup device 'C:New Folder (2)Malathi_20070502170331.Bak'. Operating system error 3(The system cannot find the path specified.).
BACKUP DATABASE is terminating abnormally.
Error Occurred while taking the Backup, Please check Error Log for Details
Malathi Rao
I get the following error in a log file created in the osql command:
Msg 3201, Level 16, State 1, Server KAC2KGS2, Procedure usp_Kaman_Full_SqlDB_Backup, Line 150
Cannot open backup device 'x:Servername_master_sqlbu_200703101930.bkf'.
Device error or device off-line. See the SQL Server error log for more
details.
Msg 3013, Level 16, State 1, Server KAC2KGS2, Procedure usp_Kaman_Full_SqlDB_Backup, Line 150
BACKUP DATABASE is terminating abnormally.
The device is established in a CMD file right before the osql command is started that starts my stored procedure. The CMD in this file is:
for /f "tokens=15 delims=." %%i in ('ipconfig^|find "IP Address"^|find "192.168"') do set SUBNET=%%i
:loop
if exist x: net use x: /del
net use x: \192.168.%SUBNET%.1ackup
if not ERRORLEVEL 1 (
goto continue
) else (
echo FAILED TO CONNECT TO BACKUP SERVER >> "%SystemDrive%LogFiles\%Computername%.log"
sleep 60
goto loop
)
:continue
echo IP ADDRESS OBTAINED
Echo delete old log file if it exists
if exist %3\%computername%_kaman_full_sqldb_backup_old.log del /Q %3\%computername%_kaman_full_sqldb_backup_old.log
Echo Rename log file to old.log
rename %3\%computername%_kaman_full_sqldb_backup.log %computername%_kaman_full_sqldb_backup_old.log
echo backup SQL Databases on server will start now
osql -E -n -d %1 -i %2kaman_full_sqldb_backup.sql -h-1 -o %3\%Computername%_kaman_full_sqldb_backup.log
In the stored procedure I try to use the x: drive and that does not work. I have tried obtaining the \192.168.x.x address and that only works on some of my servers. All are running SQL 2000, some are using Win2K and Win2003. It does not seem to matter. One of them that is using Win2003 only fails occationally.
I notice when I do a
exec master..xp_cmdshell 'x:'
The system cannot find the drive specified.
Yet, when I go to the server there is an x drive.
I am using the sqlserv user to run the job and that use is an administrator on the local machine. This is a virtual machine. We add the X before the osql and drop it after the command finishes.
Any help would be appreciated. Thanks, dbmsql
Hello!
I would like to backup a database through t-sql into a file.
I do this :
EXEC sp_addumpdevice 'disk', 'mydb','c:mydb.bak'
it tells "Command(s) completed successfully."
but I can not find the file c:mydb.bak ...
but if I do it again I get :
"Msg 15026, Level 16, State 1, Procedure sp_addumpdevice, Line 74
Logical device 'mealmanager' already exists."
:-(
Any idea? I simply want to get a .bak file of the db, that's all.
Thanks a lot for any feedback!
Regards,
Fabianus
my favorit hoster is ASPnix : www.aspnix.com !
Hi ,Can we make a backup from a SQL-server 2000 database programmatically, using asp.net and then restore this backup programmatically ?
View 4 Replies View RelatedHi all;
Is it possible to make a backup from MSDE ?? (Y/N)
The problem: I am going to develop an application for a small company and it don't like to spend more $$ rather than some $$ for the App.
So, I don't know if it is possible to do a backup from MSDE or not?
BTW: How much MS SQL Sever200 will cost per a PC? (any idea even approx.)??
Thanks in advanced!
~~CS4Ever~~
What is the best way (short of backing up the entire DB) to make a copy of a Table so that It can be easily restored. We have a table that we want to make some serious changes to, but I want to make sure I can restore if if I need to (if the changes don't work)
View 10 Replies View RelatedHi.I am trying to automatically backup transaction log when error 9002happened. So i have created appropriate job and alert to catch this error.I have two instances of sql server under Windows 2000. One of them is fullSQL Server, another is msde.When transaction log is full in full SQL Server error 9002 severity 17state 2 is logged in sql server log and in Windows Application log. Myalert firing my job. All is fine.But when transaction log is full in MSDE error 9002 severity 17 state 6 islogged only in sql server log. It is not logged into Windows Applicationlog so my alert does not work.So here is my questions:1. Why MSDE does not log error into Windows Application log?2. Why error 9002 has severity 19 in sysmessages table but is generatedwith severity 17?3. Why state of error 9002 differs under sql server and msde?Thank you.--Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
View 5 Replies View RelatedGreetings:
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
Thanks!
HiI have a backup device file ... ".bck" whick has grown pretty large.Is there any way I can reduce its size ?ThanksDavid Greenberg
View 4 Replies View Related
I have a problem when i restore my .DAT_BAK file. I am getting error like "The backup set holds a backup of a database other than existing database. Restore Database is terminating abnormally".
I tried by using
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH MOVE 'VZAI_DATA' TO D:PROGRAM FILES..MSSQLTEST.MDF',
MOVE 'VZAI_LOG' TO D:PROGRAM FILES..MSSQLTEST.LDF',
REPLACE
And also i tried like
RESTORE DATABASE <DATABASENAME>
FROM DISK = 'D:DATAMYTEST.DAT_BAK'
WITH REPLACE
When i use like this,
RESTORE FILELISTONLY FROM DISK = 'D:DATAMYTEST.DAT_BAK'. I am able to get the output as LogicalName, PhysicalName, Type, FileGroupName, Size, etc.
Can i anyone please help me out?
Thanks in Advance,
Anand Rajagopal
is there a way to backup all stored procedures in a database?
View 4 Replies View Relatedi have a database names "students" in SQL server 2000. is there any TSQL statement to make a backup of the student databse in to a sspecific location ??????????
pls help
is it possible to insert record into second database from a stored procedure which is in first database?
View 1 Replies View Related