1

Very new to SQL Server. I'm trying to bring a backup from one our clients Windows 2012 Server Install to our Linux Install. We have the site working perfectly on Linux, but can't seem to do an arbitrary DB dump to get new data. They are running SQL Server 2012 Express (I'm assuming because it didn't have "Maintenance Plans" under the Management tab of the object Explorer)

enter image description here

Or in transact SQL:

BACKUP DATABASE [YourDB] TO  [YourDB_backup] WITH NOFORMAT, NOINIT,  NAME = N'YourDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

When I pulled this down to my Linux server it didn't have updated information (that was created 5-6 hours earlier). Why is this?

To ensure I wasn't making a mistake I deleted the entried DB on my Linux machine with:

USE master;
GO
DROP DATABASE YourDB;
GO

And then uploading it again as dictated here:

RESTORE DATABASE YourDB
FROM DISK = '/var/opt/mssql/backup/YourDB.bak'
WITH MOVE 'YourDB' TO '/var/opt/mssql/data/YourDB.mdf',
MOVE 'YourDB_Log' TO '/var/opt/mssql/data/YourDB_Log.ldf'
GO

Interestingly, their backup task that runs at midnight does have the new information, but I can't see where that task occurs to compare the script.

3

The issue is the defualt backup value of NOINIT

https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-2017

Media Set Options

These options operate on the media set as a whole.

{ NOINIT | INIT } Controls whether the backup operation appends to or overwrites the existing backup sets on the backup media. The default is to append to the most recent backup set on the media (NOINIT).

Go to the options tab and change the selection to overwrite, or change the SQL to INIT

enter image description here

Alternatively you can restore from the NOINIT backup using the directions at this post SQL Recover from .bak file with NOINIT

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.