All Questions

Tagged with
Filter by
Sorted by
Tagged with
107
votes
8answers
316k views

How do I move SQL Server database files?

I have a database and want to move the .mdf and .ldf files to another location. But I do not want to stop the MSSQLSERVER service, and I do not want to export to another server. How can I do this?
16
votes
7answers
73k views

Error 3154 while restoring a backup using WITH REPLACE

I have SQL 2012 with SP1 installed on my computer. I made a backup of a database test.bak. I have a database with the name test2 which is the same database, but the data changed. I want to restore ...
13
votes
1answer
14k views

Why do we use FILE = 1 in scripts for restoring a database

Recently when I came across the script for restoring a database, I got a doubt on why we have to use "FILE = 1"? Can't we restore a database without that statement!? Basically, what it is used for?
13
votes
3answers
4k views

Backup internals - What happens when a backup job is running - in terms of locking and performance overhead in SQL Server?

For MySQL I know the database is backed up table by table in SQL Statements, this results in locking and if you update columns while backing up you might end up in integrity problems. To my ...
13
votes
3answers
17k views

How best to maintain SQL log file sizes

I'm somewhat of a new DBA and I'm managing a SQL Server 2012 instance that has a fair amount of activity. I'm running in Full Recovery mode because we need point in time recovery. Right now, I'm ...
12
votes
2answers
625 views

During a log backup is the data backed up to the start or end of the operation?

Say I am running a log backup, and that log backup takes 10 minutes to complete. During that 10 minute window, further transactions are run. Given the below example, which transactions does the log ...
11
votes
1answer
2k views

Restoring differential backup creates DEFUNCT log file?

Here is my problem. I'm trying to move a database to a new server via a full restore, then cutover with a quick differential backup/restore. I can do a full restore without a problem, but when ...
10
votes
3answers
3k views

Backing up multiple databases to the same point in time

Usually when we start the backup we do not allow changes to commit or database will be not be accessible. I mean database will be in single user mode but I want to start the backup and release the db ...
10
votes
3answers
2k views

What is the use of restore headeronly when performing a backup?

When I monitor my backups with the following query: SELECT command, percent_complete, 'elapsed' = total_elapsed_time / 60000.0, 'remaining' = estimated_completion_time / ...
9
votes
3answers
8k views

Why is .bak file size increasing/doubling with each consecutive backup on SQL Server?

I'm running a simple DB on SQL Server 2012 Express. Just today, when I backup the database, the .bak file size doubles to what it was from the previous backup just minutes before. I have done ...
9
votes
1answer
43k views

I/O is frozen on Database & also I/O was resumed on Database on daily basis in log with complete Backup

I would like to say that as on daily basis i got the message in my log file. First The I/O is frozen on Database (DatabaseName) message appears in log. The message is like I/O is frozen on ...
8
votes
2answers
25k views

Restore a SQL Server 2012 backup file in SQL Server 2016/2014

I have SQL Server 2012 backup files and want to restore these to SQL Server 2016/2014. Will it work? I need to find this out before I uninstall SQL Server 2012.
8
votes
5answers
12k views

SQL Server 2012 always-on backup, full vs copy-only

I need a quick clarification on copy-only backup vs full backup as it pertains to transaction log numbering and restore if required. I have SQL Server 2012 setup with always on for multiple databases....
8
votes
2answers
2k views

Shrinking a SQL Server Transaction Log

I’m currently having to deal with a SQL Server transaction log that has got out of control. Disclaimer: I’m not a dba and this isn’t my area of expertise so please bear with me. Currently I have a ...
7
votes
3answers
18k views

Transaction Log Maintanance While Using AlwaysOn Availability Group

We are using HADR ( AlwaysOn Availability Group) feature of SQL Server 2012. Server and AG Configuration as below: SQL12_SRV1 --> AG1(PRIMARY) - SYNC -->> DBTest SQL12_SRV2 --> AG1(SECONDARY) ...
7
votes
3answers
3k views

(Ola Hallengren) Deleting log backups older than last FULL backup

When I deploy Ola Hallengren's backup script into my servers, I always set the @CleanupTime parameter for log backups to zero. This way, every time the log backup job runs, it checks for log backup ...
6
votes
3answers
10k views

Unable to create restore plan due to break in LSN chain

My backup plan is: Full backup - once a day Differential backup - every four hours Transaction Log backup - every 30 minutes. All database backup are stored on my computer. I add some wrong ...
6
votes
2answers
2k views

Transaction Log Truncation with Copy Only

I have a database backup job set to use full backup with the Copy only option on once daily. The copy only is on because from what I have read that is the only way to backup a database that is ...
6
votes
2answers
12k views

Set database offline with rollback immediate takes an hour to complete

Every night we take our production database offline, detach it, and then re-attach it to sever any active connections before start of our backup procedures. The first command is: USE master ALTER ...
6
votes
1answer
518 views

RESTORE VERIFYONLY locks file in Azure Blob Storage

I'm using SQL Server 2012 SP2 to backup my transaction logs directly into Azure Blob Storage using the BACKUP TO URL statement. I am then attemtpting to verify my transaction log as follows: RESTORE ...
5
votes
5answers
9k views

How can I tell if a SQL Server backup is compressed?

We have recently upgraded from SQL Server 2005 to SQL Server 2012. Under SQL Server 2005 there is no option to create compressed backups as there is in 2012. If you attempt BACKUP DATABASE ... WITH (...
5
votes
2answers
599 views

SQL Always On COPY ONLY backups - what's the point if I cant restore the AG from these backups?

Think I already know the answer to this but I'll ask anyway. Situation is: SQL 2012 Always On - 2 synchronous replicas. 7 TB worth of Sharepoint DBs in 1 AG. Before patching - I want a Full DB ...
5
votes
2answers
899 views

Need suggestion on Back up Strategy [closed]

Our client has agreed to an RPO of one day data loss. So I am going to change back up strategy to eliminate space constraints. Database is in full recovery mode. Existing backup plan:- Once daily ...
5
votes
1answer
474 views

SQL availability groups log maintenance

I have a AG with 2 nodes in a site in sync mode and 1 offsite in async mode. I'm having trouble understanding how the logspace is maintained in this scenario. For instance, I'm taking log backups ...
5
votes
2answers
3k views

Deleting other files during maintenance plan

I have an SQL Server 2012 maintenance plan which does the normal stuff (rebuild indexes and update statistics, back up DBs, deletes .bak files older than X days, etc). Everything is working fine with ...
5
votes
2answers
1k views

if database is not shrinked does it affect backup size?

Ok my question is simple. Lets say that when i click database and hover over shrink it says it can be reduced 1 GB space. Now when i do a backup of this database, does this space also getting added ...
5
votes
4answers
5k views

SQL Server 2012 database backup successfully report but no backup file

I have created maintenance plan in SQL Server 2012. And every day, the maintenance plan should backup the database. There is no database backup file when I look in the folder where the backups must be ...
4
votes
2answers
4k views

Full Back up over to Network failed due to unknown network error 59

I am recently seeing the Unknown Netowrk Error 59 which saves the full back ups to a file share on some of the servers . The back ups happens at different times. Ola Halengren scripts are being used ...
4
votes
2answers
769 views

Setting DB to Simple Recovery then back to Full Recovery

I have recently inherited all of our company's SQL databases without much warning or experience, and was hoping to make a good impression and score a few quick wins by completely reviewing backups ...
4
votes
2answers
2k views

Centralized Backup report for SQL server databases

Even though, I've searched couple of them online but still looking if there is a better way to manage the SQL server backups, almost across 250 servers. Issue is we are not allowed sysadmin login to ...
4
votes
3answers
2k views

Backup strategies for zero data loss

I am working on one project and it has around 100K records. We can afford a downtime of 1 day but we need the data to be always available to us in latest state. I've gone through two options: 1) ...
4
votes
1answer
9k views

SQL Server BACKUP WITH FORMAT

When doing a SQL Server database backup using the T-SQL BACKUP statement, does WITH FORMAT only wipe out the specified Media Set or does it wipe out all Media Sets 'associated' with the database? ...
4
votes
2answers
2k views

Restore SQL Server database from .sql files

So we had an encrypted database that puked and killed our whole SQL Server setup. Sucks about our data, but we were smart enough to have our data structures / stored procedures / functions in Git The ...
4
votes
1answer
663 views

Why would a SQL Server 2008 R2 database disable a table index when upgraded to SQL Server 2012

I've restored a database backup in a new SQL Server 2012 server from a .bak made in SQL Server 2008 R2, and one of the tables has it's primary key index disabled. This doesn't happen when I restore to ...
4
votes
1answer
2k views

Restoring database backup makes identity column start at 1 again

I created a backup of our database in SSMS 2012, using Tasks -> Backup as a Full Backup, including all data. When I restore this backup on another server (2012 as well), all identity columns are ...
4
votes
1answer
1k views

Which version of Microsoft Tape Format is supported by which MS SQL Server

When you create a backup with Microsoft Sql Server, the resulting file is in Microsoft Tape Format. Different versions of SQL Server apparently use different version of this format. Which version of ...
4
votes
1answer
383 views

What are the best practices for backing up a secured SQL Server 2012?

I want to state right off the bat that I'm not looking for information on how to backup a SQL Server. I've got that taken care of. I'm looking for some form of documentation that will tell me the ...
3
votes
3answers
1k views

Does restoring a full backup bring it to the backup START time or the backup FINISH time?

Backstory: I'm trying to restore copies of several SQL 2012 databases to a different environment, but the application needs them to be (roughly) "synchronized" in time. (Of course the ideal way to do ...
3
votes
1answer
195 views

Why take full backups when you can do COPY_ONLY?

With a SQL Server 2012 availability group, you can only take COPY_ONLY full backups on replica. You can make regular BACKUP LOG. As it's possible to restore logs backups after a database restoration ...
3
votes
2answers
1k views

Backup of database X on SQL Server 2008 on server A - restore to SQL Server 2012 database Y on server B

I want to copy a SQL Server 2008 database to SQL Server 2012 with a different database name. Took a backup of 'production' and copied it to the SQL Server 2012 machine. Created a new SQL Server 2012 ...
3
votes
1answer
129 views

Separate differential backup from one restored baseline

I work on a team that customises software, part of this customisation is stored as records in a database, part is customisations to the schema itself and there are often some specific records in the ...
3
votes
2answers
1k views

Long running backups on small databases

We normally perform backups across the network to a file share on a different SAN. This works very well for all but 1 of our database servers. The problem machine is a new server running Windows ...
3
votes
1answer
95 views

Tool for quickly backup and restore a table

Is there a tool for quickly backup and restore a table's data, preferably to a csv file? It would be best if I could do it using batch. Run a .cmd file and data is backed up into a file with datetime ...
3
votes
1answer
94 views

Is tlog backup on replica clearing VLFs on the primary?

Is it true that a transaction log backup made on the secondary database is clearing the inactive VLFs on the tlog file on the primary database (in case of Availability Groups)? I was not able to find ...
3
votes
3answers
116 views

Backing up a SQL 2008 DB, then restoring, gives a SQL 2005 (compatibility level 90) DB

We have four different databases on a SQL Server 2008 (not SP2 - don't ask me why) database server that have an odd problem. When listed from sys.databases, these databases report that their ...
3
votes
2answers
2k views

Prevent logins loss when restoring a database

I'm backuping and restoring databases accross two sql server (v12.0.2000). But it looks like whenever I restore a .bak file, the login I created on the database that was backuped is no more existent ...
3
votes
1answer
572 views

Backup File Size Almost 50% of Database File Size with TDE Enabled

I receive a full backup of a very large database once per week from a vendor. It contains transactional data which we then load into our data warehouse. Fully restored, the database is around 707GB. ...
3
votes
1answer
2k views

Script out XMLA for all databases

I have several SSAS databases. I only want to script out the XMLA for them on a nightly basis. This will be a second tier backup in addition to the regular backups we have. How do I auto-generate ...
3
votes
2answers
1k views

SQL Server hangs during backup on EC2

Once and then our SQL Server will "hang" during its weekly full backup. When this happens, external connections start to timeout. The symptoms are: Connections timeout The sqlservr.exe process sits ...
3
votes
2answers
456 views

Automating an SQL task on a monthly basis

At the start of each month I have to give a team a copy of the live database which is held in a backup from 1am each morning (D:\Backup) - We then copy this database elsewhere onto a dev server and ...