I am attempting to develop a workflow that detaches a SQL Express database, downloads and copy an updated version of the database, and reattaches the database. I have the middle section working (FTPCaller and filecopy writer), but I'm struggling with the attach and detach. I keep thinking there might be a way to do this using a startup and shutdown script, but I'm not having much luck. Has anyone had any success doing something similar? Any suggestions are greatly appreciated. Thanks!
Solved
Detach and Attach SQL Express Database
Best answer by takashi
Hi @tara, according to relevant Microsoft documentations, both detaching and attaching can be performed with Transact-SQL statements. I therefore think you can detach and attach a database file from/to a SQL Server with the SQLExecutor transformer. e.g.
Detach the database file storing the data of "test" database.
EXEC sp_detach_db 'test', 'true';Attach the "test" database file.
CREATE DATABASE test
ON (FILENAME = 'C:\<data folder path>\test.mdf'),
(FILENAME = 'C:\<data folder path>\test_log.ldf')
FOR ATTACH;
In my quick test, the SQLExecutor with SQL statements above worked as expected. However, there seems to be some limitations and restrictions depending on the actual database side conditions. See here to learn more: Database Detach and Attach (SQL Server)
Hope this helps.
- Best Answer
Hi @tara, according to relevant Microsoft documentations, both detaching and attaching can be performed with Transact-SQL statements. I therefore think you can detach and attach a database file from/to a SQL Server with the SQLExecutor transformer. e.g.
Detach the database file storing the data of "test" database.
EXEC sp_detach_db 'test', 'true';Attach the "test" database file.
CREATE DATABASE test
ON (FILENAME = 'C:\<data folder path>\test.mdf'),
(FILENAME = 'C:\<data folder path>\test_log.ldf')
FOR ATTACH;
In my quick test, the SQLExecutor with SQL statements above worked as expected. However, there seems to be some limitations and restrictions depending on the actual database side conditions. See here to learn more: Database Detach and Attach (SQL Server)
Hope this helps.
takashi wrote:
Hi @tara, according to relevant Microsoft documentations, both detaching and attaching can be performed with Transact-SQL statements. I therefore think you can detach and attach a database file from/to a SQL Server with the SQLExecutor transformer. e.g.
Detach the database file storing the data of "test" database.
EXEC sp_detach_db 'test', 'true';Attach the "test" database file.
CREATE DATABASE test
ON (FILENAME = 'C:\<data folder path>\test.mdf'),
(FILENAME = 'C:\<data folder path>\test_log.ldf')
FOR ATTACH;
In my quick test, the SQLExecutor with SQL statements above worked as expected. However, there seems to be some limitations and restrictions depending on the actual database side conditions. See here to learn more: Database Detach and Attach (SQL Server)
Hope this helps.
takashi wrote:
Hi @tara, according to relevant Microsoft documentations, both detaching and attaching can be performed with Transact-SQL statements. I therefore think you can detach and attach a database file from/to a SQL Server with the SQLExecutor transformer. e.g.
Detach the database file storing the data of "test" database.
EXEC sp_detach_db 'test', 'true';Attach the "test" database file.
CREATE DATABASE test
ON (FILENAME = 'C:\<data folder path>\test.mdf'),
(FILENAME = 'C:\<data folder path>\test_log.ldf')
FOR ATTACH;
In my quick test, the SQLExecutor with SQL statements above worked as expected. However, there seems to be some limitations and restrictions depending on the actual database side conditions. See here to learn more: Database Detach and Attach (SQL Server)
Hope this helps.
Thank you for the help @takashi! After I was able to figure out the database connection issues I was having with SQL Express (the SQL Server Browser service was stopped, duh!), this worked perfectly. As always, your feedback is much appreciated!
Reply
Helpful Members This Week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.