How to write a script that will backup a SQL database with open connections

I have a SQL database and wrote a script to copy the database then zip up that copy every night. The script isn't working. When I manually copy them, I get a message saying "The action can't be completed because the file is open in SQL Server. If I shutdown SQLSERVER service, the copying works. But I can't shutdown that service every time I run this backup script. Here's an example of the script i'm running.

xcopy /s "c:\Test" "C:\Test2"

How would I zip this database up, copy it, and not have to stop the service? Thanks!!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
Before trying to restore a database, you would need to cursor through the following query and kill the SPID's that are using that particular database you wish to restore.

I'd be extremely careful with this. You don't want to arbitrarily kill the wrong SPID.

SELECT  spid,
        DB_NAME(dbid)   AS dbname,
        convert(sysname, rtrim(loginame)) as loginname,
        substring( convert(varchar,last_batch,111) ,6  ,5 ) + ' '
            + substring( convert(varchar,last_batch,113) ,13 ,8 )
                        as last_batch_char
FROM    sys.sysprocesses with (nolock)

Open in new window

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
I beg your pardon? You use the BACKUP DATABASE T-SQL command to perform online backups, of course. And make sure you either also run BACKUP LOG , or have the DB running in Simple Recovery Mode (which does not keep the transaction log entries until you backup them).
The backup command is able to create a compressed file itself.
brasimanAuthor Commented:
Thanks dsacker. This makes me nervous though. :) Are there any other options?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

brasimanAuthor Commented:
Qlemo, sorry for the naive question. What backup line are you referring to that will create a compressed backup?

Or is there a way to make the .bak file using a script and have that automated?

dsackerContract ERP Admin/ConsultantCommented:
I assumed your opening post meant you copy a database BACKUP file (a .BAK file). Are you actually trying to copy the database.MDF file? I'm assuming NO.

It should be no issue to copy a .BAK file over to your target instance (or a compressed version of it), uncompress it, and restore it. I do this nightly.

However, upon restoring, I usually bounce the SQL Services to close any SPIDs that may have the database allocated.

I also sleep my process for about 30 seconds after copying. For some reason, I had an issue like yours some months back, and giving the OS time to release the file seemed to work for me.

That said, you can pilot cursoring through the query I provided, and rather than actually "KILL (<spid>)", you can PRINT 'KILL (<spid>)', so that you can then review those you'd potentially be killing, whereby you can make some analyzed decisions.
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
To create the backup, you can use a SQL Agent Job (the Wizard can create one for you, and shows all available options), or run something like this as Scheduled Task:
sqlcmd -E -Q "backup database Test to disk = 'C:\Test\Backup\Test.backup' with compression, init"

Open in new window

to connect to the local unnamed MSSQL instance with current Windows credentials, and create a compressed backup file, overwriting a prior backup (init). For detatils of commands and options please see the MSSQL Books Online - it is all explained there ;-).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
You can't copy the physical files while they are in use by SQL Server.

As Qlemo noted, you can use a standard SQL "BACKUP DATABASE" command while SQL is running.  That is by far the most common way to back up a SQL db.  

If you do want to, yikes, kill all active tasks instead, don't use the SQL-2000 style of KILLing spids, esp. without filtering out system spids.  Instead, use ALTER DATABASE, something liks this:

USE master --any db except the one you want to put OFFLINE
--20 seconds is just an example: you could use IMMEDIATE for no wait,
--  or a diff # of seconds to give more or less time.
EXEC xp_cmdshell 'xcopy "..." "..."'
brasimanAuthor Commented:
Thanks for the replies! dsacker, sorry for the confusion. I'm looking to make a backup through a command, either in SQL or .bat file.

Anytime you say yikes, i'll stay away from it. :) If i can run a scheduled task through SQL Mgmt Studio or through a .bat file, that would be preference.

If i do the suggested: sqlcmd -E -Q "backup database Test to disk = 'C:\Test\Backup\Test.backup' with compression, init", where do i put that? Is that a .bat or is that something run in SQL? Can SQL Mgmt Studio run a query as a task daily?
dsackerContract ERP Admin/ConsultantCommented:
The backup usually should first go on the source server. I always compress it there first, too. Then I bring it over. Also I always run it from the Windows Task scheduler, using SQLCMD. I compress with 7z, which makes a far more compact compression.

Just some things to think about.
brasimanAuthor Commented:
Thanks dsacker. That is exactly what i would like to do, how you do it!

Sorry for the naive questions everyone. It looks like in Windows Task Scheduler i can run, using SQLCMD, a script that will copy the database, compress it with 7z, then copy it over to another folder for backup. Is that what this does: sqlcmd -E -Q "backup database Test to disk = 'C:\Test\Backup\Test.backup' with compression, init"? I looked in task scheduler, I didn't see where to run the SQLCMD. Or is that part of the script/.bat?
dsackerContract ERP Admin/ConsultantCommented:
You'll want to create a .BAT file for your steps. In there you'll perform your SQLCMD, your copy, etc.

I keep my .BAT files in a BIN folder, my SQL scripts in a SCRIPTS folder, and call them accordingly.

This is a project. You'll be building something that will can be leveraged easily, but it takes a little time digging the trench.
Deepak ChauhanSQL Server DBACommented:
If you are taking backup with "WITH COMPRESSION" you might not need to compress the .Bak file using 7zip or rar.

You can take backup directly to Network drive.

sqlcmd -E -Q "backup database Test to disk = '\\NetworkDrive\Test\Backup\Test.bak' with compression, init"

Open in new window

You can schedule the backup script in sql server agent job here is the option for running Operating system command (CMDExec).

Generally scheduling in Windows task manager is the option when you are using sql Express edition.

Option 2.

Create SQL server agetnt JOB and write\ copy tsql code in the JOB step.

backup database Test to disk = '\\NetworkDrive\Test\Backup\Test.bak' with compression, init"
No need to zip the file and move to another location as backup is already compressed and file is creating on Network drive.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Which version and which edition of SQL Server do you have?
Are you going to use the backup file to do something else (as restoring in another SQL Server instance) or it's really only to keep as backup?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.