Copy MSSQL Database

[RTollison 0]

i have a program (old) that creates a backup of a database then restores that database with a different name. it keeps all the users/stored procs/whatever.

Is there a newer/better way to create a copy of an MSSQL database?

Currently support team is saying that it takes a long time to finish.

[Angus Robertson 625]

Everything in SQL Server depends on the database size, and disk speed.

 

I distribute a database to multiple web servers weekly, using SSMS backup (about 30 secs), then restore on the web servers, about 15 seconds, for a 700MB DB, a 3GB DB takes a few seconds longer, I know these are not really that large....   Then a minute to update the credentials.

 

Angus

 

[RTollison 0]

interesting, but i don't have complete trust in support to manually do all that so i use delphi with adoconnection, adocommand to process it all to ensure the names never get mixed up and overwritten.

hoping for a better component or option to create the copy (test database from a production database)

[Angus Robertson 625]

Backup and restore can be scripted, ie

USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
TO DISK = 'c:\tmp\SQLTestDB.bak'
   WITH FORMAT,
      MEDIANAME = 'SQLServerBackups',
      NAME = 'Full Backup of SQLTestDB';
GO

but admit I've never tried to automate it in over 10 years, not sure if ADO supports the GO command.  

 

All my Delphi applications interact with SQL using stored procedures to separate the two languages and all easier SQL testing. 

 

Angus

 

 

[RTollison 0]

in my code i use

BACKUP DATABASE PRODUCTION TO DISK = C:\TEMP\TESTDATABASE.BAK WITH INIT

then

RESTORE DATABASE TESTDATABASE FROM DISK = C:\TEMP\TESTDATABASE.BAK WITH MOVE "logicalname" TO "location\testdatabase.mdf", REPLACE

same for ldf file

 

Is the with INIT slower than the with FORMAT?

[Angus Robertson 625]

Sounds like you are doing it correctly already!

 

FORMAT means write a new file, otherwise it will add backups to an existing file.  But I've not used these commands.

 

How long is your backup taking for what size file?

 

Angus

 

[RTollison 0]

generally speaking 10GB+ SQL server range from 2008r2 and up. Client last week had 42GB on SQL 2012 and it took about 40-45 minutes to complete the backup/restore process. This is run on the actual server while it is in use on the production database. and lots of times i see the SQL Server app using up 90% of the memory on those servers. sometimes i force clients to exit applications using the database then restart the SQL service, which drops the memory usage and then i run my app. < 20-30 minutes for a particular client who indicated that it had been running for 1.5 hrs.

 

[Angus Robertson 625]

Thanks, you are outside my experience with databases of those sizes, it might be worth trying backup from SSMS once to see if it's doing something magical to get better speeds, but I suspect it's just running those same commands. 

 

My backups used to be slower, until I went for SSDs everywhere with 32G of memory, but not highly loaded. 

 

Angus

 

[RTollison 0]

thanks for the info. i suspect it is the actual servers themselves. space available, disk cache and memory seem to be possible bottlenecks and my program is doing it correctly still. written back for sql 2005/2008 era. sounds like it is still viable solution now.

[Die Holländer 82]

11 hours ago, RTollison said:

thanks for the info. i suspect it is the actual servers themselves. space available, disk cache and memory

Yes, must be. Just look how much time it takes on the server a 10+GB file (movie or such) is copying from one directory to another..

Here a few GB database backup costs about 20 seconds. 

Edited 20 hours ago by Die Holländer

[Lars Fosdal 1845]

I use PowerShell + dbatools module to do backups and restores, as well as check for space, database schema sync, check contents of DB and run scripts, etc.

[TigerLilly 18]

Backup/Restore when the DB is under heavy load needs a lot more time. Having that said, I´d expect 10 GB to be backup/restored within a few minutes. So 45 min are worth looking where the time goes to. Is the load that heavy? Goes the backup to the same disks where the database files are located? Maybe a shortage of memory?

Maybe maintenace plans are worth looking: https://learn.microsoft.com/en-us/sql/relational-databases/maintenance-plans/create-a-maintenance-plan?view=sql-server-ver16

[Tom Chamberlain 48]

18 hours ago, RTollison said:

generally speaking 10GB+ SQL server range from 2008r2 and up. Client last week had 42GB on SQL 2012 and it took about 40-45 minutes to complete the backup/restore process. This is run on the actual server while it is in use on the production database. and lots of times i see the SQL Server app using up 90% of the memory on those servers. sometimes i force clients to exit applications using the database then restart the SQL service, which drops the memory usage and then i run my app. < 20-30 minutes for a particular client who indicated that it had been running for 1.5 hrs.

 

If your M$ SQL Server is NOT running at 80-90% memory it is not configured correctly, that's what it is suppose to do.  A maintenance plan is a must for any SQL Server database, rebuilding indexes for performance over time needs to be done for performance in general.   It takes our production system a little over a minute to backup a 6-7GB database during the day, the reason it only takes 20-30 minutes after you force the clients to exit is all the IO to the database stops, you could be fighting IO through-put and the SQL trying to get a good 'snap-shot' of the database.  Our production system is a small 16GB VMware VM running on a host with 14 other VM's all connected to a SAN running SSD's. (could I put more acronyms in there?)

 

Is this a 24/7 system?

They are making regular SQL backups right? You could just restore after that, if not there are bigger problems. (This could/should be a backup script job in SQL)

Is this a physical server or a VM (Hyper-V/VMware/Proxmox)?

They are not running some anti-virus that is looking at the database and/or backup folders are they? (anti-virus on the server is fine as long as the DB/backup folders are excluded, some cheaper A/V solutions are not smart enough to know not to to this)

Does the database have lots of BLOB fields? If so are they on their own database partition(s)? (more of a performance issue if not)

 

Never tried it but you may want to look into SQL Log Shipping instead of doing full backup/restore.  It should be able to be configured to run on the same server with different database names.