SQL Server Admin 2014 :: Backups To Different Folders Based On Week Day

Oct 25, 2014

I am trying to create a job that would backup a DB dynamically to a different folder based on the week day. So if it's Saturday, backup to folder Saturday.

I have this

DECLARE @BackupLoc nvarchar (100)
DECLARE @DayOfWeek nvarchar (100)
set @BackupLoc = N'D:Backup'
set @DayOfWeek = (SELECT DATENAME(dw,GETDATE()))
set @BackupLoc = @backuploc + @DayOfWeek

[Code] ....

But it's giving me permission errors, although the AGENT and MSSQL service accounts are members of the local admins group.

View 2 Replies


ADVERTISEMENT

SQL Server Admin 2014 :: Old Backups Will Not Delete

Jun 19, 2015

Have a SQL 2014 install and cannot for the life of me get the maintenance plan to remove old backups. I've tried everything. Rights to the folder where the backups are stored are adequate, extension set in the clean up task is as it should be, etc. Log shows the job ran successfully. Running the command manually shows successful completion, but backups are still not removed.

View 9 Replies View Related

SQL Server Admin 2014 :: Restoring DBs From Files Not Backups?

Dec 1, 2014

Until yesterday I had a server running SQL Server 2008 R2 - with all the SQL Server DB files on an attached disk array.

The server died - so I attached the disk array to a new server - and all the DB data files are visible there.

I installed SQL Server 2014 on the new server and am trying to work out how to point it at the existing database files.

I also have backups of the DB's - but they will take ages to copy over and restore - so it would be much easier to just use the db files. Should I restore the master db first (easy as its small)?

View 9 Replies View Related

SQL Server Admin 2014 :: Is It Realistic To Base Recovery On Mdf And Ldf Backups

Dec 15, 2014

My recommendation to my manager is to base recovery on .bak and .trn files. However the .mdf and .ldf files can be backed up without being detached and I am wondering about the value of these file backups. He has set up backups to external drive of the .mdf and .ldf but not of the .bak and .trn files that I have set up. His recovery strategy is based on complete backup of the machine and bare metal recovery. I am finding it hard to defend my preference for going about recovery with a new SQL Server installation, then restoring .bak files. Has tried recovery from .mdf and .ldf? Please note that all the database files (system and user) are on a single drive, I think this was set up to allow the machine to be clustered. We are intending to do a test rebuild of the machine from his backups.

View 9 Replies View Related

SQL Server Admin 2014 :: Database Backups And AlwaysOn Availability Groups

Oct 16, 2014

This is my first deployment of an always on availability group for SQL 2014 and I'm trying to get my custom backup procedure to handle all databases appropriately depending on the primary group. Basiscally I want the system databases and all databases that don't participate in the availability group to be backed up on both nodes and those that do participate backed up ONLY on the primary server. I've looked at the sys.fn_hadr_backup_is_preferred_replica funcation, but would like to only have to test for a single databases existance in the availability group. If the one database is in the group, only backup the system databases and those that don't participate, otherwise backup everydatabase. This would be the case for both full backups and transaction logs.

View 1 Replies View Related

SQL Server Admin 2014 :: Comvault Backups Breaking Backup Chain?

Jan 10, 2015

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 Related

SQL Server Admin 2014 :: Assign Timestamp For Automated Backups In Job Schedules

Jul 9, 2015

How would we know to assign the time stamps for automated backups in job schedules....

View 1 Replies View Related

SQL Server Admin 2014 :: Point In Time Restores Referencing Copy-only Backups?

Nov 25, 2014

On one of our SQL Server 2014 boxes each database has a copy-only full backup made every night, in addition to the maintenance plan schedule of a full backup weekly, daily differential backups and log backups.

When performing a PIT restore in SSMS the restore file list lists the most recent copy-only backup as the full backup to use, not the most recent plan full backup. I noticed that using SSMS 2008 to start a PIT restore on the 2014 box does not have this problem, and lists the correct restore file sequence (ignores the copy-only backups).

View 9 Replies View Related

SQL Server Admin 2014 :: Separate Data Files / Log Files / TempDB / Backups

Jan 9, 2015

I proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.

View 2 Replies View Related

SQL Server Admin 2014 :: Insert A Row To A Table Based On Table Values?

Jun 10, 2015

Here is my table:

My question is: How can I insert a row for each unique TemplateId. So let's say I have templateIds like, 2,5,6,7... For each unique templateId, how can I insert one more row?

View 0 Replies View Related

SQL Server Admin 2014 :: Does Security-admin Role Plus Deny Alter Any Login Cancel Each Other Out

Aug 27, 2015

I want to set up a database role so that users can use sp_readerrorlog through SSMS. It does a check on membership in the securityadmin role.

I have tested it and can see you can grant execute on xp_readerrorlog but the SSMS GUI uses sp_readerrorlog.

I thought I could create a user/certificate and add the signature to sp_readerrorlog but it's not permitted (likely because it's not a normal database object).

So the other solution is to add the users to the securityadmin role but then explicitly deny alter any login (best done with a custom server role in 2012+ but otherwise just manually in 2008). I tested this out and it works, I'm not able to alter any logins or increase my own permissions, I also did a check of what's reported from fn_my_permissions(null, null) and it shows minimal permissions like I'd expect.

View 0 Replies View Related

SQL Server 2014 :: DATEADD - Grouping By Week

Aug 25, 2015

We have bee using something like the following to get the Monday for the specified date.

However, we came across a scenario where this is not working

DECLARE @dt AS datetime
SET @dt = '2/19/2006'
SELECT DATEADD( ww, DATEDIFF( ww, 0, @dt),0)

It had seemed to be working as expected, but we recently came across a problem where The above is returning 2/20, not 2/13

View 9 Replies View Related

SQL Server 2008 :: Delete Folders / Sub Folders Older Than N No Of Days

Jul 30, 2015

If I run the below command, it clean up the files inside the folders but unable to clean up the folder and sub-folders older than 30 days. xp_ cmd shell ' forfiles -p "D:abcd" -s -m *.* -d -30 -c "cmd /c del /Q @path"'

View 4 Replies View Related

Recovery :: SSMS Won't Show Bak Files Or Even Folders Using Enterprise Admin Account?

Oct 16, 2015

I'm working on a newly installed windows 2012 R2 server which has SQL Server 2012 and SSMS installed on it. What has been odd is that when I open the "restore database" dialog, in the "Select Backup Devices", "Locate Backup File" dialog, only the immediate folders off of the root of the E: drive are visible. E: is where sql server data and backup files are stored.

The account I am logged in with is supposed to have Enterprise Admin rights. How is can I not see these folders and files? The files are certainly there and are visible to me on that login with windows explorer. I am able to backup via SSMS without issue.

When I look at the folder's permissions everything seems normal. If I add "Everyone" to that folder's permissions the folders and files become visible. The SQL Server service is running on the NT ServiceMSSQLSERVER account, which is normal for my other installations of sql server.

View 12 Replies View Related

SQL Server 2008 :: Return Fiscal Year Based On YYYY-week Format

Nov 6, 2015

I have a table where hours are being loaded in a weekly basis. The YearWeek is populated when the data is loaded. The value format of the Year Week is 2015-39, 2015-41, etc. I need to calculate the total hours per Fiscal Year.For example, week '2015-39' will be return FY15 and week '2015-41' will return FY16, and so on. By extracting the year, I can do a group by and have total hours for each year.

Currently, I have it working by splitting the value into year and week and then looping through each year and week, so I can assign the totals to the corresponding FY.select sum(hours) as total, yearweek from tablename group by yearweek...Then I loop through using C#.I can return the FY using an actual date,how to do it for year-week format for any given year.

select CASE
WHEN CAST(GETDATE() AS DATE) >
SMALLDATETIMEFROMPARTS(DATEPART(YEAR,GETDATE()),09,30,00,000)
THEN
DATEPART(YEAR,GETDATE()) + 1 ELSE DATEPART(YEAR,GETDATE())
END AS FY

View 9 Replies View Related

SQL Server Admin 2014 :: Encryption Key Not Known

Apr 14, 2015

I inherited a lot of Servers to upgrade to 2014 to include an SSRS Server.

The encryption Key was never backed up and it seems that no one knows what the password is?

Do I have to manually load the reports? There are a lot of Reports.

[URL]

View 4 Replies View Related

SQL Server Admin 2014 :: BCP Aborts On First FK Violation

Sep 26, 2013

I want to use BCP to load data from a text file.

By default, constraints are turned off in bcp, so I use the CHECK_CONSTRAINTS hint.

bcp aborts if ANY of the rows contains a FK violation. No data get loaded.

So if I add the -b 1 batch size option, it loads all data UNTIL the first FK violation, but nothing after that.

I want to load EVERYTHING ... except for the violations. But bcp won't let me. Is there a way?

View 2 Replies View Related

SQL Server Admin 2014 :: What Is The Default SA Password

Jan 13, 2014

If I install an instance with Windows Only authentication, and then change it to Mixed Mode, if I enable the sa login, the password has already been set. What is the default? If it's generated, how secure is it? Is the password generated? What algorithm is used for that?

View 9 Replies View Related

SQL Server Admin 2014 :: How To Restore MDF File

Mar 21, 2014

My sql databases in SQL Server 2014 has the status "suspend" as I saw in SQL Management Studio. I can't restore to serviceable condition sql databases through standard procedures. I need to restore .mdf file.

View 9 Replies View Related

SQL Server Admin 2014 :: Return One Field / One Row

Jun 18, 2014

I am using a monitoring system where I can monitor a numeric SQL result assuming the result is one field and one row.I would like to do this to say monitor the free available space or percentage on say the Master database. DBCC SQLPERF gives me a few columns and results for all databases on the server.

View 2 Replies View Related

SQL Server Admin 2014 :: DNS Pointing To A Listener

Jun 25, 2014

In our environment applications are using a DNS name which points to the physical server ip address. Now we are planning to move to 2014. We are planning to have servers in different subnets so we will be having two ip adresses for listener. How we can point the DNS to the listener ips? If failover happens can the DNS point to the exact ip address of the listener where it's primary node?

View 1 Replies View Related

SQL Server Admin 2014 :: How To Schedule A Job To Run At Different Intervals

Jul 31, 2014

Is there a way to schedule a sql job to run at different intervals

For eg:
The job should run at
7:00 Am
8:00 AM
and then at 10:00 Am

View 3 Replies View Related

SQL Server Admin 2014 :: Non-yielding On Scheduler

Nov 19, 2014

We installed the Sql 2014 in Test server.

We observed frequently

"Process 0:0:0 (0x1e10) Worker 0x00000006B6D341A0 appears to be non-yielding on Scheduler 13. Thread creation time: 12906028806348. Approx Thread CPU Used: kernel 0 ms, user 0 ms. Process Utilization 13%. System Idle 84%. Interval: 70189 ms."

Is it better to run the profiler or performan counter?

What are the filters we have to select in the profiler to monitor the Sql server

View 0 Replies View Related

SQL Server Admin 2014 :: Reporting Services Through IIS?

Dec 8, 2014

I have a SQL server box running 2014 reporting services. I have another server running IIS v8.

I would like to be able to connect to the IIS site and be given the SSRS report browser.

So externally if I browse to [URL], I am presented with the report server interface, the same as if I browse to http://xxx.xxx.xxx.xxx/reports internally.

What are my options?

View 4 Replies View Related

SQL Server Admin 2014 :: DB Read Only Copy

Dec 11, 2014

What is the best approach for a read only copy of a database that is ~ 1TB. The primary database is fed nightly with an ETL process. We are currently trying to duplicate the ETL to read only server but that process is not going well. So we are looking at other options to let SQL make the copy.

The primary database is on a Win12R2 with SQL 12 or 14, a 2 node A/P failover cluster.

The read only copy will be on a Win12R2 with SQL 12 or 14. It is not a requirement to fail over to the read only copy if the primary should go down.

What would best the approach to accomplish the end result?

View 3 Replies View Related

SQL Server Admin 2014 :: Possible Errors In AlwaysOn

Jan 12, 2015

I did not worked on AVG in sql 2012, what are the possible errors and how to resolve in always on in sql 2012 .

View 1 Replies View Related

SQL Server Admin 2014 :: Script To Failover

Jan 18, 2015

I have 10 databases which are configured as principal in mirroring I need to failover all the databases as part of failover , instead of writing query each database as parner failover, is an script which will generate the databases as principal to failover ?

View 3 Replies View Related

SQL Server Admin 2014 :: Delay In Between Replicas In AG

Feb 18, 2015

How to check the delay in between replicas in AG?

View 3 Replies View Related

SQL Server Admin 2014 :: OST File Is Not Accessible

Feb 26, 2015

my 4 GB .OST file is corrupted and i am not able to access my mails and contacts.

View 2 Replies View Related

SQL Server Admin 2014 :: How To Identify The Job With The Code

Apr 7, 2015

I got query from sysproccesses and the result for one spid is : SQLAgent - TSQL JobStep (Job 0xF3BDA6DFF9DCF94D81FF97C49EDA7C61 : Step 1

Which job has this code : 0xF3BDA6DFF9DCF94D81FF97C49EDA7C61

View 1 Replies View Related

SQL Server Admin 2014 :: SSMS Will Only Run As Administrator

May 28, 2015

After installing SSMS on some computers - the only way we can get SSMS to run correctly is to run it as the administrator. Is there a way where you don't have to do that? These end users are logging as themselves and have accounts in SQL Server all set up - but SSMS will only launch for them if we right click and select "run as administrator".After doing some digging - it seems that this is a common problem out there.

View 3 Replies View Related

SQL Server Admin 2014 :: What Does Restore Log Do After The Log Is Restored

Jul 29, 2015

when execute the restore log command, in the messages window it shows how many seconds the restore takes, at the meantime, on the status bar, it also shows the seconds the command takes.

Two values are different and could be very different, please see below examples , restoring takes 1.8 seconds, but in total the command takes 4 seconds to complete, the other one is 8.1 seconds and 12 seconds.

What does SQL Server or Windows do after the restoring?

pic a:

pic b:

I did a xperf, I can see after the restoring is completed, sql server did garbage collect and log write, which just run very quickly, but storage is busy on reading the log file for nearly 2.2 seconds( 4-1.8), and 4 seconds ( 12-8.1) .

pic 1:

pic 2:

see pic 1 above, from 13 to 17, the restore operation is finished, but the storage jump to 100% active to do some reads, only reads no writes. zoom that period shows pic 2, it read 4096 (I don't know the unit size) for about 4 seconds, what does this do?

Data file, log file, backup file are no different drives, but all local drive, the interesting point is the read jumped after restoring, I tested it on different server, same result...

View 1 Replies View Related

SQL Server Admin 2014 :: Moving Databases From One PC To Another?

Aug 9, 2015

I've got an old version of SQL Server 2008 R2 Developer Edition on an old PC which is failing. I've got a new PC and have put SQL Server 2014 Developer Edition onto it. Now before the old machine completely dies, I've gotten into SSMS on the old machine and did a backup of the databases I want to save. I've moved the .BAK files to where I could get to them from SSMS on the new machine. I've gotten into SSMS and tried to do a restore the database to my new machine. However I'm getting an error that does not make any sense to me.

The database I'm I've backed up is named JobSearch. When I backed it up, that was the only database I had selected. Like I said I copied the .BAK to the new machine. Got into SSMS, told it that I wanted to restore the JobSearch database, telling it where I wanted to put it, and it then immediately fails with a:

"Restore of database 'JobSearch' failed. System.Data.SqlClient.SqlError: Logical file 'VideoLibrary_Data' is not part of the database 'JobSearch'. Use RESTORE FILEISTONLY to list the logical file names."

Well of course VideoLibrary isn't "the logical file". But neither did I select VideoLibrary (which is a database I also want to move, but I'm doing one at a time). So what in heck is going on here? Why is it complaining about a database I haven't even selected to back up? Why, when I check everything on the old machine, it's backing up JobSearch, but on the new machine it sees VideoLibrary?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved