SQL Server Admin 2014 :: Can Backup All Stored Procedures In A Database
Oct 31, 2014is there a way to backup all stored procedures in a database?
View 4 Repliesis there a way to backup all stored procedures in a database?
View 4 RepliesI am curious what other people have done to implement read-only routing for a large number of procedures.
Basically figuring out when to call procedures that are read-only with read-only intent.
We have a user application that passes an encrypted string to a web service that directs it to our SQL Servers.
I've been tasked with finding a way to make this happen without changing the application.
The only thing I have been able to come up with is writing something (which I did) that will identify whether something is read-only or not and storing a big list.
Then having the web service look up the given procedure and adding the intent where needed.
What permission is required to run debug feature in SSMS(debug Stored Procedures). This is a development machine and developer requested for this.
EXECUTE permission was denied on the object 'sp_enable_sql_debug', database 'mssqlsystemresource', schema 'sys'.
EXECUTE permission was denied on object 'sp_sql_debug', database 'master'.
Is there any option other than giving sysadmin privilege on SQL?
Replication-Replication Distribution Subsystem: agent PRODDATA6 failed. Could not find stored procedure 'sp_MSdel_dboActiveTask_msrepl_ccs'.
Replication-Replication Transaction-Log Reader Subsystem: agent PRODDATA6. The process could not execute 'sp_replcmds' on 'PRODDATA6'.
Replication-Replication Distribution Subsystem: agent PRODDATA6. Could not find stored procedure 'sp_MSupd_dboActiveTask'.
Looks like these sp's got deleted?
Is there any way to enforce table references in stored procedures? For Example, we have stored procedures with a ton of different formats, "dbo.table", "table", "db.dbo.table", etc. Can we make it so that for every stored procedure, the reference must be at least "dbo.table"?
View 1 Replies View RelatedI would like to know what happens if i shrink the database with truncate only option and do a full backup or transaction log backup ? are the full backup or transaction log backup valid? I know that the performance of the database is bad if i shrink the database. What happens to full backup or transaction log backups?
View 9 Replies View RelatedI did tried the encryption on server "A" for database "AdventureWorks2012". Then I tried to restore to server "B". There was the certificate issue, and I thought "of course : it's encrypted ! Let's deactivate it". So here I go "ALTER DATABASE AdventureWorks2012 SET ENCYRPTION OFF".I look at sys.databases : not encrypted.I backup using no encryption, I verify using msdb.dbo.backupset : not encrypted.
I move my backup to my other server where encryption was never configured (so no certificate, nothing...), and I have the error :
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xFA130E58C999C4919B8975999C83A75A403B11D8'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
What is the best way to restore a database from a folder of backups (including full, diff and log backups) without using the backup history in msdb?
I have a restore process that restores all backups on a regular schedule in order to fully verify their integrity. To do this, I use the backup history in msdb on each server that I'm monitoring. I had a thought the other day that I would be in trouble if I lost msdb. Then my backup history would only be as good as the last backup of msdb.
What I'd like to do is read a folder of backup files and generate a restore script up to a specified time. Would I use RESTORE HEADERONLY to do this? If so, would I use PowerShell to traverse each file in the folder?
I've a SQL server 2014 running on one of our server. We're in the process of implementing security steps for our databases. I've encrypted a column in one of the table in the database on the server. The issue is when I restore the backup on my local SQL server and run a query to decrypt the column data it gives me null values. On the other end when I decrypt the column data on the main server it works fine. I found a thread on this forum which states to do the following when restoring the encrypted database on different server.
USE [master];
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongPassword';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO
select File_Name
, CONVERT(nvarchar,DECRYPTBYKEY(File_Name))
from [test].[dbo].[Orders_Customer]
I tried doing above still no luck.
Need to restore database,here's the scenario:
Data got deleted on Friday evening, need to have database restored to FRiday afternoon and also some data has been entered on Monday, which needs to be there.
I have two procedures Sp1 and Sp2. The Sp1 is calling from Sp2. If there come new parameters or removed from the first procedure Sp1 then it will effect the Sp2 and its execution will break if required changes not done. So I want that when any changes making in SP1 then i need alert about the Sp2 changes.
View 3 Replies View Relatedhow to take backup of indexes for a particular table.
View 1 Replies View RelatedHow to take backup of indexes for a particular table.
View 5 Replies View RelatedIs there a way to backing up SQL DB without having to stop users from connecting to it.
View 5 Replies View RelatedLook at this scenario
1- I have created a backup device
2- I have created a maintenance plan full backup and run it - with overwrite option on the backup device
3- I have created a maintenance plan differential backup and run it with append option on the same file of backup device
4- I have created a maintenance plan log backup with append option on the same backup device
When I made restore database from the backup device i found three files the full and differential and log backup
5- I ran again the differential backup maintenance plan ( suppose to be ran everyday night )
when i made restore database I found only two backups the full and the last differential !!!
what I want to do is to take a full backup every week , append differential backup everyday and append log backup every hour
when I ran the last differential backup it erased the first differential and log backups, why is this happening and how to apply this scenario and keep the all differential backups on the same backup device .
I run the following:
EXECUTE dbo.DatabaseBackup
@Databases = 'F1SB',
@Directory = 'F:SqlBackup2014',
@BackupType = 'FULL',
@Compress = 'Y',
@Encrypt = 'Y',
[code]...
I cannot see the file created in the directory. The account under which sql server the agen job run have full privileges on it and is sysadmin.Then i run the Command in ssms
BACKUP DATABASE [F1SB] TO DISK = N'F:SqlBackup2014<server>F1SBFULLIGS-DB01_F1SB_FULL_20150510_214455.bak' WITH NO_CHECKSUM, COMPRESSION, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = [serverCertificate])
and I get this error message:
Msg 3013, Level 16, State 1, Line 13
BACKUP DATABASE is terminating abnormally.
I need backup script to take all the database backups and we have the maintenance plan but our database character size is 98 and when we are taking the backups through maintenance plan while storing the backup history information it is adding the date and timezone information and exceeding the length to 128 so it is not writing the information on MSDB.
So we want to take the backup using the script and it has to create sub folder for each database. Also if any of the database fails it should continue with others.
I've got the below and have several variation and still cant seem to find a perfect way to query the server to bring back that last full backup per db. I'm shopwing mutilple records in the backup set db w/ type = 'D'. I look online and type D = Database. Which i assumed it meant full database backup. Apparently not. Try running the below on one of your full databases. Then check to see if the date is actually the last backup date.
DECLARE @db_name VARCHAR(100)
SELECT @db_name = DB_NAME()
-- Get Backup History for required database
SELECT TOP ( 30 ) s.database_name,
m.physical_device_name,
[Code] ....
Recently I have faced one DBA interview, below is the question they asked me.
" Does AlwaysON secondary replica support Full backup, if it is not why"?
I know AlwaysON secondary replicas support only copy_only and tlog backups, why they wont support full backup?
If data is modified (by an insert, update, or delete) while the backup is running, will the backup contain those changes or will it be added to the database afterwards?
View 2 Replies View RelatedWith all the new functionality, can 2014 now restore a single table from a standard backup without using any third party tools? I have looked, but can't see this listed as a feature (though that doesn't mean it's not there, maybe I've just missed it).
View 6 Replies View RelatedI am planning to take one full backup and Transactional Log backups for every month ..as i will be making the changes in database only once in a month .
And I am aware of that in case of disaster i need to restore database with all the Transactional Log backups . My Plan is to have Transactional log backups for 5 Years and after 5 years i would be taking a full backup .
So should I need to take any other precautions or concerns with this approach.?
we currently use Backup Scripts from Ola Hallengren, It Says Full (non copy-only) and differential backups are performed on the primary replica. Full(Copy-only) backups and transaction log backups are performed on the preferred replica.
we currently do FULL(COPY_ONLY) Backup everyday and LOG Backups for every 15 min, is there any performance benefit on running the FULL (non copy-only) on the preferred replica .
I'm performing SQL Server native backups and Comvault Backups are breaking the backup chain?I can work around them if they only perfume 1 backup per day.We have a 3rd party hosting our servers so I do not have much control as to what they do.I have concerns about being able to get my databases Comvault being restored.
View 2 Replies View RelatedRunning Frequently Transaction logbackup during Integrinty check DB /optimization job will cause any issue /impact as duration will extend ...
View 2 Replies View RelatedI've recently started working with a public sector organisation who have 4 clustered sql instances that has 80% of it's db mirrored.
Looking at the transaction log - it seems that a transaction log backup is a good idea as the log is 4x larger than the data file.But I'm not allowed access to the physical server to check onto which drive I can create the trn. No RDP, no vmware - let's be honest I'm not even allowed to launch cmd line Also the Server Manager informs me "We will need to carefully look at database backups if you guys want to start doing these backups on box, as that will break our off box backup routine (it will screw the transaction chain)."
I don't understand how backing up the transaction log could break the "transaction chain"?
I have an issue to restore an encrypted backup via GUI.I can restore an encrypted backup on another instance using t-sql command, but cannot do the same using the GUI of sql server 2014. The message error is: No backupset selected to be restored.I have restored the master key from the instance where the encrypted backup was created on the instance where I want to restore the encrypted backup, then restored the certificate. I managed to open the master key. I have the following versions:
On the instance where the encrypted backup is taken:
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
On the instance where I'm trying to restore the bakcup via GUI:
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )
I was trying to create stored proc
[code="create procedure dbo.sp_table1
@idint
as
begin
updatetable1
setisarchived = 1,
[Code] ....
But the query was continuously blocking the query below
updatetable1
setisarchived = 1,
modtime = getdate()
whereid = @id
andisarchived = 0
I was not sure, why the create procedure statement is blocking the update statement.
And have chosen the destination - unstructered (flat) file. But the wizard proposes to export only one table (dbo.Acocount) and all the others from the list are not exported. How can I export ALL the data into one file.I need to do this to edit the syntax in the editor and then import this data and database structure into Postgresql
View 4 Replies View RelatedI have two databases like each other that one is the backup of another. Each DB have 2 filegroups. I want to replace one filegroup from one db to another. How do I do this? Or how do I backup and then restore?
View 3 Replies View RelatedQuery to show logins that don't have any permissions within the SQL instance? I'm tasked with doing some cleanup and have found some cases where the database was deleted or moved to another server but the logins that used it were not deleted. I'd like to identify them to research.
For instance a query to show logins that have no permissions in any of the existing databases would be handy. I'm thinking it would be complicated by the need to loop through all of the existing databases and then outer join it to the list of instance level logins. Going to try to write something like that but was hoping that a script already exists.
I have multiple SQL 2008 severs with databases. Also, 1 mirroring server in place.
Since my database count is increasing can i have only 1 mirroring server. Is there any limit of db at mirroring server. I would have approx. 150 databases.
I want to Replace The Big Log database with A new one ( A database with same structure).But current DB has many connection .
This is my plan :
1- Create a new database with same structure.
2- Rename current database to olddb with this code :
USE master
GO
EXEC sp_dboption CurDataBase, 'Single User', True
EXEC sp_renamedb 'CurDataBase', 'OldDataBase'
GO
3- Rename Newdb to current DB.
USE master
GO
EXEC sp_renamedb 'NewDataBase', 'CurDataBase'
is it true ? and Tsql code is ok ? (dont forget many of connection to curdatabase (that Is a log db) and loss some seconds data is not problems)