All Questions

Tagged with
Filter by
Sorted by
Tagged with
129
votes
8answers
363k 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?
10
votes
1answer
48k 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 ...
14
votes
2answers
684 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 ...
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 ...
8
votes
2answers
29k 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
3answers
21k 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) ...
17
votes
7answers
75k 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
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 ...
6
votes
5answers
10k 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 (...
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 ...
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 ...
5
votes
2answers
927 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 ...
2
votes
1answer
808 views

Why SQL Server Express 2012 take automatically backup in “Virtual Device” without virtual device configuration?

I have noticed that after migration of SQL Server 2012 Enterprise to SQL Server 2012 Express, every day it shows "spurious" virtual backup after taking manual backup of database. When I execute this ...
4
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 ...
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 / ...
8
votes
5answers
13k 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....
5
votes
3answers
2k 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 ...
5
votes
4answers
6k 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 ...
2
votes
1answer
2k views

SQL Server: Transaction log still very large after log backup

I have a database that is on Full recovery mode. I previously made a full backup, and then on a daily basis add differential backups. Now I have backed up the transaction log for the first time, ...
2
votes
1answer
749 views

Transaction Log backup overwrites full backup on same media

I have a database that is currently backed up every two hours using a SQL Agent job. This is a secondary backup, done in addition to the hosting provider's backup strategy. I created a procedure (...
2
votes
1answer
491 views

What happens when two separate processes perform a full backup against the same database?

I have an instance of SQL Server 2012 RTM running. I have two separate applications that want to do a backup of a database lets call them "AppFull" and "AppFullCopyOnly". AppFull does a full ...
1
vote
1answer
105 views

Full backup of Simple recovery model - how much log is backed up if there is an open transaction

I have a situation right now. There is a database in SIMPLE recovery model. Note: 1. Compression is not enabled which will be enabled pretty soon. 2. SQL Server 2012 SP3 BackupSize: 8GB (Usually)&...