SQL 2012 :: Moved Backups To Fileshare But Can't Restore Directly
Oct 29, 2014
We had our backups backing up to the server where the databases reside. Now I modified the backups to backup to a file share. Now when we try to restore from the file share the restore fails, so we have to copy the backup to a drive on the server and recover for there. Should I be able to restore directly from the file share (using the gui)? Do I need to change something else to modify the default backup drive?
View 9 Replies
ADVERTISEMENT
May 5, 2015
Can we backup our cluster databases directly to tape using native backups (without using any third party tool) ? It's SQL Server 2012 two node Active/Passive cluster. One of the DB will be huge in size, hence checking if we can directly backup from the cluster instance to a tape.
View 7 Replies
View Related
Aug 21, 2015
So I'm testing some things in our new servers and was trying to restore a database from some striped backup sets. We have 4 files for our backups and restoring the FULL backups with no recovery worked beautifully via SSMS. But when I tried to restore the differentials (also striped across 4 files), the GUI gave me this error:
Unable to create restore plan due to break in the LSN chain.
How to locate when the break happened and I came across this article about how this is an SSMS 2012 bug.
So I tried the advice in this article to attempt a restore via Files and Filegroups, and ended up with the below error:
EDIT: Picture is attached if it is not showing in post.
I was able to restore via T-SQL, but I want to also be able to restore through the GUI.
View 5 Replies
View Related
Jun 8, 2015
I recently set up a SQL 2012 FCI with a NetApp fileshare to store the data files. The install worked just fine, but I can't run an integrity check for any of my databases. Whenever I try, I get these error messages:
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 1823, Level 16, State 8, Line 1
A database snapshot cannot be created because it failed to start.
Msg 5120, Level 16, State 104, Line 1
Unable to open the physical file "path-to-fileshareMSSQL11.MSSQLSERVERMSSQLDATAmodel.mdf:MSSQL_DBCC12". Operating system error 1: "1(Incorrect function.)".
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.The error message suggests SQL had a problem creating the snapshot, but I checked through some NetApp documentation for configuring SMB 3.0 for SQL.
View 3 Replies
View Related
Sep 1, 2015
I wonder if it is possible to run a stored procedure and save the results directly to a file in a predetermined directory
As an example I have created a (simple) stored procedure:
USE CovasCopy
GO
CREATE PROCEDURE spTryOut
(
@LastName as NVARCHAR(50)
, @FirstName AS NVARCHAR(25)
[Code] ....
What I would like to add is a (or more?) lines that save the results in a file (csv/txt/tab?)
The name I would like the file to have is "LastName, FirstName, Date query ran, time (HHMMSS?) query ran"
The directory: D:SQLServerResults
View 9 Replies
View Related
Jan 29, 2004
Hi everyone,
I have a problem with my MS SQL Server 2000 (SP2). My task is to restore a backup from a database on another computer.
The server makes a backup everyday. If this server gets a hardware problem there must be a chance to replace it by another server which is made by the backup files.
In this scenario I don't have the possibility to copy the whole folder of the old server.
Is it possible to restore the bakup so that the new server looks like the old one?
Thanks for every response.
View 2 Replies
View Related
Jun 4, 2008
Hello,
Can anyone tell me the SQL statement to restore an existing backup to an existing database?
Thanks in advance.
View 16 Replies
View Related
Feb 26, 2004
Our DBs are set up to do a full backup once a day (late at night) and then transaction log backups during the day at shorter intervals.
I want to setup a dev database on the same server. I want this database to be an automatically restored copy of the live database. So every night, after the full backup of the live DB, I want to restore the live DB to this dev DB.
Can this be automated? Can the restore automatically stop the dev database in case some open connections exist?
Is there a better way to do this?
View 5 Replies
View Related
Jan 23, 2006
can anyone explain me how can i make a DB backup in sql express 2005 ?
The sql express instance and my web aplication are running on the same server.
I need a script for directly execute it (and if possible also to restore the DB ) from my web aplication.
other question:
any idea how can i tell to sql express to do the backup automatically every day?
View 1 Replies
View Related
Oct 23, 2015
I make two full backups on Oct 1 and Oct 10. I want to restore the server to a state in Oct 5. So I just do as follows:
1.Perform a transaction log backup on the server on Oct 23. I have never backup transaction log in the past.
2. Restore the server with Oct 1 full backup with NORECOVERY option.
3.Try to restore to the point at Oct 5 12:00, with the transaction log.
But the restore fails and SQL Server said the transaction log does not contain the point. The point is too early. Why? Also my .LDF file is about 13G, but the transaction log backup is only 200MB. Why?
View 1 Replies
View Related
Oct 23, 2015
I make two full backups on Oct 1 and Oct 10. I want to restore the server to a state in Oct 5. So I just do as follows:
Perform a transaction log backup on the server on Oct 23. I have never backup transaction log in the past. Restore the server with Oct 1 full backup with NORECOVERY option.Try to restore to the point at Oct 5 12:00, with the transaction log.
But the restore fails and SQL Server said the transaction log does not contain the point. The point is too early. Why? Also my .LDF file is about 13G, but the transaction log backup is only 200MB. Why?
View 12 Replies
View Related
Oct 21, 2015
how to restore database backups with different recovery fork. I have 1-full backup 2-diff backups and 10-tran backups. My prod database in mirror, so after error, switched to mirror with "allow_data_loss" option. And now I have full and diff backup with one recovery fork GUID and other backups with another GUID.So the question is, how to restore all this backups if in middle of restoration will be different recovery fork.Tryed to restore log backups with new fork guid and got error:This backup set cannot be applied because it is on a recovery path that is inconsistent with the database. The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point. Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time.
View 2 Replies
View Related
Jun 23, 2015
I've a SQL Server Express 2012 DB that I need to backup and restore on a different machine.I know that in the past someone performed full db and logs backup with sqlcmd.exe and I found some of this backup files but not all of them.In the last 6 months no backups has been taken.What is the right procedure I need to follow in order to save a backup of this DB and restore it on different machine withou losing data?
View 9 Replies
View Related
Feb 12, 2003
I'm not sure when it started, but...
It seems that very recently, the backups made during a nightly ArcServe tape backup procedure are showing up in the restore window on our SQL server. We don't want that! The application group makes it's own backup's to disk, and we want to see only them in the restore window.
(The tape ackup is a redudnant process, to be used if one of our on-hand disk backups are bad or non-existent, or if the DB needs to be recovered from an older date.)
What made the ArcServe files show up? How can we stop SQL server from thinking tyhat they are part of the backup set we are conccerned with?
Thanks.
MichaelGG
View 1 Replies
View Related
Oct 23, 2015
I make two full backups on Oct 1 and Oct 10. I want to restore the server to a state in Oct 5. So I just do as follows:
1.Perform a transaction log backup on the server on Oct 23. I have never backup transaction log in the past.
2. Restore the server with Oct 1 full backup with NORECOVERY option.
3.Try to restore to the point at Oct 5 12:00, with the transaction log.
But the restore fails and SQL Server said the transaction log does not contain the point. The point is too early. Why? Also my .LDF file is about 13G, but the transaction log backup is only 200MB. Why?
View 4 Replies
View Related
Aug 22, 2007
I'm working on a restore procedure for the case where all MDF filesare missing, but the LDF files are all intact. A full backup is doneevery 24 hours, and a log backup is done every 3 hours. Afterrestoring the last full + log backups, is it at all possible to usethe LDF files to recover data from that point up to a newer point intime?I've found a post which explains how to do this on SQL Server 2000<http://groups.google.com/group/comp...s.ms-sqlserver/browse_thread/thread/3ef5c7cbc0a83334/f3b0c70811d35ed7>, but step 4fails with the following error message:BACKUP LOG cannot be performed because there is no current databasebackup.
View 2 Replies
View Related
Aug 18, 2006
I'm trying to test out a restore scenario for a large database. It contains several files, each one is backed up when we are finished inserting data into it. No changes are made to the data after insert. All changes happen in tables in the primary file group.
So, I backup the primary file, the log, the first "partitioned" file, the log, the next "partitioned" file, the log, etc, etc, etc. Now, I delete one of the tables residing on one of the partitioned files and want to restore it. For arguments sake, let's say I have a primary file, and 5 partitioned files. I delete the table which was on file 3.
I'm having trouble wrapping my head around the steps to restore under these circumstances. I thought I understood it but, apparently not. When I try to restore, I'm told I must first backup the tail of the log. Having done that, I restore file 3, and all subsequent log files. Now, I'm right back where I was (missing the deleted table). When I try to roll back to a point in time before the delete...it claims to be successful but, nothing perceptable is different...??
Can someone help set me straight here?
View 10 Replies
View Related
Oct 29, 2015
I am setting up mirroring on one of our live databases tonight. The database is quite big, around 300GB.
At the moment we are doing log backups once a day and full backups every night.
My question is, when I set up the mirror, will the log backups on my Principle DB affect the mirror in anyway?
View 4 Replies
View Related
Jun 3, 2015
I have a report that groups a particular dataset by customer, with a page break between each customer. If print this report, it looks great - one page per customer.
What I would like to do is have the report deployed on schedule to a windows file share, one .pdf file per customer. How to parse the output into individual .pdfs (one per customer) instead of a single .pdf containing all customers? I get it if this is not possible.
My current (painful) workaround would be to replicate the report for each potential customer, and set a schedule for each report to deploy sequentially. (Customer 1 at 9:00, Customer 2 at 9:01, etc).
Currently on SQL Server/Visual Studio 2008.
View 2 Replies
View Related
Jun 2, 2014
I have inherited a new SQL Server 2008 database server and can not figure out how my user databases are being backed up. This database server is running under a VM.
All the user databases are being backed up nightly per the SQL server log. The backups are written to a virtual disk and is kicked off by the NT AUTHORITYSYSTEM user. I can not see the virtual disk. A restore task does not provide any information about the last backup. I have created a new database, and it is automatically included in the next set of backups.
I have looked at the windows event viewer with out any luck. There are no SQL Server Maintenance Plans or Agent jobs that call a backup. I have also checked the Windows Task Scheduler and can not find any task that does a backup.Could the backups be called from another server ?
View 6 Replies
View Related
Oct 15, 2014
I am using Ola Hallegren's scripts to do backups. He uses @Cleanup Time to delete backups older than a certain number of hours. My situation is I do a full backup of a database on Sunday and then I have a few Differentials and then log backups for the rest of the week. When Sunday rolls around again and my full backup is finished, I would like to delete all the differential backups and log backups. Any way that I could accomplish this using Ola's scripts?
View 1 Replies
View Related
Dec 8, 2014
I have tried doing sql server backups to a file share and that has been taking too long. So I've decided to backup locally and then taking those backups and getting them off the server. For those that are doing this what do you use to get your backups off the server?
View 9 Replies
View Related
Jan 23, 2015
I am sure I have seen in the past in a monitoring tool that PLE drops off to 0 whenever we do a backup. I was doing some reading around this however and found something that said backups use a different portion of memory external to the buffer pool (minmax settings).
Is this correct and how can I tell how much memory will be required for a backup?
View 2 Replies
View Related
Mar 23, 2015
Just hit a issue where we had a 40gb backup file for a database with an mdf of less than 2gb and tran log less than half a gb.
Noticed someone had set the backup for this database to append the backup file in stead of overwrite it.
Just wondering why would you want to append a backup instead of overwrite it?
View 1 Replies
View Related
Jun 11, 2015
I just completed a copy-Only compressed backup of a DB (with a FULL Recovery Model ) on SQL Server 2012 and the resulting backup (the bak file) is 1/100th the size of the data & log file. Is the compression in SQL Server 2012 just that good or did something else happen that I did not catch? Below is the T-SQL to re-create the backup. The size of the data file is 750MB and the log file is 75GB and is %95 used according to the SQLPERF command.
Does the compression in SQL Server 2012 simply that good
BACKUP DATABASE [MYBIGOLEDB] TO DISK = N'Z:Microsoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBackupMYBIGOLEDB_20150611.bak' WITH COPY_ONLY, NOFORMAT, INIT, NAME = N'MYBIGOLEDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
View 1 Replies
View Related
Jul 11, 2015
Getting "The specified network name is no longer available" during SQL Backups dumping to Network share.
- problem started occurring on July 3, 2015
- our Ola Hallengren backup solution deployed to over 150 SQL Servers.. was running fine for almost 2 years
- Occurring in multiple SQL Server environments: W/Server 2012 Ent, W/Server 2008 Ent, SQL 2012 Ent, SQL 2008 R2 Ent
- We're utilizing latest Ola Hallengren backup solution (Jan 2015 release)
- Dumping to network share (jobs running under SQL Agent account w/ local admin & sysadmin on server and full rights to Network Share
View 1 Replies
View Related
May 8, 2014
We have a SQL 2012 server instance that has log shipping set up to another SQL 2012 server to provide a warm standby for a forward facing application. The databases on the primary server occasionally are required to be backed up and restored to a development environment, completely different server. Is there a way to schedule full backups with log shipping enabled?
View 2 Replies
View Related
Jan 12, 2015
Native backups to NAS do not complete.We have been experiencing an issue whereby our native backups are hanging with status': SUSPENDED/ RUNNABLE...I ran select * from sys.sysprocesses. All of the backup SPIDs processes show BACKUPTHREAD/PREEMPTIVE_OS_FILEOPS
This first occurred last Wednesday evening. When I discovered this on Thursday, I attempted to kill the backup jobs. This also hung with 0% completed/0% time remaining. Backups hung on more than one instance.That evening, I attempted to restart the instance which also failed with something along the lines of: could not start MASTER file in use.
I then restarted the server--which I really did not want to do--and this cleared it. I was also able to manually kick off maintenance plans (DBCC CHECKDB and full backup) without issue.I was off Friday and the weekend. I came in this morning and found the maintenance plans (diff/tlog backups) did not complete in some of the instances--in one case, the instance affected now was not affected before. They appeared to have hung on their next scheduled kickoff which was later that night after I went home
Remembering the "file in use" error, I have run process monitor to see if anything unusual had a lock on any files. I saw only SQL Server and Double-Take processes accessing log files.Being a relatively new DBA, I am user where to go next in trying to track down the cause of this issue. This is fairly urgent as one of the instances that has had this problem both times is our production SharePoint environment.
ENVIRONMENT:
SQL version:
Microsoft SQL Server 2012 (SP1) - 11.0.3368.0 (X64)
May 22 2013 17:10:44
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
View 8 Replies
View Related
Aug 4, 2015
I am in plan to implement following for backup of one of our database Enable Full recovery mode
1- Create full backup nightly
2- Create transaction log backup after every 25 min
as I am taking full backup every night, I think I can remove transaction log file backups at the time of full backup, as we can apply transaction log backup over full backup.My question is regarding removal of transaction log backups.
-Should I remove all transaction log backups and then execute full backup?
-Should I execute full backup and remove all transaction log backup older than 24Hrs ?
-Do I have to consider SCN or related info before deleting any transaction log backup ?
View 9 Replies
View Related
May 29, 2014
Up until a couple of days ago all my maintenance plans were working.
Now they are all failing with the following errors -
Message
Executed as user: MHS2sql2008. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 10:07:02 Progress: 2014-05-29 10:07:03.20 Source: {CE851AA2-045F-429A-885E-E60E75A38639} Executing query "DECLARE @Guid UNIQUEIDENTIFIER
[Code] ....
The package execution failed. The step failed. Now I have changed the SQL Server Agent service account to use a network account called "sql2008".Stopped and started the Agent service.
The account has access to the folders it is trying to back up to. It's a full admin on the sql box and the box where I am trying to backup to?
View 4 Replies
View Related
Jul 11, 2014
We have just implemented a SQL 2012 always on environment. We have a primary and secondary server. I am confused about how to set up the backup plans. The application team was happy to tell me that in sql 2012 always on we can offload the backups to the secondary, thus reducing overhead on the primary server.
However, the secondary only supports copy only full backups. I am unsure how these would be useful in a disaster event? I could not apply any trx log backups on a copy only backup. This means I need to run my full backups on the primary server?
View 7 Replies
View Related
Apr 20, 2015
I am setting up Availability Groups and I want to use the secondary replica to perform the full copy_only backups to reduce the load on the primary replica.But what is the best way to check for successful full backups on Availability Group databases?
Previously I could check the system table msdb.dbo.backupset but this is not available for copy_only backups.So I wonder how people are monitoring that their full backups have been successful?
Do you just check that the SQL Agent job that runs the backup was successful?
Or do you search the SQL Server Error Log for entries like "Database backed up. Database: xxx" where database xxx is in an Availability Group?
Or is there a better method?
View 1 Replies
View Related
Jun 25, 2015
I need to restore test DB from production backup but once it is restored I would need all the permissions of sql logins and windows AD account intact in test Db as it was before.
View 4 Replies
View Related
