SQL Server Differential backup in a separate file

[Banana]

I think I'm missing something.

I already have a full backup. What I want to do is to create a differential backup, but save it in a separate file, rather than adding it to the existing file. When I tried to make a new file and do a differential backup, the new file was twice as big as the original backup.

How would that be done?

 

[SQL_Hell]

How long ago was the original full backup competed?

The only time I have seen a differential backup bigger than the full backup is when the full backup was failing and I hadn't noticed. So gradually the differential was getting bigger and bigger.

 

[Banana]

Only last week. I did append some new data, so I would expect the new bak file to be roughly same size, but not twice the old bak. The full backup seems sane (e.g. I can restore it).

 

[SQL_Hell]

How big is the database?

It's possible that the appending data has caused the differential to be bigger.

I wouldn't bother with the differential backup just do a full backup every night and if you need to make a smaller file containing the changes then do a transaction log backup.


Out of interest; are you creating the backups in the GUI or with t-sql code. I prefer t-sql. If you give me some more details I can write your backups in t-sql for you if you want

 

[Banana]

11970 MB, or ~11 GB. However, I believe the difference should be only ~1 GB-ish, not ~3 GB as I have with the differential backup file.

I originally used GUI, not aware that there was T-SQL syntax for that.

The real intention here is to move only the difference in data (without changing the structure would be bonus as well) between my test server and the production server, which I thought the differential backup fit the bill here. There's no simple way of directly linking the servers due to network security, so it has to be moved via a file somehow...

 

[SQL_Hell]

There are many ways to keep sql server prod and development servers in line,

Full backup and restore
Transaction log shipping
Database mirroring
Over night SSIS package
Replication

It all depends on how often you want to do it, how update it has to be, how quick your want the process.

If it's only every now and again, then over night full backup and restore would get my vote, for simplicity

Also with transaction log backups and shipping, these processes tend to happen on a frequent schedule (every hour for example) so the file that gets restored is typically very small compared the the full database size.

edit: How many typos...grrr

 

[Banana]

In this case, this would be one-time thing.

Is there a mean of restoring a backup as an append operation rather than overwriting the existing data?

 

[SQL_Hell]

No, that is controlled by the type of backup.

You can restore the file over a network, rather than have to copy the full backup file to the development server. Depends how fast your networks is

 

[SQL_Hell]

What did you decide to do in the end?

 

[Banana]

For one, I found out that .bak files overwrite any security permissions, so moving it from one to other would be problematic, especially with Windows authentication and the fact that I didn't have sysadmin privileges on the production server.

I then found out that when I'm connected to the production server via VPN, I can then connect back to my local development server, using SQL Server authentication (gotta be SSA as my VPN login and my own Windows login are not same and thus wouldn't work) and just import the data server to server. This was much more easier than messing with a bunch of files, apparently.

 

[SQL_Hell]

Transfering the data via SSIS (import export wizard) does work very well and it can be very quick, if you use this method be sure to regulary update your indexes and statistics and be aware that if you transfer any tables that are created as new tables in the destination, it will not by default transfer your Keys and indexes.

Restoring the backup can cause problems with authentication, but this depends on what permissions you have on your production database server compared to what you have on your development database server.
But you can add users back into to database specific roles in your restore script using a system stored procedure called sp_addrolemember, in these type of scenarios I would have a script like the following:

--Restore database with replace from daily backup
RESTORE DATABASE Prod FROM ProdDaily WITH Replace,stats =10
 
--add users
exec sp_addrolemember 'db_datareader','sqladmin'
exec sp_addrolemember 'db_datawriter','sqladmin'

I am not sure which version of SQL Server you are using but when you say you lost permissions once you restored the database maybe you were getting an orphaned user issue. This is basically when the logins and users get out of synch due to the fact you restored the database and the logins have different SIDs (primary key in the system between user and login tables). There is a way to fix this using a system stored procedure called
sp_change_users_login.
In my example above I would do the following to fix the issue

exec sp_change_users_login 'sqladmin','sqladmin'

 

[Banana]

Thanks so much for those useful information.

For the permission situations, I don't think that was the cause of an orphaned user but rather that my logins weren't same for my dev computer and for client's production server, and I'm not sure if the sp_change_users_login would fix it without requiring sysadmin privilege which I didn't have?

Nonetheless, thanks for that information.

 

[Banana]

Here's a another question:

What would be the best way to "synchronize" two tables, so only the differences are moved? I think what I want is mirroring, but want to verify if that is the fastest & efficient way to do this. (For this operation, it's just one table)

 

[SQL_Hell]

Heya,

Database mirroring is more of standby solution where you mirror the entire database;

Transactional replication is probably the best thing to use here or even a manual update using triggers and a linked server.

 

[Banana]

Trigger would be overkill as 1) it's one-time thing, 2) the data is already placed.

Transactional replication... I'm looking an article and it sounds like it takes while to set up for what would be a one-time process.

I'm wondering if it's just simpler to use Import/Export again but write a query to append only if there's no matching primary key in the destination table?

 

[SQL_Hell]

ok I see...one time

Yes you can use a query in import export wizard to deal with inserts something like:

INSERT INTO table
SELECT *from table2
WHERE id not in (select id from table)

But how will you deal with data that has been updated?

 

[Banana]

Ah, yes. That's why I was looking at other things.

The thing is I *think* it was all appends and no updates, but I'm not 100% sure. Curse myself for not keeping note of what I was doing.

How would you have dealt with it if you needed to update some rows?

 

[SQL_Hell]

SSIS is very fast so I would probably just replace the whole table, especially if it's a one off.

Incidently I have done a similar thing today with transfering data, the table transfered was 26 columns wide and SSIS managed to transfer 829502 records in 2 minutes 54 seconds, pretty impressive

Another way you could do it, is to add additional columns or create a new table on the source database that logs the primary keys and the date a column was updated on that table, then run a query to update all the columns for the updated records. But that requires a fair bit of manual coding and maybe you're not allowed to start changing the structure on the source database.

 

[Banana]

The table in question has more than 100 columns (bleach! And don't look at me; I had no hand in designing this. I'm just making with what I was given), so each single row is quite expensive. It takes an hour to move approx 40,000 per hours across the network.

You may be right about it being easier just to replace the table, running it overnight. Will see about if appending is the only thing i need (I'm pretty sure I didn't do any updates).

Nonetheless, thanks so much for pointing out the options I have available in sharing the data.

 

[SQL_Hell]

100 columns aye, yep that would certainly slow it down

Glad to help in anyway I can mate

I have been working on little project where I have a job that updates records that have changed every five minutes but I do have the luxury of a DateModified column.
It was a similar thing where Replication was too much overkill and SSIS wasn't quire right due to the need for an immediate repsonse.

*Code removed*